how to paginate mysql table query with php?

[shams]

hi,
since days i am trying codes for pagination but failed to edit for my own query, this is a mysql query can any one please put in paging code:

<?php
// Make a MySQL Connection
// Connects to your Database
include 'library/config.php';
include 'library/opendb.php';
$query = "SELECT * FROM pharmacy";

$result = mysql_query($query) or die(mysql_error());
echo "<table border='1'>";
echo "<tr><th>Id</th><th>Code</th><th>Name</th></tr>";
while($row = mysql_fetch_array($result)){
echo "<tr><td>";
echo $row[id];
echo "</td><td>";
echo $row[Code];
echo "</td><td>";
echo $row[Name];
echo "</td></tr>";
}
echo "</table>";
include 'library/closedb.php';
?>

The paging should have this links:

<<First <Previous Page 2 of 30 Next > Last>>

or something like this.

[edited by: shams at 11:25 pm (utc) on Nov. 30, 2006]

[eelixduppy]

Hello,

Related Thread [webmasterworld.com]

A search [google.com] may do some good for you too :)

[shams]

thansk for reply, the problem is i can't fit my own query the code i posted above in the pagination codes, as i tried from your link and got just a blank page, kindly some one edit the pagination code for my above query and post here.
i tried this code and got the blank page:

<?php
if(!isset($_GET['page'])){
$page = 1;
} else {
$page = $_GET['page'];
}

$max_results = PUT A NUMBER HERE;

$from = (($page * $max_results) - $max_results);

$sql = mysql_query("SELECT * FROM pharmacy WHERE id = '$FIELDA' LIMIT $from, $max_results") or die (mysql_error());
$num_rows = mysql_num_rows($sql);

if($_GET[id] == "")
{
echo "No query specified please try again.";
}
elseif($num_rows == 0)
{
print "No results available please try again.";
}
else
{

while(list($FIELDA, Code,Name)=mysql_fetch_array($sql)){

//Your output data goes here....
echo "<table border='1'>";
echo "<tr><th>Id</th><th>Code</th><th>Name</th></tr>";
echo "<tr>$FIELDA<td></td><td>$Code</td><td>$Name</td></tr>";
}
echo "</table>";
}

$total_results = mysql_num_rows($sql);
$max_results = 50;
$prev = ($page - 1);
$next = ($page + 1);
$total_pages = ceil($total_results / $max_results);
echo "<div align=center>";

if($page > 1){
echo "&lt;&lt;<a href=RESULTS-PAGE.php?FIELDA=", urlencode ($FIELDA),"&page=$prev>&nbsp;Previous</a> - ";
}

for($i = 1; $i <= $total_pages; $i++){
if(($page) == $i){
echo "$i";
} else {
echo "<a href=RESULTS-PAGE.php?FIELDA=", urlencode ($FIELDA),"&page=$i> $i </a>";
}
}

if($page < $total_pages){

echo " - <a href=RESULTS-PAGE.php?FIELDA=", urlencode ($FIELDA),"&page=$next>Next</a>&nbsp;&gt;&gt;";
} elseif($page = $total_pages) {
}}

echo "</div>";
include 'library/closedb.php'
?>

[shams]

and also tried this code but got a blank page:

<?php
include 'library/config.php';
include 'library/opendb.php';
/* Set current, prev and next page */
$page = (!isset($_GET['page']))? 1 : $_GET['page'];
$prev = ($page - 1);
$next = ($page + 1);

/* Max results per page */
$max_results = 2;

/* Calculate the offset */
$from = (($page * $max_results) - $max_results);

/* Query the db for total results. You need to edit the sql to fit your needs */
$result = mysql_query("select id from pharmacy");

$total_results = mysql_num_rows($result);

$total_pages = ceil($total_results / $max_results);

$pagination = '';

/* Create a PREV link if there is one */
if($page > 1)
{
$pagination .= '<a href="index.php?page='.$prev.'">Previous</a> ';
}

/* Loop through the total pages */
for($i = 1; $i <= $total_pages; $i++)
{
if(($page) == $i)
{
$pagination .= $i;
}
else
{
$pagination .= '<a href="index.php?page='.$i.'">$i</a>';
}
}

/* Print NEXT link if there is one */
if($page < $total_pages)
{
$pagination .= '<a href="index.php?page='.$next.'">Next</a>";
}

/* Now we have our pagination links in a variable($pagination) ready to print to the page. I pu it in a variable because you may want to show them at the top and bottom of the page */

/* Below is how you query the db for ONLY the results for the current page */
$result=mysql_query("select * from pharmacy LIMIT $from, $max_results ");
echo "<table border='1'>";
echo "<tr><th>Id</th><th>Code</th><th>Name</th></tr>";
while ($i = mysql_fetch_array($result))
{
/* This is where you print your current results to the page */
echo "<tr><td>";
echo $i[id];
echo "</td><td>";
echo $i[Code];
echo "</td><td>";
echo $i[Name];
echo "</td></tr>";
}
echo "</table>";
include 'library/closedb.php';
?>