Pagination in php

Dear All

Please see below script & help me on, how to add pagination in this script.

Kindly help.


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
        "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">


<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>View Orders Details</title>
 </head>
<body>
<?php
//$host="localhost"; // Host name
$username="root"; // Mysql username
$password=""; // Mysql password
//$database="crosswea_tech6"; // Database name
$database="merchandising";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM newcomp ORDER BY ship_date ASC";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();
?>

<table border="1" cellspacing="0" cellpadding="0" id="yourtablename">
<tr align="center" bgcolor="#817679">
<th>Buying House</font></th>
<th>Buyer</th>
<th>Department</th>
<th>Department For</th>
<th>Brand</th>
<th>Yaer</th>
<th>Season</th>
<th>Order No</th>

</tr>
   
<?php 
$color1 = "	#348781";

$color2 = "	#ffffff";

$i=0;
while ($i < $num) {

$row_color = ($i % 2) ? $color1 : $color2;

$f6=mysql_result($result,$i,"buyinghouse");
$f1=mysql_result($result,$i,"buyer");
$f7=mysql_result($result,$i,"departmentname_no");
$f12=mysql_result($result,$i,"department_for");

$f8=mysql_result($result,$i,"year");
$f9=mysql_result($result,$i,"season");
?>
<?php echo '<tr bgcolor="'. $row_color .'" align="center">';?>
<?php echo '<td bgcolor="'. $row_color .'" align="center">';?> <font face="Arial, Helvetica, sans-serif"><?php echo $f6; ?></font></td>

<td align="center"><font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font></td>
<td align="center"><font face="Arial, Helvetica, sans-serif"><?php echo $f7; ?></font></td>
<td align="center"><font face="Arial, Helvetica, sans-serif"><?php echo $f12; ?></font></td>

<td align="center"><font face="Arial, Helvetica, sans-serif"><?php echo $f8; ?></font></td>
<td align="center"><font face="Arial, Helvetica, sans-serif"><?php echo $f9; ?></font></td>
</tr>

<?php
$i++;
}
?>

</TABLE> 

</body>
</html>

You can use the following class to add pagination
Pagination (pagination class) - PHP Classes

You must learn about the mySQL LIMIT clause and its usage.

The script worked fine except you cannot write search query with a where clause or with an ordered by. I have tried modifying the query string in a few obvious places in the usage.php and ps_pagination.php but none have worked so far just getting errors.

Can any one show some example?

use ready pagination class :slight_smile:
your code is very fix.

It’s pretty simple – you just use LIMIT, which accepts two parameters.

Normally if you only feed it one number, it’s the maximum number of results you want. If you feed it two numbers, the first one is where in the result list to start, the second being how many you want to see… so if for example you wanted to get 20 of them starting at the 40th result:

$query=“SELECT * FROM newcomp ORDER BY ship_date ASC LIMIT 40,20”;

Of course, it would be a lot easier to deal with your code if it were a wee bit more… modern on the markup.

Something like this: (untested)


<?php

echo '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html
	xmlns="http://www.w3.org/1999/xhtml"
	lang="en"
	xml:lang="en"
><head>

<meta
	http-equiv="Content-Type"
	content="text/html; charset=utf-8"
/>

<meta
	http-equiv="Content-Language"
	content="en"
/>

<link
	type="text/css"
	rel="stylesheet"
	href="screen.css"
	media="screen,projection,tv"
/>

<title>
	View Orders Details
</title> 

</head><body>';

//$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password=""; // Mysql password 
//$database="crosswea_tech6"; // Database name 
$database="merchandising"; 

$start=1; /* where to start in the results */
$count=20; /* how many results to show */

mysql_connect(localhost,$username,$password); 
@mysql_select_db($database) or die( "Unable to select database"); 
$query="SELECT * FROM newcomp ORDER BY ship_date ASC LIMIT $start,$count"; 
$result=mysql_query($query); 

mysql_close(); 

echo '

<table id="yourtablename">
	<thead>
		<tr> 
			<th>Buying House</font></th> 
			<th>Buyer</th> 
			<th>Department</th> 
			<th>Department For</th> 
			<th>Brand</th> 
			<th>Yaer</th> 
			<th>Season</th> 
			<th>Order No</th> 
		</tr>
	</thead><tbody>';

$odd=true;
while ($row=mysql_fetch_row($result)) {
	echo '
		<tr',($odd ? '' : ' class="even"'),'>
			<td>',$row['buyinghouse'],'</td>
			<td>',$row['buyer'],'</td>
			<td>',$row['departmentname_no'],'</td>
			<td>',$row['department_for'],'</td>
			<td>',$row['year'],'</td>
			<td>',$row['season'],'</td>
		</tr>';
	$odd=!$odd;
}

echo '
	</tbody>
</table>

</body></html>';

?>

EVERYTHING else you are doing there belongs in the CSS, NOT in the markup. That’s one of the joys of CSS – less markup getting in the way of clear code on the CGI side.

Likewise I’m assuming this is a new page, so the loose tranny raises my hackles. Transitional is for supporting old/outdated crap, not for building new sites.

In any case, now that you know how/where to start pulling results, you can make the pagination code fairly easily – though you’ll also want a query to count the total number of results first… that way you know how many pages there are.