Jump to content

Archived

This topic is now archived and is closed to further replies.

wwfc_barmy_army

Show a certain amount of records.

Recommended Posts

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.

Share this post


Link to post
Share on other sites
[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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.