By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,988 Members | 1,367 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,988 IT Pros & Developers. It's quick & easy.

Sorting Results Problem

P: 1
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
Share this Question
Share on Google+
1 Reply


helimeef
P: 77
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

Post your reply

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