Building a dynamic sql query with ORDER BY + Variables

WeberForums.com Forum Index -> PHP General

View previous topic :: View next topic   Author Message fael097 Joined: 11 Mar 2010 Posts: 5 Posted: Thu Mar 11, 2010 4:20 pm    Post subject: Building a dynamic sql query with ORDER BY + Variables 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 the sql query section. Code: $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"; the $order variable defines the column name wich the result will be sorted by, and the $dir variable dedfines DESC or ASC only. my syntax error is related to this, because if i change the Code: $order=$_GET['order']; $dir=$_GET['dir']; to Code: $order=id; $dir=DESC; the sql syntax works fine, as you can see in http://fael097.site11.com/test (obviously ordering result by column wont work) thanks for any kind of help! Back to top lostboy Joined: 02 May 2004 Posts: 6033 Location: toronto, canada Posted: Thu Mar 11, 2010 6:24 pm    Post subject: That easy to handle with the code Code: //default values for $order and $dir $dir     = "asc"; $order = "name"; $string = "";  //empty default (show all within reason) $cat     = ""; //no cat set yet //basic sql to pull [all] records $sql     = "SELECT * FROM test WHERE 1 "; //get the values (note you should validate this data or someone will hack //your site) //(string) justed forces the data to be a string (this is not validation) //the '%' indicates a wildcard search, so the below statements would match //anything where the $string value is in the word, beginning, middle or end if(!empty($_GET['string'])){   $string=(string)$_GET['string'];   //search string     $sql .= " AND name LIKE '%$string%' OR brand LIKE '%$string%' "; }//end if if(!empty($_GET['cat'])){   $cat    =(string)$_GET['cat'];      //category     $sql .= " OR category like '%$string%' "; //check $dir first since it just needs to be a var to fit into the sql if(!empty($_GET['dir'])){   $dir     =(string)$_GET['dir'];          //dir of order by } if(!empty($_GET['order'])){   $order =(string)$_GET['order'];   //order by   $sql .= "ORDER BY $order $dir"; } Back to top fael097 Joined: 11 Mar 2010 Posts: 5 Posted: Thu Mar 11, 2010 10:03 pm    Post subject: perfect! thanks much just a few changes and you can see here: http://fael097.site11.com/test Back to top lostboy Joined: 02 May 2004 Posts: 6033 Location: toronto, canada Posted: Fri Mar 12, 2010 12:01 am    Post subject: works nice! Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First

WeberTrivia Questions   Think you are smart? Prove it!. Try your skills with these questions :   Recursive arrays and multi-dimensional arrays are one and the same. (PHP and MySQL)   The \"cache_dir\" tag of the squid configuration has a default of /var/spool/squid. (Linux) PHP Code Examples   Stream diffrent sizes of images from a single image to save disk space.   JavaScript dropdown list menu to switch any page.   Dump the contents of a PHP variable in html format with a recursive list of subfolders and files from a given root directory.   PHP Dump in html format the contents of one array variable with a recursive list of the nested array variables inside.   Link Extractor - This function is used to extract links from a given URL. This will convert relative path into absolute path and also remove PHPSESSID stuff.   Building a Dynamic Form using Javascript and innerHTML. Add form elements in realtime without refreshing the page.   A PHP Calendar function with CSS : add a cool calendar to any php page by just adding a calendar class based function.

	WeberForums.com Forum Index -> PHP General	All times are GMT + 2 Hours
Page 1 of 1

Jump to: Select a forum PHP Forums----------------PHP GeneralPHP MySQL Jobs and ProjectsPHP Articles Tutorials and ScriptsWhy Use PHPPEARPHP Books MySQL Forums----------------MySQL General Apache Forums----------------Apache General Web Development Forums----------------Java Script / AJAXSearch Engine Optimization (SEO)Cascading Style SheetsHTMLXML & XSL Graphics & Design----------------Theory related web designWeb Design Security Forums----------------Security Linux Forums----------------Linux General Web Development Misc----------------Hosting Related Miscellaneous----------------Feedback & TestimonialsMiscEvents  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum