473,322 Members | 1,522 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Sorting Results Problem

I have 3 drop down menus that are used in a search to locate restaurants in a db. All of the drop down menus function, a search can be submitted with any combination of drop downs and the results are sorted by restaurant name. The problem occurs when you do not select anything from a drop down ("All" is submitted for all drop downs) and submit the search, the following error occurs: "Can't connect because You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BY `restaurant Name` ASC' at line 1." The error shows up after I add in "$restaurant_results = conn($find_restaurants."ORDER BY `restaurant Name` ASC"); " line 93. Without this line, the reults are returned by index number and will not be sorted by restaurant name. I need to have all the results sorted by restaurant name. I have included the code below. Any help would be greatly appreciated.

Expand|Select|Wrap|Line Numbers
  1. <?php 
  2. /*------------------------------------------------------------------------ 
  3.             control codes 
  4. ------------------------------------------------------------------------*/ 
  5.  
  6. if (isset($_POST['submit'])) 
  7.  
  8.   search();       //call the search function 
  9.  
  10. }else{ 
  11.  
  12.   show_form();   //call the show form function 
  13.  
  14. }//end if 
  15.  
  16. /*------------------------------------------------------------------------ 
  17.             show the search form 
  18. ------------------------------------------------------------------------*/ 
  19.  
  20. function show_form() 
  21.   //call the dropdown function which creates an html string to build a select box for each element 
  22.   $neighborhood_dropdown   = dropdown('neighborhood','restaurant'); 
  23.   $cuisine_dropdown  = dropdown('cuisine1','restaurant'); 
  24.   $type_dropdown     = dropdown('type','restaurant');
  25.  
  26.  
  27.  
  28.  
  29.  
  30.  
  31.   echo "<form name='search' action='".$_SERVER['PHP_SELF']."' method='post'> 
  32.         <table width='50%' align='center' valign='center'> 
  33.         <tr> 
  34.           <td colspan='2' align='center'>Search Form</td> 
  35.         </tr> 
  36.         <tr> 
  37.           <td align='right'>Neighborhood:</td><td>$neighborhood_dropdown</td> 
  38.         </tr> 
  39.         <tr> 
  40.           <td align='right'>Cuisine:</td><td>$cuisine_dropdown</td> 
  41.         </tr> 
  42.         <tr> 
  43.           <td align='right'>Type:</td><td>$type_dropdown</td> 
  44.         </tr> 
  45.         <tr> 
  46.           <td colspan='2' align='center'> </td> 
  47.         </tr> 
  48.         <tr> 
  49.           <td colspan='2' align='center'><input type='submit' name='submit' value='Go!'></td> 
  50.         </tr> 
  51.         </table> 
  52.         </form>"; 
  53.  
  54. }//end function 
  55.  
  56.  
  57. /*------------------------------------------------------------------------ 
  58.             run the search and show the results 
  59. ------------------------------------------------------------------------*/ 
  60.  
  61. function search() 
  62.  
  63. //base sql 
  64. $find_restaurants = "select * FROM `restaurant` WHERE 1";
  65.  
  66. //get the values from the form 
  67. //NOTE: You should do way more valdation on the values before you attempt to process anything 
  68.  
  69.  
  70.   if ((!empty($_POST['neighborhood']))&&($_POST['neighborhood'] != 'all')) 
  71.   { 
  72. $find_restaurants .= " and neighborhood like '". addslashes($_POST['neighborhood']). "%'"; 
  73.   }
  74.  
  75.   if ((!empty($_POST['cuisine1']))&&($_POST['cuisine1'] != 'all')) 
  76.   { 
  77. $find_restaurants .= " and cuisine1 like '". addslashes($_POST['cuisine1'])."%'"; 
  78.   } 
  79.  
  80.   if ((!empty($_POST['type']))&&($_POST['type'] != 'all')) 
  81.   { 
  82. $find_restaurants .= " and type like '". addslashes($_POST['type'])."%' "; 
  83.   }
  84.  
  85.   //add more elements (or take away) as you desire...follow the same code structure as above 
  86.  
  87.  
  88.   //run query
  89.  
  90.     $restaurant_results = conn($find_restaurants."ORDER BY `restaurant Name` ASC"); 
  91.  
  92.   if (!$restaurant_results){ die("No results due to database error.<br>".mysql_error());  } 
  93.  
  94.   if (mysql_num_rows($restaurant_results)==0) 
  95.   { 
  96.     echo "No Results found!"; 
  97.   }else{ 
  98.  
  99.     echo "<table border='1'><th>Restaurant Name</th><th>neighborhood</th><th>cuisine</th><th>type</th></tr>"; 
  100.  
  101.     while ($rows= mysql_fetch_array($restaurant_results)) 
  102.     { 
  103.       echo "<tr>"; 
  104.       echo "<td>". $rows['restaurant Name'] ."</td>";
  105.       echo "<td>". $rows['neighborhood']  ."</td>"; 
  106.       echo "<td>". $rows['cuisine1'] ."</td>"; 
  107.       echo "<td>". $rows['type']    ."</td>";
  108.       echo "</tr>"; 
  109.  
  110.     }  
  111.     echo "</table>"; 
  112.   }//end if 
  113.  
  114. ?>
  115. <p align="center"><a href="test3.php">refine search</a></p>
  116. <?
  117.  
  118. }//end function 
  119.  
  120. /*------------------------------------------------------------------------ 
  121.             create the drop downs 
  122. ------------------------------------------------------------------------*/ 
  123.  
  124. function dropdown($field, $table) 
  125. {  
  126.   //initialize variables 
  127.   $oHTML  = ''; 
  128.   $get_dropdown_values = ''; 
  129.  
  130.   //check to see if the field is passed correctly 
  131.   if (($field == "")||($table == "")) 
  132.   { 
  133.     die("No column or table specified to create drop down from!"); 
  134.   } 
  135.  
  136.   $get_dropdown_values = "select distinct($field) from $table ORDER BY $field"; 
  137.  
  138.   //call the db function and run the query 
  139.   $result_drop_down = conn($get_dropdown_values); 
  140.  
  141.   //if no results are found to create a drop down return a textbox 
  142.   if ((!$result_drop_down) ||(mysql_num_rows($result_drop_down)==0)) 
  143.   { 
  144.     $oHTML .= "<input type='text' name='$field' value='' size='15'>"; 
  145.   }elseif (($result_drop_down)&&(mysql_num_rows($result_drop_down)>0)){ 
  146.  
  147.     //build the select box out of the results 
  148.     $oHTML .= "<select name='$field'>\n<option value='all'>All</option>\n"; 
  149.     while ($rows = mysql_fetch_array($result_drop_down)) 
  150.     { 
  151.       $oHTML .= "<option value='".$rows[$field]."'>".$rows[$field]."</option>\n"; 
  152.     } 
  153.     $oHTML .= "</select>\n"; 
  154.   } 
  155.  
  156.   //send the value back to the calling code 
  157.   return $oHTML; 
  158. }//end function 
  159.  
  160. /*------------------------------------------------------------------------ 
  161.             database connection function 
  162. ------------------------------------------------------------------------*/ 
  163.  
  164. function conn($database) 
  165. {    
  166. include 'config.php';
  167. include 'opendb.php';
  168.  
  169.     $result = mysql_query($database) or die ("Can't connect because ". mysql_error()); 
  170.  
  171.     return $result; 
  172.  
  173. }//end function      
  174.  
  175. ?>
  176.  
Dec 24 '07 #1
1 1604
I have 3 drop down menus that are used in a search to locate restaurants in a db. All of the drop down menus function, a search can be submitted with any combination of drop downs and the results are sorted by restaurant name. The problem occurs when you do not select anything from a drop down ("All" is submitted for all drop downs) and subm...
Change line 93 to
Expand|Select|Wrap|Line Numbers
  1. $restaurant_results = die($find_restaurants."ORDER BY `restaurant Name` ASC");
  2.  
And tell me what it says on the page. FYI die() is a PHP function that ends the script and displays a message, so this will let me see what is getting queried.
I'm almost 100% sure that it not valid mysql to have a 2 word name for a column (or field as some peeps say), and maybe the random "1" placed at the end of the query on line 67.
Dec 26 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Henry Wiz | last post by:
Hi all, I wanna ask how to do the following sorting function, for example Name Age Brian 22 Alice 17 Patrick 21
11
by: Nikki | last post by:
Is it possible to sort a dataset rather than a dataview? I have a web service that returns a dataset which I would like to sort before returning it (this is so the sorting is standardised and so...
7
by: Karin Jensen | last post by:
Hi I am running a PHP program that connects to an Access 2000 database via ODBC: $results = odbc_exec($connection_id, $sql_select); Is it possible to sort the contents of $results? I wish to...
8
by: Mike MacSween | last post by:
tblCourses one to many to tblEvents. A course may have an intro workshop (a type of event), a mid course workshop, a final exam. Or any combination. Or something different in the future. At...
1
by: aredo3604gif | last post by:
On Sun, 10 Apr 2005 19:46:32 GMT, aredo3604gif@yahoo.com wrote: >The user can dynamically enter and change the rule connection between >objects. The rule is a "<" and so given two objects: >a <...
7
by: Brett Romero | last post by:
I have a dataset with one table, which has four columns. All are of type INT. I need to convert this dataset into a dataview so I can sort on the last three columns. I may sort one of the three...
1
by: LoopyNZ | last post by:
Hi there, I have an IIF expression that is returning a 0 (exact match), 1 (partial match), or 2 (no match) based on comparing text fields (see below). However, when I try to sort the results...
4
by: Trent | last post by:
Still have problems with this thing. Seems my results are not matching the "correct" example given. The three sets of numbers below the last 3 columns is suppose to be the number of comparisons...
1
KevinADC
by: KevinADC | last post by:
Introduction In part one we discussed the default sort function. In part two we will discuss more advanced techniques you can use to sort data. Some of the techniques might introduce unfamiliar...
1
by: John A Grandy | last post by:
In regard to a GridView that must support searching, filtering, sorting, and paging ... There is a tradeoff in performing the sorting and paging in the database versus to creating a CLR sort...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.