PHP pagination with HTML form submit contain drop-down and checkbox filter

jeffrey_fsp · Jan 31, 2012

Hi,

  I am a rookie on php and this is my first php site. I am facing a pagination issue with my site and I hope I can get help here. I've find a pagination code from

internet and it is work for me if my php page just show all record from mysql and don't let user to filter the data. But after I merged the pagination code with my php page (add HTML drop-down and checkbox), The pagination is only work on first page, and when I click on other pages, no data show up.

  I've tried to use $_GET install of $_POST and also define some variable using

$_SESSION variable but still got the same issue. Following is my code, maybe a bit long (2xx lines), anyone can advise me how can I fix my issue? Thx

<?php
	if(!isset($_SESSION)) {
	session_start();
	}
	  print '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">';
	  print '<html>';
	  print '<head>';
	  print '<meta http-equiv="Content-Type" content="text/html;charset=UTF-8" />';
	  print '<meta http-equiv="Content-Style-Type" content="text/css" />';
	  print '<title>result</title>';
	  print '</head>';
	  print '<body>';
	/*
		Place code to connect to your DB here.
	*/
	$link_ID = mysql_connect("127.0.0.1","???","???");
	mysql_set_charset('utf8',$link_ID);
	mysql_select_db("???");
	$str_get_district="SELECT * FROM district;";
	$result_get_district = mysql_query($str_get_district,$link_ID);
	$sn_district = mysql_num_rows($result_get_district);
?>

<h2>pls select your choice</h2>
<hr>

<?php
	if (isset($_POST["leisure"])) {
		foreach ($_POST["leisure"] as $selectedleisure)
			$selected[$selectedleisure] = "checked";
	}
	//	include('config.php');	// include your code to connect to DB.

$tbl_name="shop";		//your table name
// How many adjacent pages should be shown on each side?
$adjacents = 3;
?>

<form method=post name=main>
	pls select your district :
	<select name=sel_district size=1>
		<option value=0>All districts</option>

<?php
	for ($index=0; $index < $sn_district; $index++) {
		$arr[$index] = mysql_fetch_array($result_get_district);		
		echo "<option value={$arr[$index]['id']}";
		if (isset($_POST["sel_district"]) && $_POST["sel_district"] == $arr[$index]['id']) {
			echo " selected='selected'";
		}
		echo ">{$arr[$index]['name']}</option>";
	}
?>

</select>
<hr>
	select item(s) : <br>
<input type="checkbox" name="leisure[]" <? echo $selected['1'] ?> value=1 />All<br>
<input type="checkbox" name="leisure[]" <? echo $selected['2'] ?> value=2 />Item 1<br>
<input type="checkbox" name="leisure[]" <? echo $selected['3'] ?> value=3 />Item 2<br>
<input type="checkbox" name="leisure[]" <? echo $selected['4'] ?> value=4 />Item 3<br>
<input type="checkbox" name="leisure[]" <? echo $selected['5'] ?> value=5 />Item 4<br>
<br>
<input type="submit" value="Search" />
</form>
<hr>

<table border="1" width="1920">
	<col width="60" />
	<col width="200" />
	<col width="60" />
	<col width="200" />
	<col width="100" />
	<col width="300" />
	<col width="300" />
<tr>
<th>Type</th>
<th>Name</th>
<th>District</th>
<th>Address</th>
<th>Telephone</th>
<th>Info 1</th>
<th>Info 2</th>
</tr>

<?php
	/* 
	   First get total number of rows in data table. 
	   If you have a WHERE clause in your query, make sure you mirror it here.
	*/
	$query = "SELECT COUNT(*) as num FROM $tbl_name";
	$total_pages = mysql_fetch_array(mysql_query($query));
	$total_pages = $total_pages[num];

/* Setup vars for query. */
$targetpage = "main.php"; 	//your file name  (the name of this file)
$limit = 20; 								//how many items to show per page
$page = $_GET['page'];
if($page) 
	$start_from = ($page - 1) * $limit; 			//first item to display on this page
else
	$start_from = 0;								//if no page var is given, set start to 0

/* Get data. */
//	$sql = "SELECT column_name FROM $tbl_name LIMIT $start, $limit";
	$list_district = $_POST["sel_district"];

if (count($_POST["leisure"]) == 0) {
	echo "Pls select at least one item.";
}

for ($index=0; $index < count($_POST["leisure"]); $index++) {
	$arr_item_type[$index]=$_POST["leisure"][$index];

if ($list_district == 0 && $arr_item_type[0] == 1) {
$str_list_shop="SELECT shoptype.name as stype,shop.name as sname,district.name as dname,address,phone,info,info2 FROM shop
				LEFT JOIN shoptype ON shop.type=shoptype.id
				LEFT JOIN district ON shop.district=district.id
				ORDER BY dname,stype ASC LIMIT $start_from, $limit;";
}
else if ($list_district == 0 && $arr_item_type[0] > 1) {
$str_list_shop="SELECT shoptype.name as stype,shop.name as sname,district.name as dname,address,phone,info,info2 FROM shop
				LEFT JOIN shoptype ON shop.type=shoptype.id
				LEFT JOIN district ON shop.district=district.id
				WHERE type='$arr_item_type[$index]'
				ORDER BY dname,stype ASC LIMIT $start_from, $limit;";
	}
else if ($list_district != 0 && $arr_item_type[0] == 1) {
$str_list_shop="SELECT shoptype.name as stype,shop.name as sname,district.name as dname,address,phone,info,info2 FROM shop
				LEFT JOIN shoptype ON shop.type=shoptype.id
				LEFT JOIN district ON shop.district=district.id
				WHERE district='$list_district'
				ORDER BY dname,stype ASC LIMIT $start_from, $limit;";
}
else {
$str_list_shop="SELECT shoptype.name as stype,shop.name as sname,district.name as dname,address,phone,info,info2 FROM shop
				LEFT JOIN shoptype ON shop.type=shoptype.id
				LEFT JOIN district ON shop.district=district.id
				WHERE district='$list_district' AND type='$arr_item_type[$index]'
				ORDER BY dname,stype ASC LIMIT $start_from, $limit;";
}

/* Setup page vars for display. */
if ($page == 0) $page = 1;					//if no page var is given, default to 1.
$prev = $page - 1;							//previous page is page - 1
$next = $page + 1;							//next page is page + 1
$lastpage = ceil($total_pages/$limit);		//lastpage is = total pages / items per page, rounded up.
$lpm1 = $lastpage - 1;						//last page minus 1

/* 
	Now we apply our rules and draw the pagination object. 
	We're actually saving the code to a variable in case we want to draw it more than once.
*/
$pagination = "";
if($lastpage > 1)
{	
	$pagination .= "<div class=\"pagination\">";
	//previous button
	if ($page > 1) 
		$pagination.= "<a href=\"$targetpage?page=$prev\">? previous</a>";
	else
		$pagination.= "<span class=\"disabled\">? previous</span>";	

	//pages	
	if ($lastpage < 7 + ($adjacents * 2))	//not enough pages to bother breaking it up
	{	
		for ($counter = 1; $counter <= $lastpage; $counter++)
		{
			if ($counter == $page)
				$pagination.= "<span class=\"current\">$counter</span>";
			else
				$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
		}
	}
	elseif($lastpage > 5 + ($adjacents * 2))	//enough pages to hide some
	{
		//close to beginning; only hide later pages
		if($page < 1 + ($adjacents * 2))		
		{
			for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
			{
				if ($counter == $page)
					$pagination.= "<span class=\"current\">$counter</span>";
				else
					$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
			}
			$pagination.= "...";
			$pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
			$pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";		
		}
		//in middle; hide some front and some back
		elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
		{
			$pagination.= "<a href=\"$targetpage?page=1\">1</a>";
			$pagination.= "<a href=\"$targetpage?page=2\">2</a>";
			$pagination.= "...";
			for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
			{
				if ($counter == $page)
					$pagination.= "<span class=\"current\">$counter</span>";
				else
					$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
			}
			$pagination.= "...";
			$pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
			$pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";		
		}
		//close to end; only hide early pages
		else
		{
			$pagination.= "<a href=\"$targetpage?page=1\">1</a>";
			$pagination.= "<a href=\"$targetpage?page=2\">2</a>";
			$pagination.= "...";
			for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
			{
				if ($counter == $page)
					$pagination.= "<span class=\"current\">$counter</span>";
				else
					$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
			}
		}
	}

	//next button
	if ($page < $counter - 1) 
		$pagination.= "<a href=\"$targetpage?page=$next\">next ?</a>";
	else
		$pagination.= "<span class=\"disabled\">next ?</span>";
	$pagination.= "</div>\n";		
}

$result_get_shop = mysql_query($str_list_shop,$link_ID);
$sn_shop = mysql_num_rows($result_get_shop);

for ($count=0; $count < $sn_shop; $count++) {
	$arr_list_shop[$count] = mysql_fetch_array($result_get_shop);
	echo "<tr>";
	echo '<td>'.$arr_list_shop[$count]['stype'].'</td>';
	echo '<td>'.$arr_list_shop[$count]['sname'].'</td>';
	echo '<td>'.$arr_list_shop[$count]['dname'].'</td>';
	echo '<td>'.$arr_list_shop[$count]['address'].'</td>';
	echo '<td>'.$arr_list_shop[$count]['phone'].'</td>';
	echo '<td>'.$arr_list_shop[$count]['info'].'</td>';
	echo '<td>'.$arr_list_shop[$count]['info2'].'</td>'; 
	echo "</tr>";
}
}
echo "</table>";

?>

<?=$pagination?>

<?
//mysql_close($link_ID);
print '</body>';
print '</html>';
?>

johanafm · Jan 31, 2012

jeffrey.fsp;10995567 wrote:
Following is my code, maybe a bit long (2xx lines)

Most definitely - yes!

If you want specific help, you should
1. Indent your code to make it readable.
2. Wrap it in proper tags (see what the board has to offer). You may have noticed that other people have syntax highlighting in their code. And if you havn't noticed, then you SHOULD HAVE NOTICED. You did look around the board for other posts with similar issues didn't you?
3. Either create a minimal "non-working" example, that is, the least amount of code that actually works, but reproduces your problem, or you should show the minimal amount of code necessary where the code in itself show the issue, even if the code as such can't be executed. And should you do the latter and we find that something we need to know in order to help you is missing, then we'd tell you.

jeffrey.fsp;10995567 wrote:
anyone can advise me how can I fix my issue? Thx

The first thing I'd advice you to do is follow the same principles as above, for your own sake.

Remove database connection stuff and put it in its own file. You don't need to scroll past that every time it's needed, and you most definitely don't want to have it in multiple files if you ever change a password, DB server etc. This is a lot easier on the eyes and keeps the connection stuff in one single place
```
require_once 'dbconnect.php';
```
The same goes for printing html doctype, head element etc
```
# either
require_once 'html_head.html';
# or, should you need to use dynamic page titles, include different javascript files etc
require_once 'html_head.php';
```
And so on for anything which either IS needed for other pages, or which MIGHT be needed for other pages later on. That is, generic functionality which doens't really depend on the currently requested page.
If things work on the first page in the paginated set but in no other page, something should be different. Could it possibly be the database query itself? Have you tried to output the query or queries involved in retrieving the data? What did they look like for the first page and for any subsequent page?

jeffrey_fsp · Feb 2, 2012

Hi Johanafm, Thx for your advise and suggestion. I will try it

jeffrey_fsp · Feb 13, 2012

I have used a non-intelligent way to fix this. user php to get the website url after the query has been submitted. then user mb_substr function to retrieve part of the url as a string and paste it to the url of the pagination.

johanafm · Feb 13, 2012

There are two ways the user filtering selection can be passed to the server. Either as POST data or as GET data. If a user clicks a link containing a query string (usually following a ?: example.com/file.php?querystring) then the query string is what ends up in $GET. If a form is sent with method GET then the form data is also passed as querystring parameters and end up in $GET. When method is POST, then it ends up in $_POST.

Thus when you build your pagination links, you need to include the filter parameters in the links.

# All possible filter variables
$filter_vars = array('name', 'age', 'location');
$filter = array();
foreach ($filter_vars as $fv)
{
	# Check which filter vars are pesent in $_GET, i.e. check what filtering the user
	# selected. The reason you don't loop over the entire _GET array is that you
	# only want to keep relevant stuff
	if (isset($_GET[$fv]))
	{
		# Build an array looking like 
		# array( name=joe, location=earth )
		# Never forget to sanitize input before using it. In this case, it means
		# urlencoding user input. While neither "name", "age" or "location" needs to
		# be urlencoded, it might be an idea to do so allready since you might one
		# day add a filter var which needs to be urlencoded.
		$filter[] = urlencode($fv) . '=' . urlencode($_GET[$fv]);
	}
}

# Finally add both this and the page variable to the url you create for your pagination links.
$qs = implode('&amp;', $filter);

PHP pagination with HTML form submit contain drop-down and checkbox filter

Jjeffrey_fsp

Jjohanafm

Jjeffrey_fsp

Jjeffrey_fsp

Jjohanafm