Searching by multiple categories on same page

PHP search using single page but multiple categories

[galahad2]

Hi, I'm trying to set up a page that displays db results but which has the ability to search by various fields from dropdowns at the top of the page.

At the moment I have it set up so each search category has its own form and its own "Go" button i.e Submit button, which then goes and runs its query against a mySQL database and uses a different page.

What I need to try and do is have just a single Submit button that works with all of the forms i.e all the queries, so a user could search by any of the categories, or even a combination of any of them together, and then just click the same Submit button each time to bring up the desired results.

The code I currently have is: (ignore the pagination code as this just splits the results across pages, already works so isn't part of the issue)

[php]

<?php
include ('inc/dbconnect.php');

echo '<fieldset>
<legend>Search special offers</legend>
<table><tr>
<td><form action="specialoffers_sortbylocation.html" method="post" name="sortbylocation">
Location <input name="_Location" /> <input type="submit" value="Go" name="sortbylocation" /></form></td>

<td><form action="specialoffers_sortbyprice.html" method="post" name="sortbyprice">
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> <input type="submit" value="Go" name="sortbyprice" /></form></td>

<td><form action="specialoffers_sortbycategory.html" method="post" name="sortbycategory">
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="sortbycategory" /></form></td>

</tr></table></fieldset>

';

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 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++ ;
}
?>

[/php]

[tomda]

Not really a PHP issue.

I don't get it, why can't you just combine everything under one single <form> ?

[galahad2]

Because there needs to be a submit for each category or at least that was the only way I could find of doing it, so I made different forms so that I could have various submit buttons that go to pages and run the appropriate query.

But I neeed to figure a way of doing it with just a single submit button that can work with all the various form fields together and submit a single query based on that.

[tomda]

Sorry to say but my guess is that you are wrong.

You should have only one form.

<form action="specialoffers.php">
<input LOCATION>
<input PRICE1>
<input PRICE2>
<input CATEGORY>

Then in your result page in php

1/ Check if location is filled
2/ Check if prices are filled
3/ Check if category are filled

4/ Create your SINGLE request this way

-----------------

$query = "SELECT * FROM specialofferstable WHERE ";
$and = "0";
if(!empty(LOCATION)) {
$and = "1";
$query .= " location LIKE '%".$yoursafe_post_location."%' "
}
if(!empty(PRICE1) && !empty(PRICE2) {
$and = "1";
if(!empty($and)) {$query .= " AND ";}
$query .= " price BETWEEN '".$yoursafe_post_price1."%' AND '".$yoursafe_post_price2."%' "
}
if(!empty(CATEGORY) {
$and = "1";
if(!empty($and)) {$query .= " AND ";}
$query .= " category = '".$yoursafe_post_category."%'"
}
$query .= " ORDER BY price"; 
echo $query;

-----------------

Hope you understand the logic

[galahad2]

Thanks, I've tried this but it's now just bringing up a blank page. This is the code I'm now using:

<?php
include ('manager/special_offers/inc/dbconnect.php');

echo '<fieldset>
<legend>Search special offers</legend>
<table><tr>
<td><form action="specialoffersall.html" method="post" name="sortform">
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" /></form></td>

</tr></table></fieldset>

';

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);

$query = "SELECT * FROM specialofferstable WHERE ";
$and = "0";

if(!empty(_Location)) {
$and = "1";
$query .= " location LIKE '%".$yoursafe_post_location."%' "
}

if(!empty(_Price1) && !empty(_Price2) {
$and = "1";
if(!empty($and)) {$query .= " AND ";}
$query .= " price BETWEEN '".$yoursafe_post_price1."%' AND '".$yoursafe_post_price2."%' "
}

if(!empty(_Category) {
$and = "1";
if(!empty($and)) {$query .= " AND ";}
$query .= " category = '".$yoursafe_post_category."%'"
}

$query .= " 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 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++ ;
}
?>

[andrewsmd]

A "blank" page usually means a syntax error. Just off of the top of my head I see
if(!empty(_Location)) {
$and = "1";
$query .= " location LIKE '%".$yoursafe_post_location."%' "
}

Should be
if(!empty(_Location)) {
$and = "1";
$query .= " location LIKE '%".$yoursafe_post_location."%' ";//added a ;
}

if(!empty(_Price1) && !empty(_Price2) {
$and = "1";
if(!empty($and)) {$query .= " AND ";}
$query .= " price BETWEEN '".$yoursafe_post_price1."%' AND '".$yoursafe_post_price2."%' "; //added a ;
}

if(!empty(_Category) {
$and = "1";
if(!empty($and)) {$query .= " AND ";}
$query .= " category = '".$yoursafe_post_category."%'";//added a ;
}

[tomda]

Andrew is right, forgot the ; in my example...

@galahad2 - This is just an example so that you understand how to create a single query based on different POST variable. Please, adapt the code to your own code (e.g. change $yoursafe_post_location = $_POST['location]';)

[galahad2]

Okay, I've added declarations for the POST variables at the top, and also ensured the query strings have the ending ; in place, but I'm still getting a blank page...

This is the code- any ideas if anything else needs doing to it?

<?php
include ('manager/special_offers/inc/dbconnect.php');
echo '<fieldset>
<legend>Search special offers</legend>
<table><tr>
<td><form action="specialoffersall.html" method="post" name="sortform">
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" /></form></td>
</tr></table></fieldset>
';
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);
$yoursafe_post_location = $_POST['_Location'];
$yoursafe_post_price1 = $_POST['_Price1'];
$yoursafe_post_price2 = $_POST['_Price2'];
$yoursafe_post_category = $_POST['_Category'];
$query = "SELECT * FROM specialofferstable WHERE "; 
$and = "0"; 
if(!empty(_Location)) { 
$and = "1"; 
$query .= " location LIKE '%".$yoursafe_post_location."%' ";
} 
if(!empty(_Price1) && !empty(_Price2) { 
$and = "1"; 
if(!empty($and)) {$query .= " AND ";} 
$query .= " price BETWEEN '".$yoursafe_post_price1."%' AND '".$yoursafe_post_price2."%' ";
} 
if(!empty(_Category) { 
$and = "1"; 
if(!empty($and)) {$query .= " AND ";} 
$query .= " category = '".$yoursafe_post_category."%'";
} 
$query .= " 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 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++ ;
}
?>

[galahad2]

Anyone know of any reason why I'm still getting the blank page?