Much simplified and optimized search code...

phpwolf · March 10, 2010

Hi all,

Below is a much simplified and optimized version of my other search codes, to make it easier to find and solve the error / problem.

The problem is that the script does not page correctly.

It does however show page 1 correctly.

Any suggestions or possible solutions guys?

I've done all the tutorials I have found, and also used free code snippets.

They simply don't cover multiple keyword searching with user selected sortorder or rowsperpage.

This version uses the "build-query method" where the seach query is built when the searchvariables are set.

This is much more efficient than the elseif approach.

Here is the database setup:

Database name: shopdb

Table name: shopdbtable

Columns: ID, STATUS, PRODUCT

Add a STATUS column with phpmyadmin:

In structure screen,

Make a new field: STATUS

Type: ENUM

Length Values: 'Y','N'

Default: As defined: Y

Here is the optimized version:

<?php
session_start();
?>
<html>
<body>
<form method="post" action="http://localhost/rssearch.php">
<p>
Product Search:
<input name="psearch" size="20" maxlength="80">
</input>
</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="PRODUCT ASC">A - Z</option>
<option value="PRODUCT 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['psearchfil'] = @$_POST['psearchfil'];
}
var_dump($_SESSION['psearchfil']);
var_dump($_SESSION['rowsperpage']);
var_dump($_SESSION['sortorder']);


if (!isset($_SESSION['psearchfil']) And !empty($_POST['psearch']))

{
$_SESSION['psearchfil'] = preg_replace('/[^a-zA-Z0-9]/', '', $_POST['psearch']);
}

if (isset($_SESSION['psearchfil']))

{
$sqlcount = "SELECT COUNT(PRODUCT) FROM shopdbtable WHERE `PRODUCT` REGEXP '$_SESSION[psearchfil]'";
}

var_dump($sqlcount);
$result = mysql_query($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'];

$sqlsearch = "SELECT ID, STATUS, PRODUCT FROM shopdbtable WHERE STATUS='Y' "; 

if (isset($_SESSION['psearchfil'])) 
{
$sqlsearch .= " AND `PRODUCT` REGEXP '$_SESSION[psearchfil]'  ";
}

if (isset($_SESSION['psearchfil'])) 
{
$sqlsearch .= " ORDER BY $_SESSION[sortorder] LIMIT $offset, $_SESSION[rowsperpage] ";
}

var_dump($sqlsearch);

$result = mysql_query($sqlsearch, $conn) or trigger_error("SQL", E_USER_ERROR);

while ($list = mysql_fetch_assoc($result)) {
   echo $list['ID'] . $list['PRODUCT'] . "<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> ";
}
var_dump ($sqlsearch);
var_dump ($_SESSION['psearchfil']);
mysql_close();
?>
</body>
</html>

Any possible solutions or suggestions or guidance in solving this issue is much appreciated

Thank you

Sign In

Much simplified and optimized search code...

Recommended Posts

phpwolf

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information