help with a sorting table in php

[oiwio]

What I want to do is get all of the members from a table in my database and sort it by a certain column. All that works perfectly, but I only want to display 20 users per page and I cant figure out how to set it up where it automatically detects how many users there are, divide that by 20, and have that many page links at the bottom of the table and have each page display the next 20 users.

<?
// Get the name of the column to sort
$columnname = "alchemy";
if ($_GET['alchemy']) {
$columnname = $_GET['alchemy'];
}
// Get the value of either ascending or descending
$order = "";
if ($_GET['order']) {
$order = $_GET['order'];
}
// Display 20 records per page.
$display_number = 20;
// Make the calculation to see how many pages are there.
if (!isset($num_pages)) {
$query1 = "SELECT * FROM ".TBL_USERS."";
// Query the database.
$query_result1 = mysql_query ($query1) or die (mysql_error());
// Calculate the number of pages required.
$num_results = @mysql_num_rows ($query_result1);
if ($num_results > $display_number) {
$num_pages = $num_results/$display_number;
} elseif ($num_results > 0) {
$num_pages = 1;
}
// Currently at item 0.
$start = 0;
}
// Make the default column name
if ($columnname == "")
{$columnname = "username"; }
// Make the default order sorting
if ($order == "") {
$order = "DESC"; }
// Make the new, limited query.
$query = "SELECT * FROM ".TBL_USERS." ORDER BY $columnname $order LIMIT $start, $display_number";
// Print a table.
echo '<form action="main.php" method="post"> <br><table width="740" align="center" cellpadding="0" cellspacing="0" border="1">
<tr align="center">
<td width="300"><div align="center">
<font color="#ffffff" size="2" face="Verdana, Arial, Helvetica, sans-serif"><b>Username</b></font>
</div>
</td>
<td width="175"><div align="center">
<font color="#ffffff" size="2" face="Verdana, Arial, Helvetica, sans-serif"><b>Alchemy</b></font>
</div>
</td>
<td width="600"><div align="center">
<font color="#ffffff" size="2" face="Verdana, Arial, Helvetica, sans-serif"><b>Status</b></font>
</div>
</td>
</tr>';
// Print each item.
$query_result = @mysql_query ($query);
while ($row = @mysql_fetch_array ($query_result)) {
echo "<tr align=\"center\" bgcolor=\"$row[colourcode]\"> 
<td align=\"left\"><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\" >&nbsp$row[username]</font></td>
<td align=\"left\"><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\" >&nbsp$row[alchemy]</font></td>
<td align=\"left\"><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\" >&nbsp$row[statetitle]</font></td>
</tr>";
}
?>

 

[DS Gamers]

This section of code:

$num_results = @mysql_num_rows ($query_result1);
if ($num_results > $display_number) {
$num_pages = $num_results/$display_number;
} elseif ($num_results > 0) {
$num_pages = 1;
}

shows us how it figures out the number of pages,
All you need to do is change the HTML at the bottom of the code (where it says: "// Print a table.") to include an extra cell containing the page numbers.
I'm not sure how you would code that in HTML but as long as you use the '$num_pages' it should work.

 

[oiwio]

Yea, but the problem Im having is how to code that

 

[leafypiggy]

to print the page numbers you would use

echo() $num_pages

I dont know how you would link it. it would have to be dynamic. (page=1, page=2, ect.)

 

[Adam01]

Or,

while ($row = @mysql_fetch_array ($query_result)){
$num += 1;
if($num >= 10){
break;
echo "<a>Page 2</a>";
}
else{
echo "<td>$row['Member']</td>";
}

}

Im sure theres a code where it only lists 10 rows from the database.

 

[deadimp]

You ought to use a LIMIT statement in MySQL. That way you don't have to go through with all of that in PHP.
Ex:

$res=mysql_query('select count(`id`) from `table`); //Using count(`id`) might be faster since the server won't bother sending any extra data
$count=mysql_result($res,0,0);
$page=$_GET['pg']; //Be sure to sanitize!
$per_pg=20;
$start=/*Do calculations...*/;
$res=mysql_query('select * from `table` limit '.$start.', '.$per_pg);
//Process

I have a working pagination system in Thacmus, viewable in the source:
. /main.php - Browse class
. /db/dbi.php - DBI::makeBrowse()
It's probably not the best example, though, especially since I haven't released a newer version for a few months. I plan on it, though.
If you want something more tutorial-oriented, google "php mysql pagination" or something like that.

 

[oiwio]

Well, i started messing around with some different scripts and I found one that is working for me so far.

The new parts are surrounded by *'s

<?****new****if (!isset($_GET['start'])){
$start = 0;
}else{
$start = $_GET['start'];
}****end****
// Get the name of the column to sort
$columnname = "alchemy";
if ($_GET['alchemy']) {
$columnname = $_GET['alchemy'];
}
// Get the value of either ascending or descending
$order = "";
if ($_GET['order']) {
$order = $_GET['order'];
}
// Display 20 records per page.
$display_number = 20;
// Make the calculation to see how many pages are there.
if (!isset($num_pages)) {
$query1 = "SELECT * FROM ".TBL_USERS."";
// Query the database.
$query_result1 = mysql_query ($query1) or die (mysql_error());
// Calculate the number of pages required.
$num_results = @mysql_num_rows ($query_result1);
if ($num_results > $display_number) {
$num_pages = $num_results/$display_number;
} elseif ($num_results > 0) {
$num_pages = 1;
}
****Got rid of $start = 0****
}
// Make the default column name
if ($columnname == "")
{$columnname = "username"; }
// Make the default order sorting
if ($order == "") {
$order = "DESC"; }
// Make the new, limited query.
$query = "SELECT * FROM ".TBL_USERS." ORDER BY $columnname $order LIMIT $start, $display_number";
// Print a table.
echo '<form action="main.php" method="post"> <br><table width="740" align="center" cellpadding="0" cellspacing="0" border="1">
<tr align="center">
<td width="300"><div align="center">
<font color="#ffffff" size="2" face="Verdana, Arial, Helvetica, sans-serif"><b>Username</b></font>
</div>
</td>
<td width="175"><div align="center">
<font color="#ffffff" size="2" face="Verdana, Arial, Helvetica, sans-serif"><b>Alchemy</b></font>
</div>
</td>
<td width="600"><div align="center">
<font color="#ffffff" size="2" face="Verdana, Arial, Helvetica, sans-serif"><b>Status</b></font>
</div>
</td>
</tr>';
// Print each item.
$query_result = @mysql_query ($query);
while ($row = @mysql_fetch_array ($query_result)) {
echo "<tr align=\"center\" bgcolor=\"$row[colourcode]\"> 
<td align=\"left\"><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\" >&nbsp$row[username]</font></td>
<td align=\"left\"><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\" >&nbsp$row[alchemy]</font></td>
<td align=\"left\"><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\" >&nbsp$row[statetitle]</font></td>
</tr>";
}
?> </form></table>****new****<p>Page: <a href="ranks.php?start=0">1</a><? 
$pages = 1;
$amount = 0;
while($num_pages >= $pages){
$pages = $pages + 1;
$amount = $amount + 20;
echo " <a href=\"ranks.php?start=$amount\">$pages</a>";
}****end****