Building a dynamic sql query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
fael097
Forum Commoner
Posts: 34
Joined: Sat Mar 06, 2010 7:57 pm

Building a dynamic sql query

Post 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";
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Building a dynamic sql query

Post 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
fael097
Forum Commoner
Posts: 34
Joined: Sat Mar 06, 2010 7:57 pm

Re: Building a dynamic sql query

Post 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.
Post Reply