Jump to content

Help implementing Paging with a Dynamic Sql Query


viperdrake

Recommended Posts

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

Link to comment
Share on other sites

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

 

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.