best way to pass a sql command through the query string

rbeemsterboer

Hi,

I am working on a php script that has to pass a sql command through a query string to the page that has to be called.

Earlyer, i also bumped onto this problem because i was trying to sort an database row using a href link om my page.
When a user clicks on a column name the recordset has to change to ORDER BY MyColumn DESC or ORDER BY MyColumn ASC.
As you might understand MyColumn is a variable here and has to be set to the right column to sort/order by

My Question:
What is the best way to pass a (large) SQL string throug query string using php?

I had experinced some problems with (raw)urlencode so i thought this was the best way to get behind the right answer.

I hope someone can help me out with my problem.

Best Regards,

Rick Beemsterboer
Amsterdam / Netherlands

TodManPlaa

Hey Rick,

Rather than passing the statement in the query, why not just pass a variable and alter the sql accordingly?

(i've made up the column names for illustrative purposes)

For example:
page 1:

<a href="pagetwo.php?col=add&ord=asc">Address</a><br>
<a href="pagetwo.php?col=tel&ord=asc">Telephone</a><br>
<a href="pagetwo.php?col=fax&ord=dsc">Fax</a><br>
<a href="pagetwo.php?col=veg&ord=asc">Favourite vegetable</a><br>

So if a user clicks on the first link, two variables are passed to page 2:
col and ord
(note I suggest you do not use the real column names in the querystring.)

(note the order for Fax is dsc)

Page 2:

<?php
//set the data type

settype($col,"string");
settype($ord,"string");

/*cut off any garbage that a user tries to throw in - 
there's more checking you could do to validate this data, 
but am sure you know what I mean.*/

$col=substr($col,0,3);
$ord=substr($ord,0,3);

/*Then set the real column name*/

switch ($col){
	case "add":
	$col="address";
	break;
	case "tel":
	$col="telephone";
	break;
/*And so on for all the names - probably better done with a function*/
}

/*Set to upper case if that is your style*/

$ord=strtoupper($ord);

$someSql="SELECT c.col1,c.address,c.telephone FROM some_table c WHERE c.col1='something' ORDER BY c.$col $ord"

/*After the above got the variables, it should read:*/

SELECT c.col1,c.address,c.telephone FROM some_table c WHERE c.col1='something' ORDER BY c.address ASC

/*Which, I think is what you are after*/

Hope this helps

rbeemsterboer

Hi,

Thank you for your anwser,
I Think i was trying to use the same method as what you did when i was working at the Sort/OrderBy function a few weeks ago(but it didn't work out right, so i did quit)

I didn't wanted to make the post on this forum too large, so i brought up this old issue of my concerning the sorting and orderby subject.
But it was stupid of me to not tell the actuall problem.

what i'm actually working on is as follows:

I'm (re)building a large CRM application for the Company where i am working at.
The part where i am working at now has the following structure:

(Small) Relations Search:

( You can search on 16 diffrent areas for Organisation and Contact fields)

When the search results contains a list of Organisations (or see later Contacts) where you searched for and you are clicking on one.

You will get one table with al the general stuff from that company.(Such as Name, Extra Info, Employee#, Status, which Industry e.g.)
2 other tables are being used for fetching all the Communication(Gsm, Tel, Fax, Data) Numbers and Address information from the database.

The last table is used for showing all contact persons of that organisation with some other compact info on the organiastio page

When you click on one contact, you are going to a more detailed page of that person as you might understand.
The reason why i need to take along the (search) sql command a few pages is because i want to be able to go back to the page where all the search results are displayed.
javascript:history.go(-3) is not an option because there are several other functions on that page that are in the way.(interfear or how do you spell it).

asume that you are on the Contact page and on the top of that page this line below is showed where each word in that line is clickable and stands for the steps you took to get on that particular page.
(Except when you searched for a contact person. but then you also want to able to go one step "back" to the (this is no problem. done this already)) organisation level
the line looks like this:
// Relations // Search Results // Relations // Contacts

I still want to able to go back to search result page and re-execute that partical sql command (also when i searched for contact person or something else)

Can you or someone else still help me with this?
earlyer when i took along the sql string and wanted to go back to that page
mysql_query wont't fetch the data, but an echo command shows the string completly right.
on some occations, the sql string can be very long and deffintrly then i need the have the right method to ensure that it works right.
(say when there are 150 results that match my search, then there are 150 small strings attached to orgienele query trough a loop in php to extend the sql string)
where (OrgId = 1 or OrgId = 2 or OrgId =3 ... and so on) AND CountryId = 31 ......

🙂 I guess now that i can see my post on this forum as too large :rolleyes:
Since i was going for smaller

Best Regards,
Rick Beemsterboer

TodManPlaa

OK, well, its difficult to suggest a fix on this as I'm still not 100% clear on what you are doing but anyway I'll give it a try.

So, if what you are saying that in the following layout, the creation of each of the following (// Relations // Search Results // Relations // Contacts) requires different sql queries, with different variables.

Lets say Search results requires the variable "$name", and Relations requires "$OrgID", so you'd just pass those values between all the pages but not alwats use them.

So the URL for your Contacts page might be:
contacts.php?name=Sara&OrgID=524&someothervariable=apples

etc etc

but on the contacts page you'd only use Sara.

So by carrying all the variables page to page you could still generate the links for the users to go backwards...

hope this helps.

ps interfere
;-)
If this what you mean?

rbeemsterboer

Hi,

It seems that you are in the right direction.
I think i'm gonna use your method instead :-)

I was thinking way to difficult for what i was doing.
there are some other small things i have to adjust and add but that is no problem.(such as changing my $http_post_var[' '] functions to functions that understand both http_post_var and http_get_vars.

i am lazy mtf so i didn't wanted to make too much adjustments to my code.

Thanks for the advice on the coding and my word spelling (interfere)

Best Regards,
Rick Beemsterboer
Amsterdam, Netherlands