view 20 db entries per page

jonathen

Hi,

I have a list of db entries which display in an html table, line after line (listed by db id)

However, the list is ever-growing, causing the page to take a while to load. Ideally I would like to display 20 db entries per page, and then have "next 20" & "previous 20" (if applicable) links which will display the next 20. Also, I need to display something like "viewing 1-20 of 100 records" which updates on next 20 (i.e: "viewing 21-30 of 100 records" and so on)

Any ideas how I go about this? The current code I have for the recursive tables is:

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM data ORDER BY id DESC";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

$i=0;
while ($i < $num) {

$id=mysql_result($result,$i,"id");
$policytype=mysql_result($result,$i,"type");
$policynum=mysql_result($result,$i,"num");
$issued=mysql_result($result,$i,"date");


echo "<td>$id</td>";
echo "<td>$num</td>";
echo "<td>$type</td>";
echo "<td>$date</td>";
echo "<td>$dbview</td>";
echo "<td>$dbedit</td>";
echo "<td>$dbnotes</td>";
echo "<td>$dbdelete</td>";
echo "</tr>";
++$i;
}

TIA

Jonathen

BuzzLY

There is an article about this on phpbuilder, along with an extensive discussion. Check it out here

jonathen

Thank you - I didn't realise there was an article about this on here already! I appreciate you pointing me to it 🙂

BuzzLY

It's no problem. I was not trying to tell you that you shouldn't have posted the question -- merely pointing you to an answer to get you started. If you still have issues with it, come back and we'll work on it! 😃

jonathen

I used that script, and combined it with my current output, but it's breaking, and I can understand why. The line it gets an error on (Warning: Supplied argument is not a valid MySQL result resource in /home/web/test/output.php on line 200) is the following:

// now you can display the results returned
while ($data=mysql_fetch_array($result)) {
    // include code to display results as you see fit
echo "<td>$id</td>";
echo "<td>$num</td>";
echo "<td>$type</td>";
echo "<td>$date</td>";
echo "<td>$dbview</td>";
echo "<td>$dbedit</td>";
echo "<td>$dbnotes</td>";
echo "<td>$dbdelete</td>";
echo "</tr>";
}

so it's falling over on the "while ($data=mysql_fetch_array($result)) {" statement - this happens if I enter the echo statements in, or if I leave it blank. Also, those <td>'s are not displaying at all. Only the html before the script (starting the table) works.

Any ideas what I am doing wrong?

BuzzLY

apparently there is something wrong with $result -- it's not recognizing it as a result set. Are you sure you are getting data back from the database?