...

View Full Version : PHP search form with multiple inputs



galahad3
10-12-2009, 08:54 PM
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...



<?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):




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

oesxyl
10-12-2009, 09:28 PM
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

galahad3
10-12-2009, 09:50 PM
Okay, but when I changed the $query to this:



$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:



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:



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

fifth
10-12-2009, 10:14 PM
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.

oesxyl
10-12-2009, 10:23 PM
are two problems here:
1. you don't check the results and you assume that everything is ok
2. syntax errors in mysql query.



// 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 ($row= mysql_fetch_array($result)) {
. .....
}
}else{
// do something in case of error
print '<pre>'.mysql_error().'</pre>';
}



Okay, but when I changed the $query to this:



$query .= "location LIKE '$yoursafe_post_location'";


try:


$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:



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:



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

galahad3
10-12-2009, 10:25 PM
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)



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.

oesxyl
10-12-2009, 10:27 PM
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)



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

galahad3
10-12-2009, 10:35 PM
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:



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

oesxyl
10-12-2009, 10:42 PM
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:



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

galahad3
10-12-2009, 10:46 PM
Where do I put that though? Would it go before this line? Do I just put if { isset ($yoursafe_post_location)}?



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

oesxyl
10-12-2009, 10:52 PM
Where do I put that though? Would it go before this line? Do I just put if { isset ($yoursafe_post_location)}?



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:


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

galahad3
10-13-2009, 09:37 AM
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:



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?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum