Multi-Search Fields and Pagination Help Please

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
phpwolf
Forum Newbie
Posts: 2
Joined: Mon Mar 08, 2010 3:34 am

Multi-Search Fields and Pagination Help Please

Post by phpwolf »

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:

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
phpwolf
Forum Newbie
Posts: 2
Joined: Mon Mar 08, 2010 3:34 am

Re: Multi-Search Fields and Pagination Help Please

Post by phpwolf »

Hi guys

Just a little error:
Change this line:

Code: Select all

 
<form method="post" action="http://localhost/search.php">
 
to:

Code: Select all

 
<form method="post" action="http://localhost/rssearch.php">
 
or

rename the file for the code to search.php

Does anyone know of a good Multi-Field Search and Dynamic Pagination Class???
Post Reply