Jump to content


Photo

Show a certain amount of records.


  • Please log in to reply
4 replies to this topic

#1 wwfc_barmy_army

wwfc_barmy_army
  • Members
  • PipPipPip
  • Advanced Member
  • 320 posts

Posted 05 October 2006 - 06:46 PM

Hello.

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

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

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.

#2 chriscloyd

chriscloyd
  • Members
  • PipPipPip
  • Advanced Member
  • 489 posts
  • LocationArizona

Posted 05 October 2006 - 06:49 PM

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

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
44 bugs in my java code
44 bugs in my java code
Fix 1 bug, and complie again
122 bugs in my java code

#3 wwfc_barmy_army

wwfc_barmy_army
  • Members
  • PipPipPip
  • Advanced Member
  • 320 posts

Posted 05 October 2006 - 06:53 PM

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.

#4 markbett

markbett
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts

Posted 05 October 2006 - 06:58 PM

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

#5 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 05 October 2006 - 10:33 PM

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

$result = mysql_query("SELECT * FROM site ORDER BY $column ASC LIMIT (20, 0");

Try chaning it to this:

$result = mysql_query("SELECT * FROM site ORDER BY $column LIMIT 20");

As for what you're trying to do, it's called pagination.  Try this tutorial.

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users