PHP Pagination & Variables
Posted: Sat May 05, 2007 6:49 am
I have a html form with two drop downs to filter a database and then return its results. I have below my script which I have tried to implemtn pagination into. MY problem is that the pagination only works properly if "SelectManufacutrer" and "SelectColour" are selected meaning that it brings back all of the records. If I filter it by the colour Red, then only the first page works and the pagination doesnt work then.. it loses its variables and returns : Failed2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= 'Red' LIMIT 25, 25' at line 1
Im a noob, a beginner. I am learning php. Can someone help me tidy up the code and implement a working pagination where it will still paginate when the results are filtered.
As you can see, ive tried passing the variables through the URL using Manufacturer=$Manufactuer&colour...
Can someone check to see if i have made any mistakes.
The prolbem only occours if I select something in the drop downs, then the second page of results doesnt work. It works perfcetley fine when all the records are returned.
Thanks in advance
Im a noob, a beginner. I am learning php. Can someone help me tidy up the code and implement a working pagination where it will still paginate when the results are filtered.
As you can see, ive tried passing the variables through the URL using Manufacturer=$Manufactuer&colour...
Can someone check to see if i have made any mistakes.
The prolbem only occours if I select something in the drop downs, then the second page of results doesnt work. It works perfcetley fine when all the records are returned.
Thanks in advance
Code: Select all
MYSQL_CONNECT($hostname, $username, $password) OR DIE("DB connection unavailable");
@mysql_select_db( "$dbName") or die( "Unable to select database");
function secured($val)
{
$val = strip_tags(trim(($val))) ;
$val = escapeshellcmd($val);
return stripslashes($val);
}
if (get_magic_quotes_gpc()) {
$Manufacturer = $_GET["Manufacturer"];
$Color = $_GET["Color"];
} else {
$Manufacturer = addslashes($_GET["Manufacturer"]);
$Color = addslashes($_GET["Color"]);
}
if(isset($_GET['brick'])) {
$where = "";
$sep = " WHERE ";
}
if($Manufacturer != "SelectManufacturer") {
$where .= $sep." Manufacturer = '".$Manufacturer."'";
$sep = " AND ";
}
if($Color != "SelectColor") {
$where .= $sep." colour = '".$Color."'";
$sep = " AND ";
}
if($_GET['page']) // Is page defined?
{
$page = $_GET['page']; // Set to the page defined
}else{
$page = 1; // Set to default page 1
}
$limit = 25;
if(empty($page)){
$page = 1;
}
$limitvalue = $page * $limit - ($limit);
$query = "SELECT * FROM bricks $where LIMIT $limitvalue, $limit";
$result = mysql_query($query) or die("Failed2: ".mysql_error());
$query1 = "SELECT * FROM bricks $where";
$result1 = mysql_query($query1) or die("Failed1: ".mysql_error());
$totalrows = mysql_num_rows($result1);
if(!$totalrows)
{
echo "<table align='center'><tr><td align='center'>Sorry, no bricks matching your selection were found. </td></tr></table>"; //message for when no bricks are returned
}
else
{
if ($bgcolor == "#E0E0E0"){
$bgcolor = "#FFFFFF";
}else{
$bgcolor = "#E0E0E0";
}
echo "<table align='center' border=0 ><tr>";
echo "<td align='left' width=250 >There are <b>$totalrows</b> matches to your query</td>"; //message saying how many matches for query
echo "<table align='center' border=1 bgcolor=E0E0E0><tr>";
echo "<td align='left' width=125 bgcolor='#11d163' color='#FF0000'><b>Supplier</b></td>";
echo "<td align='left' width=125 bgcolor='#11d163'><b>Color</b></td>";
echo "<td align='left' width=125 bgcolor='#11d163'><b>Details</b></td>";
echo "<td align='center' bgcolor='#11d163'><b>Picture</b></td>";
echo "</tr>";
while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo "<tr><td align='left'>".$row["Manufacturer"]."</td>";
echo "<td align='left'>".$row["Colour"]."</td>";
echo "<td align='left'>".$row["Brick Name"]."</td>";
echo "<td align='left'>".$row["Image"]."</td>";
echo "</tr>";
}
}
echo "</table>";
if($page != 1){
$pageprev = ($page - 1);
echo("<a href=\"$_SERVER[PHP_SELF]?Manufacturer=$Manufacturer&Colour=$Color&page=$pageprev\">PREV".$limit."</a> ");
}else{
echo("PREV".$limit." ");
}
$numofpages = ceil($totalrows / $limit);
for($i = 1; $i <= $numofpages; $i++){
if($i == $page){
echo($i." ");
}else{
echo("<a href=\"$_SERVER[PHP_SELF]?Manufacturer=$Manufacturer&Color=$Color&page=$i\">$i</a> ");
}
}
if(($totalrows - ($limit * $page)) > 0){
$pagenext = ($page + 1);
echo("<a href=\"$_SERVER[PHP_SELF]?Manufacturer=$Manufacturer&Color=$Color&page=$pagenext\">NEXT".$limit."</a>");
}
mysql_free_result($result);
?>