1

Basically I have a form that submits a info into a mysql database table then displays it in a table located @ http://burtonmonster.com/list.php but I'd like to only display the latest 20 entries into the table so that there won't have to load like 100 entries basically it'll just keep displaying entries forever on 1 page.. No clue how to add pagination into my code.

<?php
$con=mysqli_connect("localhost","burtonmo_skype","PasswordSNIPPED","burtonmo_skype");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$result = mysqli_query($con,"SELECT * FROM users");

echo "<table class='table table-striped table-bordered' border='1'>
<tr>
<th>Username</th>
<th>Site</th>
<th>Age</th>
<th>Gender</th>
</tr>";

while($row = mysqli_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['username'] . "</td>";
  echo "<td>" . $row['bio'] . "</td>";
  echo "<td>" . $row['age'] . "</td>";
  echo "<td>" . $row['gender'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysqli_close($con);
?>
Tyler D.
  • 43
  • 1
  • 7
  • I completely overlooked the question and answered partially :(. Passing in page number and number of records per page in query parameter might be the way to go. – vee Jan 28 '14 at 04:26
  • 1
    [How to Paginate data with php](http://net.tutsplus.com/tutorials/php/how-to-paginate-data-with-php/) should guide you to solution. – vee Jan 28 '14 at 04:29

4 Answers4

1

To create pages of (X) results in mysql you can just use the limit like vee describes but with two parameters as shown here MySQL skip first 10 results.

SELECT * FROM users order by creation_date desc limit [starting offset], [page size ;
Community
  • 1
  • 1
Carth
  • 2,303
  • 1
  • 17
  • 26
  • For now i'd just like to only display the 20 latest table entries.. I'll move onto pagination a bit later. I tried your snippet of code and it didn't quite work. – Tyler D. Jan 28 '14 at 04:35
1

You can use this query, it will solve your problem 

 SELECT * FROM users ORDER BY any_Column_name DESC LIMIT 20;

It will 100% work for you. If true then please mark this answer as an answer so that others can use it for reference in future.....

Partap
  • 181
  • 1
  • 9
1

you must have an auto increment field or a field having record creation time then only you can order the result.. let consider you have a auto increment field call as ID in user table then

 $result = mysqli_query($con,"SELECT * FROM users order by ID desc limit 10");

it will return you the latest 10 user entry in USER table.

Deepak Sharma
  • 4,124
  • 1
  • 14
  • 31
  • I'd like to display the latest 20 users in the mysql database. On auto_increment doesnt it go 1,2,3,4,5,6,7,8,9,10,11... and so on? Picture this.. When a new user gets put on the list it'll be on the top and as other users get added it'll be pushed to the bottom and eventually won't be displayed anymore. – Tyler D. Jan 28 '14 at 04:42
  • so he added desc to order the records in descending order – Horst Jan 28 '14 at 04:45
  • right .. @TylerD. thats why i used the "order by id **DESC**" desc will reverse the order mean from 11, 10, 9... so on and then it will return the top 20 records.. try it.. it will work for you.. – Deepak Sharma Jan 28 '14 at 04:46
  • thanks a bunch added an id with auto_increment and its working beautifully. – Tyler D. Jan 28 '14 at 05:02
  • its mah pleasure.. !! :) – Deepak Sharma Jan 28 '14 at 05:03
0

This will work fine for you-

 $result = mysqli_query($con,"SELECT * FROM users LIMIT ORDER BY id DESC start,offset");

example: 

$result = mysqli_query($con,"SELECT * FROM users ORDER BY id DESC LIMIT 0,20"); //start=0 offset=20

In every pagination you must provide two parameter such as starting number and offset limit. If you provide no ordering it will take default id ordering ASC. You prodive id DESC because of last entry will come first. I think this will be perfect.

Md Mehedi Hasan
  • 1,733
  • 1
  • 21
  • 34