Help implementing Paging with a Dynamic Sql Query

[viperdrake]

Hello Board im new to the forum and to php, been using php for ... a week now lol. till now all ive used is .NET and decided to learn php as a quick and cheap way to make my webpage.

 

now the probem, i have a main search in which you choose certain options then click on the search button and it will bring build the query and show the results in a table, it works excelent, BUT i need to paginate this results, i have paginated (is that even a word? ) other query before and had no problem but the problem with this one is that since the query is dynamically build by the user, when the user clicks the links for the next page (using the $_GET['page']) it shows a blank page and its ok to show this since it never made a postback it just refreshed into the page. anywho

i thougth of actually somehow store in the session the original query and keep using this until the user changed the  search parameters but i dont know im really lost lol.

 

here is the code for my search page

			<?php 			
				$maxRows = 4;
				$pageNum = 1;
				if(isset($_GET['page']))
				{
					$pageNum = $_GET['page'];
				}
				$offset = ($pageNum - 1) * $maxRows;
				connect();
				if(isset($_SESSION['query']))
					$query = $_SESSION['query'];
				else
					$query = "SELECT COUNT(postId) AS numrows FROM post WHERE expired = 'N'";//some how it has to be the same query as the search or it will give wrong paginations(sp)
				echo $query;
				$result  = mysql_query($query) or die('Error, query failed');
				$row     = mysql_fetch_array($result, MYSQL_ASSOC);
				$numrows = $row['numrows'];
				$maxPage = ceil($numrows/$maxRows);
				//GridIndexDrawing
				$self = $_SERVER['PHP_SELF'];
				$nav  = '';
				for($page = 1; $page <= $maxPage; $page++)
				{
				   if ($page == $pageNum)
				   {
					  $nav .= "<span>$page</span>"; // no need to create a link to current page
				   }
				   else
				   {
					  $nav .= "<a style='background:orange;color:white;width:10px;text-decoration:none;cursor:pointer;cursor:hand;padding:3px;' href='$self?page=$page'>$page</a>";
				   }
				}
				$pageIndex = $nav;					
				//#################
			if($_SERVER['REQUEST_METHOD'] == 'POST')
			{
				unset($_SESSION['query']);
				MySQL_connect("localhost","root","usbw");
				MySQL_select_db("pettradepr");
				$searchValue = $_POST['txtSearch'];
				$category = ($_POST['txtCategoria'] == -1) ? ('category') : $_POST['txtCategoria'];
				$location = ($_POST['txtLocalizacion'] == -1) ? ('location') : $_POST['txtLocalizacion'];
				$seccion = ($_POST['txtSeccion'] == -1) ? ('section') : $_POST['txtSeccion'];							
				$Precio = $_POST['txtPrecio'];
				$orderBy = $_POST['OrderBy'];
				$pic = (isset($_POST['pic']))?($_POST['pic'] == 'Foto')?('<> ""')'= picPath'):'= picPath';
				$sqlSearch = "SELECT * ,section.name as secName, categoria.name as catName
				FROM pettradepr.post , pettradepr.section, pettradepr.categoria
				WHERE (body LIKE '$_POST[txtSearch]%' OR title LIKE  '$searchValue')
				AND section = section.sectionID
				AND category = categoria.catID
				AND category = ".$category."
				AND location = ".$location."
				AND section = ".$seccion."
				AND price ".$Precio."
				AND picPath ".$pic."
				AND expired = 'N'
				ORDER BY ".$orderBy.",creationDate DESC
				LIMIT {$offset},{$maxRows};";

				stripslashes($sqlSearch);
				mysql_real_escape_string($sqlSearch);
				$res = MySQL_query($sqlSearch);
				$cantRows = mysql_num_rows($res);
				if($cantRows > 0)
				{ 
					echo "\n<div class='MResults'>\n";
					echo "<fieldset class='resultados'><legend>Resultados</Legend>\n";
					echo $pageIndex;
					echo "<table name='results' class='searchresults' cellpadding=0 cellspacing=0>\n";
					echo "	<tr class='hRow'>\n";
					echo "		<td style='width:32px; padding:10px;'>Miembro</td>\n";
					echo "		<td style='width:50px; padding:10px;'>Sección</td>\n";
					echo "		<td style='width:100%; padding:10px;'>Titulo</td>\n";
					echo "		<td style='width:20px; padding:10px;'>Precio</td>\n";
					echo "		<td style='width:40px; padding:10px;'>Fecha</td>\n";
					echo "		<td style='width:32px; padding:10px;'>Foto</td></tr>\n";
					$ctr = 0;
					while($row = MySQL_fetch_array($res))
					{
						$ctr+=1;
						echo ($ctr%2 == 0) ? "<tr onClick='clickEvent(this);' id='test' class='nRow'>":"<tr onClick='clickEvent(this);' id='test' class='oRow'>";
						echo "	<td><input type='hidden' value='{$row['postId']}'/><label class='user'>{$row['userID']}</label></td>\n";
						echo "	<td><label class='section'>{$row['secName']}</label><br><label class='category'>{$row['catName']}</label></td>\n";
						echo "	<td><label class='titulo'>{$row['title']}</label></td>\n";
						echo "	<td><label class='precio'>\$".number_format($row['price'],2)."</label><br><label class='precioComment'>{$row['priceComments']}</label></td>\n";
						echo "	<td><label class='timestamp'>".date("m/d/y g:i (A)", strtotime($row['creationDate']))."</label></td>";
						echo ($row['picPath'] != "") ? ("	<td><img src='images/camera.png'/></td>\n</tr>\n") : "	<td></td>\n</tr>\n";
					}
					echo "</table>\n";
					echo "</fieldset>\n";
					echo "</div>\n";
				}
				else
				{
					echo '<div style="margin:0; heigth:50px; font-size:large;"> - No se econtraron anuncios con las criterios seleccionados - </div>';
				}
			}
			?>

 

i would gladly appreciate any help guys!!!

 

and thx in advance 

 

btw any good books for php???

[PravinS]

Use this

<?php
function select_row($sql)
{
//echo $sql . "<br />";
if ($sql!="")
{
	$result = mysql_query($sql) or die("Error: ".mysql_errno().":- ".mysql_error());
	if ($result)
	{
		while($row = mysql_fetch_assoc($result))
			$data[] = $row;
	}
	return $data;
}
}

function pagingSlot($sql, $recperpage, $pagesetlimit, $page, $class, $getvars)
{
$rescnt=mysql_query($sql);
$totcnt=mysql_num_rows($rescnt);
   
	if (!$page)   
		$page = 1;
	$first=(($page-1)* $recperpage);

	$sql = $sql . " limit ".$first.",".$recperpage;
	$res = select_row($sql);
   
	$serial_no = ($page - 1) * $recperpage;
   
	$t = ($totcnt/$recperpage);
	$arr=split('[.]',$t);
	if ($arr[1])
		$totalpages=$arr[0]+1;
	else
		$totalpages=$arr[0];
   
	if ($totalpages > $pagesetlimit)
	{
		if ($page > 1)
			$pagesetstart = $page - 1;
		else
			$pagesetstart = $page;               
		   
		$pagesetend= ($pagesetstart-1) + $pagesetlimit;
		if ($pagesetend > $totalpages)
		{
			$pagesetend = $totalpages;
			$pagesetstart = $pagesetend - $pagesetlimit + 1;
		}
	}
	else
	{
		$pagesetstart = 1;
		$pagesetend = $totalpages;
	}
   
	$str = "";

	if ($page > 1)
	{
		$prev = $page - 1;
		$str.= "<a href='".$_SERVER['PHP_SELF']."?page=$prev".$getvars."'  class='".$class."'> << </a> | ";                                           
	}
	else
	{
		$str.= "<<  | ";   
	}


	for ($i=$pagesetstart; $i<=$pagesetend; $i++)
	{       
   
		if ($i <= $totalpages)
		{
			if (!$page)
				$page=1;
			if ($page==$i)
				$str.= '<font color=red>'.$i.'</font> ';
			else
				$str.= "<a href='".$_SERVER['PHP_SELF']."?page=$i".$getvars."'  class='".$class."'>".$i."</a> ";
		}                               
	}

	if ($page < $totalpages)
	{
		$next = $page + 1;
		$str.= " | <a href='".$_SERVER['PHP_SELF']."?page=$next".$getvars."'  class='".$class."'> >> </a> ";                                           
	}
	else
	{
		$str.= " |  >>  ";
	}

if ($totcnt == 0)
	$str = "";
   
$arr["records"]=$res;
$arr["link"]=$str;
$arr["serial"]=$serial_no;
return $arr;
}        
?>

 

 

[viperdrake]

WoW fast reply lol!!

 

thx pbs i will try it and post back!!

[viperdrake]

ok so i havent implemented the code you gave me PBS, cause im currently at work, and my code is at home, but reading it what i get is that basically you will pass the parameters of the query in the address bar ($_GET[]) and then requery it again? rigth?