Multi-Search Fields and Pagination Help Please
Posted: Mon Mar 08, 2010 4:21 am
Hello everyone
I'm a newbie webdeveloper and new to Php and Mysql.
Also, i'm new to Phpdn.
I'm having difficulty solving my multi-search script problem.
My script is based on a tutorial on pagination at phpfreaks.com
The problem is that my script does not correctly show "page 2".
It does show page 1 correctly.
It overwrites the $_SESSION variables when going to page 2, because it displays the message for when no values are entered.
Don't be put off by the number of lines of code, it is really simple:
The script searches and paginates based on 3 input fields,
and sorts alphabetically ascending or descending,
and shows the number of rows, default is 10.
It uses a series of elsif statements to determine the Count Query ( The 1st group of mysql_queries ), which is used to count the total number of pages, and matching elseif 's to determine the Search Query ( The 2nd group of mysql_queries ), which is used to search for the values entered in the search boxes.
Here is the Full Script Code:
One more thing:
I've renamed all the variables and database, table, ect. for security reasons. Also, I created a new database & table to search from, but the script now gives following error:
Fatal error: SQL in /opt/lampp/htdocs/rssearch.php on line
254.
The original script works fine, except for overwriting the SESSION variables.
After it is Solved, I'll put it in the Code Snippets Section because a multiple-field search script is a rare thing it seems, and it is also very
essential and useful feature for one's website.
All help is greatly appreciated
Thank You
I'm a newbie webdeveloper and new to Php and Mysql.
Also, i'm new to Phpdn.
I'm having difficulty solving my multi-search script problem.
My script is based on a tutorial on pagination at phpfreaks.com
The problem is that my script does not correctly show "page 2".
It does show page 1 correctly.
It overwrites the $_SESSION variables when going to page 2, because it displays the message for when no values are entered.
Don't be put off by the number of lines of code, it is really simple:
The script searches and paginates based on 3 input fields,
and sorts alphabetically ascending or descending,
and shows the number of rows, default is 10.
It uses a series of elsif statements to determine the Count Query ( The 1st group of mysql_queries ), which is used to count the total number of pages, and matching elseif 's to determine the Search Query ( The 2nd group of mysql_queries ), which is used to search for the values entered in the search boxes.
Here is the Full Script Code:
Code: Select all
<?php
session_start();
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<head>
<title>
</title>
</head>
<html>
<body>
<form method="post" action="http://localhost/search.php">
<p>
Brand Name Search:
<input name="bnsearch" size="20" maxlength="80">
</input>
</p>
<p>
Product Search:
<input name="psearch" size="20" maxlength="80">
</input>
</p>
<p>
Shop Location Search:
<input name="slsearch" size="20" maxlength="80">
</input>
</p>
</p>
<p>
Display Number On Page:
<select name="rowsperpage">
<option value="10" selected="">10</option>
<option value="20">20</option>
<option value="30">30</option>
<option value="40">40</option>
<option value="50">50</option>
<option value="100">100</option>
</select>
</p>
<p>
Sort Order:
<select name="sortorder">
<option value="ID ASC">A - Z</option>
<option value="ID DESC">Z - A</option>
</select>
</p>
<button TYPE="submit" NAME="submitform">SEARCH!
</button>
</form>
<?php
$conn = mysql_connect('localhost','root','') or trigger_error("SQL", E_USER_ERROR);
$db = mysql_select_db('shopdb',$conn) or trigger_error("SQL", E_USER_ERROR);
if (isset($_POST['submitform']))
{
unset($_SESSION);
$_SESSION['rowsperpage'] = @$_POST['rowsperpage'];
$_SESSION['sortorder'] = @$_POST['sortorder'];
}
$_SESSION['rowsperpage'];
$_SESSION['sortorder'];
if(isset($_SESSION['sqlsearch']) And isset($_SESSION['sqlcount']))
{
$_SESSION['sqlsearch'];
$_SESSION['sqlcount'];
}
if (!isset($_SESSION['bnsfil']) And !empty($_POST['bnsearch']))
{
$_SESSION['bnsfil'] = preg_replace('/[^a-zA-Z0-9]/', '', $_POST['bnsearch']);
}
elseif(!isset($_SESSION['bnsfil']) And empty($_POST['bnsfil']))
{
$_SESSION['bnsfil'] = "";
}
if (!isset($_SESSION['psearch']) And !empty($_POST['psearch']))
{
$_SESSION['psfil'] = preg_replace('/[^a-zA-Z0-9]/', '', $_POST['psearch']);
}
elseif (!isset($_SESSION['psearch']) And empty($_POST['psearch']))
{
$_SESSION['psfil'] = "" ;
}
if (!isset($_SESSION['slsfil']) And !empty($_POST['slsearch']))
{
$_SESSION['slsfil'] = preg_replace('/[^a-zA-Z0-9]/', '', $_POST['slsearch']);
}
elseif(!isset($_SESSION['slsfil']) And empty($_POST['slsfil']))
{
$_SESSION['slsfil'] = "";
}
var_dump ($_SESSION['bnsfil']);
var_dump ($_SESSION['psfil']);
var_dump ($_SESSION['slsfil']);
if (!empty($_SESSION['bnsfil']) And empty($_SESSION['psfil']) And empty($_SESSION['slsfil']))
{
$_SESSION['sqlcount'] = "SELECT COUNT (BRANDNAME) FROM shopdbtable WHERE `BRANDNAME` REGEXP '$_SESSION[bnsfil]'";
}
if (empty($_SESSION['bnsfil']) And !empty($_SESSION['psfil']) And empty($_SESSION['slsfil']))
{
$_SESSION['sqlcount'] = "SELECT COUNT(PRODUCT) FROM shopdbtable WHERE `PRODUCT` REGEXP '$_SESSION[psfil]'";
}
if (empty($_SESSION['bnsfil']) And empty($_SESSION['psfil']) And !empty($_SESSION['slsfil']))
{
$_SESSION['sqlcount'] = "SELECT COUNT(SHOPLOCATION) FROM shopdbtable WHERE `SHOPLOCATION` REGEXP '$_SESSION[slsfil]'";
}
if (!empty($_SESSION['bnsfil']) And !empty($_SESSION['psfil']) And !empty($_SESSION['slsfil']))
{
$_SESSION['sqlcount'] = "SELECT COUNT(*) FROM shopdbtable WHERE `BRANDNAME` REGEXP '$_SESSION[bnsfil]' AND `PRODUCT` REGEXP '$_SESSION[psfil]' AND `SHOPLOCATION` REGEXP '$_SESSION[slsfil]'";
}
if (!empty($_SESSION['bnsfil']) And empty($_SESSION['psfil']) And !empty($_SESSION['slsfil']))
{
$_SESSION['sqlcount'] = "SELECT COUNT(*) FROM shopdbtable WHERE `BRANDNAME` REGEXP '$_SESSION[bnsfil]' AND `SHOPLOCATION` REGEXP'$_SESSION[slsfil]'";
}
if (empty($_SESSION['bnsfil']) And !empty($_SESSION['psfil']) And !empty($_SESSION['slsfil']))
{
$_SESSION['sqlcount'] = "SELECT COUNT(*) FROM shopdbtable WHERE `PRODUCT` REGEXP '$_SESSION[psfil]' AND `SHOPLOCATION` REGEXP'$_SESSION[slsfil]'";
}
if (!empty($_SESSION['bnsfil']) And !empty($_SESSION['psfil']) And empty($_SESSION['slsfil']))
{
$_SESSION['sqlcount'] = "SELECT COUNT(*) FROM shopdbtable WHERE `BRANDNAME` REGEXP '$_SESSION[bnsfil]' AND `PRODUCT` REGEXP'$_SESSION[psfil]'";
}
if ( empty($_SESSION['bnsfil']) And empty($_SESSION['psfil']) And empty($_SESSION['slsfil']))
{
$_SESSION['sqlcount'] = "Your search query did not match any records. Please enter another value.";
}
var_dump ($_SESSION['sqlcount']);
$result = mysql_query($_SESSION['sqlcount'], $conn) or trigger_error("SQL", E_USER_ERROR);
$r = mysql_fetch_row($result);
$numrows = $r[0];
$totalpages = ceil($numrows / $_SESSION['rowsperpage']);
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage']))
{
$currentpage = (int) $_GET['currentpage'];
}
else
{
$currentpage = 1;
}
if ($currentpage > $totalpages)
{
$currentpage = $totalpages;
}
if ($currentpage < 1)
{
$currentpage = 1;
}
$offset = ($currentpage - 1) * $_SESSION['rowsperpage'];
if (!empty($_SESSION['bnsfil']) And empty($_SESSION['psfil']) And empty($_SESSION['slsfil']))
{
$_SESSION['sqlsearch'] = "SELECT ID, BRANDNAME, FROM shopdbtable WHERE `BRANDNAME` REGEXP '$_SESSION[bnsfil]' ORDER BY $_SESSION[sortorder] LIMIT $offset, $_SESSION[rowsperpage]";
}
if ( empty($_SESSION['bnsfil']) And !empty($_SESSION['psfil']) And empty($_SESSION['slsfil']))
{
$_SESSION['sqlsearch'] = "SELECT ID, PRODUCT, FROM shopdbtable WHERE `PRODUCT` REGEXP '$_SESSION[psfil]' ORDER BY $_SESSION[sortorder] LIMIT $offset, $_SESSION[rowsperpage]";
}
if ( empty($_SESSION['bnsfil']) And empty($_SESSION['psfil']) And !empty($_SESSION['slsfil']))
{
$_SESSION['sqlsearch'] = "SELECT ID, SHOPLOCATION, FROM shopdbtable WHERE `SHOPLOCATION` REGEXP '$_SESSION[slsfil]' ORDER BY $_SESSION[sortorder] LIMIT $offset, $_SESSION[rowsperpage]";
}
if ( !empty($_SESSION['bnsfil']) And !empty($_SESSION['psfil']) And !empty($_SESSION['slsfil']))
{
$_SESSION['sqlsearch'] = "SELECT ID, BRANDNAME, PRODUCT, SHOPLOCATION, FROM shopdbtable WHERE `BRANDNAME` REGEXP '$_SESSION[bnsfil]' AND `PRODUCT` REGEXP '$_SESSION[psfil]' AND `SHOPLOCATION` REGEXP '$_SESSION[slsfil]' ORDER BY $_SESSION[sortorder] LIMIT $offset, $_SESSION[rowsperpage]";
}
if ( !empty($_SESSION['bnsfil']) And empty($_SESSION['psfil']) And !empty($_SESSION['slsfil']))
{
$_SESSION['sqlsearch'] = "SELECT ID, BRANDNAME, SHOPLOCATION, FROM shopdbtable WHERE `BRANDNAME` REGEXP '$_SESSION[bnsfil]' AND `SHOPLOCATION` REGEXP '$_SESSION[slsfil]' ORDER BY $_SESSION[sortorder] LIMIT $offset, $_SESSION[rowsperpage]";
}
if ( empty($_SESSION['bnsfil']) And !empty($_SESSION['psfil']) And !empty($_SESSION['slsfil']))
{
$_SESSION['sqlsearch'] = "SELECT ID, PRODUCT, SHOPLOCATION, FROM shopdbtable WHERE `PRODUCT` REGEXP '$_SESSION[psfil]' AND `SHOPLOCATION` REGEXP '$_SESSION[slsfil]' ORDER BY $_SESSION[sortorder] LIMIT $offset, $_SESSION[rowsperpage]";
}
if ( !empty($_SESSION['bnsfil']) And !empty($_SESSION['psfil']) And empty($_SESSION['slsfil']))
{
$_SESSION['sqlsearch'] = "SELECT ID, BRANDNAME, PRODUCT, FROM shopdbtable WHERE `BRANDNAME` REGEXP '$_SESSION[bnsfil]' AND `PRODUCT` REGEXP '$_SESSION[psfil]' ORDER BY $_SESSION[sortorder] LIMIT $offset, $_SESSION[rowsperpage]";
}
/*
if (empty($_SESSION['bnsfil']) And empty($_SESSION['psfil']) And empty($_SESSION['slsfil']))
{
echo "Enter another value.";
}
*/
var_dump ($_SESSION['sqlsearch']);
$result = mysql_query($_SESSION['sqlsearch'], $conn) or trigger_error("SQL", E_USER_ERROR);
while ($list = mysql_fetch_assoc($result))
{
echo $list['ID'] . " : " . $list['BRANDNAME'] . "<br />";
}
$range = 3;
if ($currentpage > 1)
{
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";
$prevpage = $currentpage - 1;
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";
}
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++)
{
if (($x > 0) && ($x <= $totalpages))
{
if ($x == $currentpage)
{
echo " [<b>$x</b>] ";
}
else
{
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
}
}
}
if ($currentpage != $totalpages)
{
$nextpage = $currentpage + 1;
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> ";
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
}
mysql_close();
?>
</body>
</html>
One more thing:
I've renamed all the variables and database, table, ect. for security reasons. Also, I created a new database & table to search from, but the script now gives following error:
Fatal error: SQL in /opt/lampp/htdocs/rssearch.php on line
254.
The original script works fine, except for overwriting the SESSION variables.
After it is Solved, I'll put it in the Code Snippets Section because a multiple-field search script is a rare thing it seems, and it is also very
essential and useful feature for one's website.
All help is greatly appreciated
Thank You