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:
[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';
?>
[/code]
The paging should have links like  this:
[code]

<<First <Previous Page 2 of 30 Next > Last>>
[/code]
[/code]

[JasonLewis]

here is how it is down. read through the code and learn from it.

[code=php:0]
<?php
// Make a MySQL Connection
// Connects to your Database
include 'library/config.php';
include 'library/opendb.php';

if(!isset($_GET['pg'])){
$pg = 1;
}else{
$pg = $_GET['pg'];
}

$from = ($pg * 10) - 10;
$total_results = mysql_num_rows(mysql_query("SELECT * FROM pharmacy"));
$total_pages = ceil($total_results / 10);

$query = "SELECT * FROM pharmacy LIMIT {$from}, 10";

$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>";

//START WRITING PAGE NUMBERS
//-------------------------------

if($total_pages > 1 && $pg != 1){
echo "<a href='page.php?pg=1'>&lt;&lt;First</a> ";
echo "<a href='page.php?pg=".($pg - 1)."'>&lt;Previous</a> ";
}else{
echo "&lt;&lt;First ";
echo "&lt;Previous ";
}

echo "Page {$pg} of {$total_pages} ";

if($total_pages >1 && $pg != $total_pages){
echo "<a href='page.php?pg='".($pg + 1)."'>Next &gt;</a> ";
echo "<a href='page.php?pg={$total_pages}'>Last &gt;&gt;</a> ";
}else{
echo "Next &gt; ";
echo "Last &gt;&gt; ";
}

include 'library/closedb.php';
?>
[/code]

I think that should do ya, any errors just ask me cuz i quickly wrote that.
Oh! And remember to change the number 10 to anything you want. 10 is how much items to list on each page. Also remember to change the links for the pages, from page.php?pg= to whatever you want.[/code]

[projectshifter]

[quote author=ProjectFear link=topic=116909.msg476831#msg476831 date=1164957061]
here is how it is down. read through the code and learn from it.

[code=php:0]
<?php
// Make a MySQL Connection
// Connects to your Database
include 'library/config.php';
include 'library/opendb.php';

if(!isset($_GET['pg'])){
$pg = 1;
}else{
$pg = $_GET['pg'];

}

$from = ($pg * 10) - 10;
$total_results = mysql_num_rows(mysql_query("SELECT * FROM pharmacy"));
$total_pages = ceil($total_results / 10);

$query = "SELECT * FROM pharmacy LIMIT {$from}, 10";

$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>";

//START WRITING PAGE NUMBERS
//-------------------------------

if($total_pages > 1 && $pg != 1){
echo "<a href='page.php?pg=1'>&lt;&lt;First</a> ";
echo "<a href='page.php?pg=".($pg - 1)."'>&lt;Previous</a> ";
}else{
echo "&lt;&lt;First ";
echo "&lt;Previous ";
}

echo "Page {$pg} of {$total_pages} ";

if($total_pages >1 && $pg != $total_pages){
echo "<a href='page.php?pg='".($pg + 1)."'>Next &gt;</a> ";
echo "<a href='page.php?pg={$total_pages}'>Last &gt;&gt;</a> ";
}else{
echo "Next &gt; ";
echo "Last &gt;&gt; ";
}

include 'library/closedb.php';
?>
[/code]

I think that should do ya, any errors just ask me cuz i quickly wrote that.
Oh! And remember to change the number 10 to anything you want. 10 is how much items to list on each page. Also remember to change the links for the pages, from page.php?pg= to whatever you want.[/code]
[/quote]
No no no, you guys are killing me :P
First you do NOT want to use mysql_num_rows(mysql_query("SELECT * FROM blah")) that is SO bad, you're going to kill your server lol.  $total_results = mysql_result(mysql_query("SELECT COUNT(*) as count FROM pharmacy"), 0, 'count'); if you feel the need to do it that way.  It's best to get MySQL to parse and handle as much data as it can rather than leave that job to PHP.  If you're dealing with any kind of traffic, returning EVERY result (x2 because you're using *) in the pharmacy database is going to suck, where as returning a single number takes almost no work at all.