Jump to content

Multi-Field Search and Pagination Help Please!

phpwolf

By phpwolf
in PHP Coding Help

Recommended Posts

phpwolf Newbie

phpwolf

Posted
Posted

Greetings Everyone

 

I'm having a seemingly unsolvable problem with my search and pagination script.

//www.phpfreaks.com/forums/Smileys/nrg_alpha/shrug.gif

http://www.phpfreaks.com/forums/Smileys/nrg_alpha/shrug.gifhttp://www.phpfreaks.com/forums/Smileys/nrg_alpha/shrug.gifhttp://www.phpfreaks.com/forums/Smileys/nrg_alpha/shrug.gifhttp://www.phpfreaks.com/forums/Smileys/nrg_alpha/shrug.gifhttp://www.phpfreaks.com/forums/Smileys/nrg_alpha/shrug.gif

 

Information about the script:

My script based on crayon violent tut in the tutorials section here on phpfreaks.

Feel free to use it, when it is solved.

 

 

A little about myself and my project:

This is my first PHP & MYSQL project and also my introduction to Server-Side scripting.

 

It is really great to be able to have so much control over the website and to be able to do things that you want, but can't do in HTML, but it gets really frustrating when faced with a problem that you can't solve immediatly or by yourself.

 

However, it is also a great opportunity to learn when faced with problems.

 

I've learned about SESSIONS and Conditional Statements ( If, elesif, switch, break, for,  ) and about how to write code differently but get the same result ( Eg:  elseif and switch ).

Most of all, I think I've got acquainted with

PAAAAAAAAAATTTTTTTTTTIIIIIIIIIEEEEEEEEEEENNNNNNNNNCCCCCCCCCEEEEEEEEEEE, DETERMINATION AND BELIEVING THAT I CAN DO THIS. 

( and getting    [sqaure] [eyes] )

 

But like all of you guys we work with time constrictions and I've now spent way too much time than what is neccassary trying to solve these problems, it is now time for this problem to be solved so I can move on to another section of the website design, otherwise it's not getting done.

 

This script is really important because my whole website is based on it being able to handle 4 search values.

( The script below is only 3 to make it easier to work with )

 

 

 

This is why forums like phpfreaks is so great because we have the chance to get help and get things done.

 

I REEEEEEEEEEEEEEEEEEAAAAAAAAAAAAALLLLLLYYYYY hope you guys & gurus can help me out.

 

 

 

 

 

Anyways, on with the problemsolving!

 

Problem 1:  It doesn't display page 2!

                  It correctly displays page 1. 

                  ( It somehow overwrites the session variables, because it displays the message

                  for when no value is entered ).

                  How can the session variables be passed correctly and not be overwritten?

 

 

Problem 2:  For sequrity reasons I've renamed all the variables and tablefields and making

a new database and table,       

            etc...     

            But the script now gives following error:

            Fatal error: SQL in /opt/lampp/htdocs/rssearch.php on line

            254. 

            What are possible causes?

            ( My original script works correctly, except for overwriting

            the session variables )

 

 

Here is the Full Script:

 

 

<?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>

 

 

 

All your help and insights are greatly appreciatedhttp://www.phpfreaks.com/forums/Smileys/nrg_alpha/happy-02.gif

Thank you

 

 

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.