Re: Problem with search when add BETWEEN to query
Thank you very much for replying. I have done some modification to the script since I last posted. Although, it was not exactly what I would like it to be, I was able to get it to work by using >= and <= instead of BETWEEN. And, after trying to follow your suggestions with not much success due to my limited knowledge, Here is what the script looks like.
<?
//Process Form
if (isset($_GET['keyword']))
{
$keyword = "%".$_GET['keyword']."%";
}
else
{
$keyword = "%";
}
$type=$_GET['type'];
$material=$_GET['material'];
$gem=$_GET['gem'];
$brand=$_GET['brand'];
$seller=$_GET['seller'];
$max_price=$_GET['max_price'];
//This doesn't seem to work. I have to set an initial value in the field:(
if (isset($_GET['max_price']))
{
$max_price=$_GET['max_price'];
}
else
{
$max_price="1000000";
}
if (isset($_GET['min_price']))
{
$min_price=$_GET['min_price'];
}
else
{
$min_price="0";
}
require('connect.php');
$tbl_name="product"; // Table name
//Most of the following parts are built by Dreamweaver :)>
$currentPage = $_SERVER["PHP_SELF"];
$maxRows_search_results = 12;
$pageNum_search_results = 0;
if (isset($_GET['pageNum_search_results'])) {
$pageNum_search_results = $_GET['pageNum_search_results'];
}
$startRow_search_results = $pageNum_search_results * $maxRows_search_results;
//This is the only part of your suggestions which I successfully followed:)
mysql_select_db($db_name, $connection);
$query_search_results = "SELECT * FROM $tbl_name WHERE ";
$query_search_results .= "(description LIKE '".mysql_real_escape_string($keyword)."') AND ";
$query_search_results .= "(type LIKE '".mysql_real_escape_string($type)."') AND ";
$query_search_results .= "(material LIKE '".mysql_real_escape_string($material)."') AND ";
$query_search_results .= "(gem LIKE '".mysql_real_escape_string($gem)."') AND ";
$query_search_results .= "(brand LIKE '".mysql_real_escape_string($brand)."') AND ";
$query_search_results .= "(seller LIKE '".mysql_real_escape_string($seller)."') AND ";
$query_search_results .= "(regular_price <= '".mysql_real_escape_string($max_price)."') AND ";
$query_search_results .= "(regular_price >= '".mysql_real_escape_string($min_price)."')";
$query_limit_search_results = sprintf("%s LIMIT %d, %d", $query_search_results, $startRow_search_results, $maxRows_search_results);
$search_results = mysql_query($query_limit_search_results, $connection) or die(mysql_error());
$row_search_results = mysql_fetch_assoc($search_results);
if (isset($_GET['totalRows_search_results'])) {
$totalRows_search_results = $_GET['totalRows_search_results'];
} else {
$all_search_results = mysql_query($query_search_results);
$totalRows_search_results = mysql_num_rows($all_search_results);
}
$totalPages_search_results = ceil($totalRows_search_results/$maxRows_search_results)-1;
$queryString_search_results = "";
if (!empty($_SERVER['QUERY_STRING'])) {
$params = explode("&", $_SERVER['QUERY_STRING']);
$newParams = array();
foreach ($params as $param) {
if (stristr($param, "pageNum_search_results") == false &&
stristr($param, "totalRows_search_results") == false) {
array_push($newParams, $param);
}
}
if (count($newParams) != 0) {
$queryString_search_results = "&" . htmlentities(implode("&", $newParams));
}
}
$queryString_search_results = sprintf("&totalRows_search_results=%d%s", $totalRows_search_results, $queryString_search_results);
$count=mysql_num_rows($search_results);
if($count<1)
{
echo "<br/>Sorry, there are no products match your request.<br />";
}
else
{
do
{
$folder = $row_search_results['image_folder'];
$image = $row_search_results['image'];
$name = $row_search_results['link2'];
$price = $row_search_results['regular_price'];
$seller = $row_search_results['seller'];
echo '<table class="product" ><tr><td class="product">';
echo '<img class="product" src="'.$folder.'/'.$image.'" /><br />';
echo $name."<br />";
echo "$".$price." @ ".$seller."<br />";
echo "</td></tr></table>";
}
?>
END
// I'd also try to optimize, as you suggested, by removing the empty field from the query but I have not been able to get it to work:(
<?
$keyword = "%".$_GET['keyword']."%";
$type=$_GET['type'];
$material=$_GET['material'];
$gem=$_GET['gem'];
$brand=$_GET['brand'];
$seller=$_GET['seller'];
$max_price=$_GET['max_price'];
$min_price=$_GET['min_price'];
If (!isset($_GET['keyword']))
{
$sql = "SELECT * FROM $tbl_name WHERE ";
$sql .= "(type LIKE '".mysql_real_escape_string($type)."') AND ";
$sql .= "(material LIKE '".mysql_real_escape_string($material)."') AND ";
$sql .= "(gem LIKE '".mysql_real_escape_string($gem)."') AND ";
$sql .= "(brand LIKE '".mysql_real_escape_string($brand)."') AND ";
$sql .= "(seller LIKE '".mysql_real_escape_string($seller)."') AND ";
$sql .= "(regular_price <= '".mysql_real_escape_string($max_price)."') AND ";
$sql .= "(regular_price >= '".mysql_real_escape_string($min_price)."')";
}
else if (!isset($_GET['max_price']))
{
$sql = "SELECT * FROM $tbl_name WHERE ";
$sql .= "(description LIKE '".mysql_real_escape_string($keyword)."') AND ";
$sql .= "(type LIKE '".mysql_real_escape_string($type)."') AND ";
$sql .= "(material LIKE '".mysql_real_escape_string($material)."') AND ";
$sql .= "(gem LIKE '".mysql_real_escape_string($gem)."') AND ";
$sql .= "(brand LIKE '".mysql_real_escape_string($brand)."') AND ";
$sql .= "(seller LIKE '".mysql_real_escape_string($seller)."') AND ";
$sql .= "(regular_price >= '".mysql_real_escape_string($min_price)."')";
}
else if (!isset($_GET['min_price']))
{
$sql = "SELECT * FROM $tbl_name WHERE ";
$sql .= "(description LIKE '".mysql_real_escape_string($keyword)."') AND ";
$sql .= "(type LIKE '".mysql_real_escape_string($type)."') AND ";
$sql .= "(material LIKE '".mysql_real_escape_string($material)."') AND ";
$sql .= "(gem LIKE '".mysql_real_escape_string($gem)."') AND ";
$sql .= "(brand LIKE '".mysql_real_escape_string($brand)."') AND ";
$sql .= "(seller LIKE '".mysql_real_escape_string($seller)."') AND ";
$sql .= "(regular_price <= '".mysql_real_escape_string($max_price)."')";
}
else
{
$sql = "SELECT * FROM $tbl_name WHERE ";
$sql .= "(description LIKE '".mysql_real_escape_string($keyword)."') AND ";
$sql .= "(type LIKE '".mysql_real_escape_string($type)."') AND ";
$sql .= "(material LIKE '".mysql_real_escape_string($material)."') AND ";
$sql .= "(gem LIKE '".mysql_real_escape_string($gem)."') AND ";
$sql .= "(brand LIKE '".mysql_real_escape_string($brand)."') AND ";
$sql .= "(seller LIKE '".mysql_real_escape_string($seller)."') AND ";
$sql .= "(regular_price <= '".mysql_real_escape_string($max_price)."') AND ";
$sql .= "(regular_price >= '".mysql_real_escape_string($min_price)."')";
}
?>
Please advise:)