Page 1 of 1

Building a dynamic sql query

Posted: Wed Mar 10, 2010 6:18 pm
by fael097
hi, im building a search engine (ebay like) wich should work like this: you can display a list of products by selecting the category or typing a search keyword (i'll try to make it like you can search your keyword only inside that category later), and when result list is being shown, you can sort it by the field's name (name, brand, category and price) by clicking the fields header, doing so i get a variable with the fields name to put in my sql query on "order by $field" for example. First problem: it says i have an incorrect mysql syntax. Second problem: i need an initial 'order by' value, wich changes when i click a link specifying a name for the order by. If someone can help, i would be really glad. Here's my site: http://fael097.site11.com/test and here's the sql query section. If you need the full code please ask

Code: Select all

$string=$_GET['string'];
$cat=$_GET['cat'];
$order=$_GET['order'];
$dir=$_GET['dir'];
$sql="SELECT * FROM `test` WHERE `name` LIKE '$string' OR `brand` LIKE '$string' OR `category` LIKE '$string' ORDER BY $order $dir";

Re: Building a dynamic sql query

Posted: Wed Mar 10, 2010 7:06 pm
by mikosiko
for your syntax problem....

you need a , between your $order and $dir variables in the ORDER BY

Code: Select all

 
... ORDER BY $order , $dir ...
 
I also suggest you to read and look for the examples in this 2 links
http://dev.mysql.com/tech-resources/art ... ments.html

http://php.activeventure.com/function/m ... epare.html

In the last one scroll down around the middle... couple good examples

Re: Building a dynamic sql query

Posted: Thu Mar 11, 2010 5:06 am
by fael097
hey, thanks for your reply
although, i don't think that the , is the problem, since the $order defines the column to list by, and the $dir only dedfines ASC or DESC. also, adding the , didnt fix the problem... so its probably a quoting problem. any clues?

just fyi, if i change the :

Code: Select all

 
$order=$_GET['order'];
$dir=$_GET['dir'];
 
to:

Code: Select all

 
$order=id;
$dir=DESC;
 
it works, so im clueless, maybe i need an initial value for the GET variables, i dont know what exactly to do...

the objective of the $_GET['order'] and $_GET['dir'] is to allow the user to select ORDER BY name, brand, category or price, and then DESC or ASC.