Show a certain amount of records.

[wwfc_barmy_army]

Hello.

I currently use this code to list a number of sites:

[code=php:0]<?php
print "<table border=1 class=list>";
echo("<thead><tr><td><a href=index.php?sortid=0>Site Name</a></td><td><a href=index.php?sortid=1>Editor Rating</a></td><td><a href=index.php?sortid=2>Visitor Rating</a></td><td><a href=index.php?sortid=3>Date Added</a></td><td><a href=index.php?sortid=4>Publisher</a></td></tr></thead><tbody>");

switch ($_GET['sortid']) {
  case 0 : $column = 'name'; break;
  case 1 : $column = 'editorrating'; break;
  case 2 : $column = 'rating'; break;
  case 3 : $column = 'dateadded'; break;
  case 3 : $column = 'publisher'; break;
  default : $column = 'name';
}
$result = mysql_query("SELECT * FROM site ORDER BY $column ASC");
while ($qry = mysql_fetch_array($result))
{
print "<tr>";
print "<td><a href=site.php?id=$qry[id]>$qry[name]</a>" ;
date_default_timezone_set("US/Eastern");
list($year, $mon, $day) = explode('-', $qry[4]);
$sevenDays = (7 * 24 * 60 * 60); // 604,800 seconds in 7 days
$entryTime = strtotime("$year-$mon-$day");
$now = strtotime("now");
if (($now - $entryTime) < $sevenDays)
{
echo "<img src='images/new.gif'>";
}
"</td>";

//Assuming each editorrating correpsonding an image name w/ that rating
If ($qry['editorrating'] == "") {$image = '0.png';
} else{
$image = $qry['editorrating'] . '.png';
}

//Display user's rating image
echo "<td><img src=\"images/{$image}\" /></td>";


//Get User's ID rating and display rating image based on that rating
$id = $qry['id'];
$qry_rating = mysql_query("SELECT rating FROM site where id='$id'");           
list($rating) = mysql_fetch_row($qry_rating);
$new_rating2 = (string)$rating;








//gets the average rating from the database and put into an image variable
$rateA = explode(".", $new_rating2);
$rateA[1] = ((int)$rateA[1] <= 49) ? NULL : $rateA[1];
$rateA[1] = ((int)$rateA[1] >= 50 && (int)$rateA[1] <= 99) ? "5" : $rateA[1];

//store rating in image2 variable
$image2 = $rateA[0] . $rateA[1] . '.png';
   
//Display user's rating image2
echo "<td><img src=\"images/{$image2}\" /></td>";

//Display dateadded
print "<td>$qry[dateadded]</td>";
//Display publisher
print "<td>$qry[publisher]</td>";
print "</tr>";
}

print "</tbody></table>";
?>[/code]

This will just list many many records, although i am looking to make it so it shows 20 records and then you have the pages option at the bottom, i'm sure you all know what i mean. Can anyone offer any advice/tutorials/code?

Thanks.

Peter.

[chriscloyd]

[code]<?php
print "<table border=1 class=list>";
echo("<thead><tr><td><a href=index.php?sortid=0>Site Name</a></td><td><a href=index.php?sortid=1>Editor Rating</a></td><td><a href=index.php?sortid=2>Visitor Rating</a></td><td><a href=index.php?sortid=3>Date Added</a></td><td><a href=index.php?sortid=4>Publisher</a></td></tr></thead><tbody>");

switch ($_GET['sortid']) {
  case 0 : $column = 'name'; break;
  case 1 : $column = 'editorrating'; break;
  case 2 : $column = 'rating'; break;
  case 3 : $column = 'dateadded'; break;
  case 3 : $column = 'publisher'; break;
  default : $column = 'name';
}
$result = mysql_query("SELECT * FROM site ORDER BY $column ASC LIMIT (20, 0");
while ($qry = mysql_fetch_array($result))
{
print "<tr>";
print "<td><a href=site.php?id=$qry[id]>$qry[name]</a>" ;
date_default_timezone_set("US/Eastern");
list($year, $mon, $day) = explode('-', $qry[4]);
$sevenDays = (7 * 24 * 60 * 60); // 604,800 seconds in 7 days
$entryTime = strtotime("$year-$mon-$day");
$now = strtotime("now");
if (($now - $entryTime) < $sevenDays)
{
echo "<img src='images/new.gif'>";
}
"</td>";

//Assuming each editorrating correpsonding an image name w/ that rating
If ($qry['editorrating'] == "") {$image = '0.png';
} else{
$image = $qry['editorrating'] . '.png';
}

//Display user's rating image
echo "<td><img src=\"images/{$image}\" /></td>";


//Get User's ID rating and display rating image based on that rating
$id = $qry['id'];
$qry_rating = mysql_query("SELECT rating FROM site where id='$id'");           
list($rating) = mysql_fetch_row($qry_rating);
$new_rating2 = (string)$rating;








//gets the average rating from the database and put into an image variable
$rateA = explode(".", $new_rating2);
$rateA[1] = ((int)$rateA[1] <= 49) ? NULL : $rateA[1];
$rateA[1] = ((int)$rateA[1] >= 50 && (int)$rateA[1] <= 99) ? "5" : $rateA[1];

//store rating in image2 variable
$image2 = $rateA[0] . $rateA[1] . '.png';
   
//Display user's rating image2
echo "<td><img src=\"images/{$image2}\" /></td>";

//Display dateadded
print "<td>$qry[dateadded]</td>";
//Display publisher
print "<td>$qry[publisher]</td>";
print "</tr>";
}

print "</tbody></table>";
?>[/code]

i changed ur query to limit between 20 and 0
so if u have 7 it will show all but if u have more than 20 it will only show the top 20

[wwfc_barmy_army]

Adding that gives me this error:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\public_html\RPG\list.php on line 23

But how do i make it so that if there are more than 20 records you have the choice of clicking 'next' which brings up the next 20?

Thanks.

Peter.

[markbett]

it in the LIMIT command and when you choose which record to start with...

Definition: Limit is used to limit your MySQL query results to those that fall within a specified range. You can use it to show the first X number of results, or to show a range from X - Y results. It is phrased as Limit X, Y and included at the end of your query. X is the starting point (remember the first record is 0) and Y is the duration (how many records to display).
Also Known As: Range Results
Examples:
SELECT * FROM `your_table` LIMIT 0, 10
This will display the first 10 results from the database.
SELECT * FROM `your_table` LIMIT 5, 5
This will show records 6, 7, 8, 9, and 10

[HuggieBear]

OK, the reason you're getting the error is that this line is incorrect:

[code=php:0]$result = mysql_query("SELECT * FROM site ORDER BY $column ASC LIMIT (20, 0");
[/code]

Try chaning it to this:

[code=php:0]$result = mysql_query("SELECT * FROM site ORDER BY $column LIMIT 20");
[/code]

As for what you're trying to do, it's called pagination.  [url=http://www.phpfreaks.com/tutorials/73/0.php]Try this tutorial[/url].

Regards
Huggie