Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    Regular Coder
    Join Date
    Sep 2009
    Posts
    165
    Thanks
    16
    Thanked 0 Times in 0 Posts

    PHP search form with multiple inputs

    I have a PHP script which I'm trying to use to generate search results from a db, with multiple search categories and a single submit. But it's failing withn a syntax error even though when I echo the query it looks fine.

    I'm desperate to try and get this sorted and I'm sure there must be a way...

    Code:
    <?php
    //include ('manager/special_offers/inc/dbconnect.php'); 
    
    echo '<h2>Hotel special offers</h2>'; 
    //Include the PS_Pagination class 
    include('manager/special_offers/inc/ps_pagination.php'); 
    
    
    //Connect to mysql db 
    $show_form=true; //used later on
    
    if(isset($_POST['sortform'])) {
    
        $conn = mysql_connect('localhost', 'login', 'password'); 
        mysql_select_db('specialoffers_db',$conn); 
        
        $yoursafe_post_location = $_POST['_Location']; 
        $yoursafe_post_price1 = $_POST['_Price1']; 
        $yoursafe_post_price2 = $_POST['_Price2']; 
        $yoursafe_post_category = $_POST['_Category']; 
    
    /* check variables being set */ 
    echo '<p>Values:</p>'; 
    echo 'Location: ' . $yoursafe_post_location . '<br />'; 
    echo 'Price1: ' . $yoursafe_post_price1. '<br />'; 
    echo 'Price2: ' . $yoursafe_post_price2. '<br />'; 
    echo 'Category: ' . $yoursafe_post_category. '<br />';  
    
        
        $query = "SELECT * FROM specialofferstable WHERE "; 
        $and = "0"; 
        
        if(trim($yoursafe_post_location)!= '') { 
        $and = "1"; 
        $query .= "location LIKE $yoursafe_post_location"; 
        } 
        
        if(trim($yoursafe_post_price1)!= '' && trim($yoursafe_post_price2)!= '') { 
        $and = "1"; 
        if(trim($and)!= '') {$query .= " AND ";} 
        $query .= "price BETWEEN $yoursafe_post_price1 AND $yoursafe_post_price2";  
        } 
        
        if(trim($yoursafe_post_category)!= '') { 
        $and = "1"; 
        if(trim($and)!= '') {$query .= " AND ";} 
        $query .= "category LIKE $yoursafe_post_category"; 
        } 
        
        $query .= " ORDER BY price"; 
    
    echo '<b>The query is:</b>' . $query; 
    $result = mysql_query($query) or die('Could not execute query:' . mysql_error());  
    
        //Create a PS_Pagination object 
        $pager = new PS_Pagination($conn, $query, 10, 4, 'param1=valu1&param2=value2'); 
        //The paginate() function returns a mysql 
        //result set for the current page 
        $rs = $pager->paginate(); 
        //Loop through the result set 
        while ($row= mysql_fetch_assoc($rs)) { 
        $title = $row["category"]; 
        $title2 = $row["company_hotel"]; 
        $title3 = $row["location"]; 
        $title4 = $row["offer"]; 
        $title5 = $row["price"]; 
        $title6 = $row["offerends"]; 
        $title7 = $row["mobile"]; 
        $dateformat = date("M j Y" ,strtotime($title6)); 
        
        echo '<h3>'.$title2.' ¦ '.$title3.' <em class=grey>(Offer ends: '.$dateformat.')</em></h3><h4>£'.$title5.' <strong class=call>Call 0844 793 7300</strong></h4> 
        <p>'.$title4.'</p><hr />' ; 
        
        $count++ ; 
        } 
        //Display the navigation 
        echo $pager->renderFullNav(); 
    
    echo '<h2>Travel special offers</h2>'; 
        // Build SQL Query 
        $query = "select * from specialofferstable where category like 'travel' ORDER BY price"; // specify the table and field names for the SQL query 
        $numresults=mysql_query($query); 
        $numrows=mysql_num_rows($numresults); 
        // get results 
        $result = mysql_query($query) or die("Couldn't execute query"); 
        // display the results returned 
        while ($row= mysql_fetch_array($result)) { 
        $title = $row["category"]; 
        $title2 = $row["company_hotel"]; 
        $title3 = $row["location"]; 
        $title4 = $row["offer"]; 
        $title5 = $row["price"]; 
        $title6 = $row["offerends"]; 
        $title7 = $row["mobile"]; 
        $dateformat = date("M j Y" ,strtotime($title6)); 
        
        echo '<h3>'.$title2.' ¦ '.$title3.' <em class=grey>(Offer ends: '.$dateformat.')</em></h3><h4>£'.$title5.' <strong class=call>Call 0844 793 7300</strong></h4> 
        <p>'.$title4.'</p><hr />' ; 
        
        $count++ ; 
        } 
        
        /* form has been processed! */
        $show_form=false;
    }
    
    if($show_form) { ?>
    
    <fieldset>
    <legend>Search special offers</legend>
    <form action="" method="post" name="thesortform">
      <table>
        <tr>
          <td>Location
            <input name="_Location" />
          </td>
          <td> Price
            <select name="_Price1" size="1">
              <option value="0">0</option>
              <option value="20">20</option>
              <option value="40">40</option>
              <option value="60">60</option>
              <option value="80">80</option>
              <option value="100">100</option>
            </select>
            <select name="_Price2" size="1">
              <option value="40">40</option>
              <option value="60">60</option>
              <option value="80">80</option>
              <option value="100">100</option>
              <option value="150">150</option>
              <option value="200">200</option>
              <option value="9999" selected>200+</option>
            </select></td>
          <td> Type
            <select name="_Category" size="1">
              <option value="">All</option>
              <option value="Hotel">Hotel</option>
              <option value="Travel">Travel</option>
            </select>
            <input type="submit" value="Go" name="sortform" /></td>
        </tr>
      </table>
    </form>
    </fieldset>
    <?php }
    ?>
    The error it generates if I do a search and submit (for example):

    Code:
    SELECT * FROM specialofferstable WHERE location LIKE London Croydon AND price BETWEEN 0 AND 9999 ORDER BY priceCould not execute query: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 'Croydon AND price BETWEEN 0 AND 9999 ORDER BY price' at line 1

  • #2
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Code:
    SELECT * FROM specialofferstable WHERE location LIKE 'London Croydon' AND price BETWEEN 0 AND 9999 ORDER BY priceCould not execute query: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 'Croydon AND price BETWEEN 0 AND 9999 ORDER BY price' at line 1
    unquoted string 'London Croydon'

    best regards

  • #3
    Regular Coder
    Join Date
    Sep 2009
    Posts
    165
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Okay, but when I changed the $query to this:

    Code:
    $query .= "location LIKE '$yoursafe_post_location'";
    I got a generic mySQL error: (mysql_fetch_assoc...not a valid resource...)

    I also got this error when I ran another query picking all three categories:

    Code:
    The query is:SELECT * FROM specialofferstable WHERE location LIKE 'London Croydon' AND price BETWEEN 0 AND 200 AND category LIKE Travel ORDER BY priceCould not execute query:Unknown column 'Travel' in 'where clause'
    Bizarrely, if I run a query for an exact string in the Location, for a location which I know exists in the db, it works- for example Manchester brings up 2 results.

    But the query crashes with a generic error if I input a location which doesn't EXACTLY match a location value in the db:

    Code:
    mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource
    Is there a way to change it so that it will closely match the location. For example at the moment if I enter location as "Sheffield" it returns the results, but if I enter "Sheffiel" it crashes with the generic mySQL error.

    It seems that if there's a result, it's fine (as long as it matches the exact value) but if there's no result to the search, it just crashes...
    Last edited by galahad3; 10-12-2009 at 09:10 PM.

  • #4
    New Coder
    Join Date
    Jun 2009
    Posts
    20
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by galahad3 View Post
    Code:
    The query is:SELECT * FROM specialofferstable WHERE location LIKE 'London Croydon' AND price BETWEEN 0 AND 200 AND category LIKE Travel ORDER BY priceCould not execute query:Unknown column 'Travel' in 'where clause'
    I would check the case of your travel column, since your other columns are all lower case.

  • #5
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    are two problems here:
    1. you don't check the results and you assume that everything is ok
    2. syntax errors in mysql query.

    PHP Code:
        // Build SQL Query 
        
    $query "select * from specialofferstable where category like 'travel' ORDER BY price"// specify the table and field names for the SQL query 
        
    $numresults=mysql_query($query); 
         
    // you must check $numresults !!!
        
    $numrows=mysql_num_rows($numresults); 
        
    // get results 
        
    $result mysql_query($query) or die("Couldn't execute query"); 
        
    // display the results returned 
        
    if($result){ // <-- Don't assume that $result is a valid resource( same for $numresult)
            
    while ($rowmysql_fetch_array($result)) { 
         .   .....
            } 
        }else{
           
    // do something in case of error
           
    print '<pre>'.mysql_error().'</pre>';
        } 
    Quote Originally Posted by galahad3 View Post
    Okay, but when I changed the $query to this:

    Code:
    $query .= "location LIKE '$yoursafe_post_location'";
    try:
    PHP Code:
    $query .= "location like '".$yoursafe_post_location."'";
    print 
    '<pre>'.$query.'</pre>'
    you can see, using print, if the query is what you expect( when you write the script, and comment after)

    I got a generic mySQL error: (mysql_fetch_assoc...not a valid resource...)

    I also got this error when I ran another query picking all three categories:

    Code:
    The query is:SELECT * FROM specialofferstable WHERE location LIKE 'London Croydon' AND price BETWEEN 0 AND 200 AND category LIKE Travel ORDER BY priceCould not execute query:Unknown column 'Travel' in 'where clause'
    Bizarrely, if I run a query for an exact string in the Location, for a location which I know exists in the db, it works- for example Manchester brings up 2 results.

    But the query crashes with a generic error if I input a location which doesn't EXACTLY match a location value in the db:

    Code:
    mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource
    Is there a way to change it so that it will closely match the location. For example at the moment if I enter location as "Sheffield" it returns the results, but if I enter "Sheffiel" it crashes with the generic mySQL error.

    It seems that if there's a result, it's fine (as long as it matches the exact value) but if there's no result to the search, it just crashes...
    see the first part of this post for all this.

    best regards

  • #6
    Regular Coder
    Join Date
    Sep 2009
    Posts
    165
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Same problem I'm afraid, if I select either Hotel or Travel from the dropdown- even if I change the name of the option to lower case.

    Error is: (for example)

    Code:
    The query is:SELECT * FROM specialofferstable WHERE location LIKE '%London%' AND price BETWEEN 0 AND 150 AND category LIKE hotel ORDER BY priceCould not execute query:Unknown column 'hotel' in 'where clause'
    Also I don't know why it's picking up "hotel" as the name of the column, the column in the table is called "category" and it can have one of two values, "Hotel" or "Travel", picked from a select.

  • #7
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by galahad3 View Post
    Same problem I'm afraid, if I select either Hotel or Travel from the dropdown- even if I change the name of the option to lower case.

    Error is: (for example)

    Code:
    The query is:SELECT * FROM specialofferstable WHERE location LIKE '%London%' AND price BETWEEN 0 AND 150 AND category LIKE hotel ORDER BY priceCould not execute query:Unknown column 'hotel' in 'where clause'
    -> 'hotel'
    strings must be quoted in mysql!

    best regards

  • #8
    Regular Coder
    Join Date
    Sep 2009
    Posts
    165
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Ok, thanks- seems to work fine with quotes around the $category, however I haven't used % this time, I'm guessing they aren't needed as the users can only pick one of two exact values.

    So, only problem now seems to be if I don't enter a value at all for Location, I get this error:

    Code:
    The query is:SELECT * FROM specialofferstable WHERE AND price BETWEEN 0 AND 9999 ORDER BY priceCould not execute query: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 'AND price BETWEEN 0 AND 9999 ORDER BY price' at line 1

  • #9
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by galahad3 View Post
    Ok, thanks- seems to work fine with quotes around the $category, however I haven't used % this time, I'm guessing they aren't needed as the users can only pick one of two exact values.

    So, only problem now seems to be if I don't enter a value at all for Location, I get this error:

    Code:
    The query is:SELECT * FROM specialofferstable WHERE AND price BETWEEN 0 AND 9999 ORDER BY priceCould not execute query: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 'AND price BETWEEN 0 AND 9999 ORDER BY price' at line 1
    check location with isset, empty, === or !== and skip that part of the query,

    best regards

  • #10
    Regular Coder
    Join Date
    Sep 2009
    Posts
    165
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Where do I put that though? Would it go before this line? Do I just put if { isset ($yoursafe_post_location)}?

    Code:
    if(trim($yoursafe_post_location)!= '') {

  • #11
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by galahad3 View Post
    Where do I put that though? Would it go before this line? Do I just put if { isset ($yoursafe_post_location)}?

    Code:
    if(trim($yoursafe_post_location)!= '') {
    I don't know the source of that variable $yoursafe_post_location so take the decision:
    - use isset if $yoursafe_post_location don't exists
    - empty or !== if is ''

    I'm not sure but is probably like that:
    PHP Code:
    if($yoursafe_post_location !== ''){
      
    $query .= "location LIKE '$yoursafe_post_location' ";

    put a space at the end to be sure that $yoursafe_post_location will be separate from the rest of the words in query.

    best regards

  • Users who have thanked oesxyl for this post:

    galahad3 (10-12-2009)

  • #12
    Regular Coder
    Join Date
    Sep 2009
    Posts
    165
    Thanks
    16
    Thanked 0 Times in 0 Posts
    I will try that, thanks.

    Only issue I have now is that I also need the initial search page (i.e before someone does a custom search using the various categories) to show ALL the records in the table, first all the records where category = Hotel and then in another section all the records where category = Travel.

    I had this set up in a previous version of the page but I'm not sure where I should put the while loops and connections on this new page. Also when someone does their custom search the page should then only display the search results, rather than all the records.

    This is the code I have for displaying all the records where category is Hotel:

    Code:
    echo '<h2>Hotel special offers</h2>';
    	//Include the PS_Pagination class
    	include('manager/special_offers/inc/ps_pagination.php');
    	//Connect to mysql db
    	$conn = mysql_connect('localhost', 'login', 'password');
    	mysql_select_db('specialoffers_db',$conn);
    	$sql = "select * from specialofferstable WHERE category like 'hotel' ORDER BY price";
    	//Create a PS_Pagination object
    	$pager = new PS_Pagination($conn, $sql, 10, 4, 'param1=valu1&param2=value2');
    	//The paginate() function returns a mysql
    	//result set for the current page
    	$rs = $pager->paginate();
    	//Loop through the result set
    	while ($row= mysql_fetch_assoc($rs)) {
      $title = $row["category"];
      $title2 = $row["company_hotel"];
      $title3 = $row["location"];
      $title4 = $row["offer"];
      $title5 = $row["price"];
      $title6 = $row["offerends"];
      $title7 = $row["mobile"];
      $dateformat = date("M j Y" ,strtotime($title6)); 
    
    
    echo '<h3>'.$title2.' | '.$title3.' <em class=grey>(Offer ends: '.$dateformat.')</em></h3><h4>£'.$title5.' <strong class=call>Call 0844 793 7302</strong></h4>
    <p>'.$title4.'</p><hr />' ;
    
    $count++ ;
    }
    	//Display the navigation
    	echo $pager->renderFullNav();
    And then a similar query and title would follow, but for category being Travel.

    How can this be set up on the new page?


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •