CODEWITHSUNDEEP
phpmysql
munin24
munin24

Reputation: 31

PHP best way to display x results per page

I have a database with many entries which contain datetime fields as well. Right now the index is an md5 hash value which gets calculated from other fields including the date, making each entry unique.

What would be the best way to display let's say 50 results per page from this database? Could you provide a php example?

I might not see this right but this would be a lot easier if I would use an integer ID field as a unique key (which gets auto incremented) because then I could query the last ID like 9412 and do a -50 to get the last 50 results for the first page then do a -50 to get the previous 50 results on the 2nd page when the user clicks on the arrow.

The only problem with this is that I do REPLACE INTO this db to avoid duplicate entries by the hash. If the ID is an auto incrementing integer this might wouldn't work.

Thanks.

Upvotes: 0

Views: 9150

Answers (4)

Alex Szabo
Alex Szabo

Reputation: 3276

As mentioned by others, I'd also suggest using LIMIT, OFFSET.

I've seen this method on a fair amount of sites:

User clicks for example page 5 and gets redirected to

-> view.php?p=5

and in the PHP file there's something like this:

$page = $_GET['p'];
$limit = 50;

$page = ($page - 1) * $limit;

$sql = "SELECT * FROM Posts LIMIT ".$limit." OFFSET ".$page." ORDER BY ID";
. . .

EDIT 2020: I just accidentally rediscovered my answer, please if you want to go this way, use prepared statements to protect yourself from SQL Injection attacks

Upvotes: 2

mingos
mingos

Reputation: 24502

You needn't worry about the IDs. Use LIMIT and OFFSET in your SQL.

SELECT * FROM tbl
ORDER BY f_date DESC
LIMIT 50 OFFSET 0

would be the first page of your results. You can increase the offset to fetch other pages, e.g. the second page would be OFFSET 50.

To get more information on the subject, please use Google and search for pagination.

Upvotes: 0

Ajant
Ajant

Reputation: 158

Integer auto-incremented ids combined with your calculations are not a good solution, as far as pagination/sorting is concerned, if you allow for deletion of rows. Both "hard" delete that removes the row from the table, and "soft" delete where you have isActive column, which prevents row from being shown, if value is set to inactive, will mess up your calculations. Rows with certain ids will be missing meaning your result set will sometimes have fewer then 50 rows, or will display inactive rows that shouldn't be viewable. You can use "LIMIT" in your MySQL query.

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15

http://dev.mysql.com/doc/refman/5.0/en/select.html

Combine that with pagination and you have a good solution.

As for sorting, that depends on when you update date rows. If date column is immutable, using LIMIT combined with sorting by date will ensure consistent representation, otherwise representation will change with time, as the date column changes, but it will still be valid (you'll get 50 rows and they will all be viewable active rows).

If your date column is changeable (for instance it reflects last login information), but you want to ensure that records are ordered in the same way (same 50 records are always shown on the 1st page etc.) and you can afford to add a column, you could add immutable integer auto-increment column for sorting purposes, but keep your primary key column as it is convenient to you

Upvotes: 0

bpgergo
bpgergo

Reputation: 16037

I'm not sure about your hash value but using GUID (Globally Unique Id) has several advantage over autoincrement, integer id values. GUID primary keys are a natural fit for many development scenarios, such as replication, or when you need to generate primary keys outside the database.

Regarding pagination, you'll need to find a field or a combination of fields that defines a natural order on your data. E.g. you can use the date field. Then you can use this order to implement pagination with the LIMIT and OFFSET keywords. This is how you get the first page:

select * from table
order by date_field desc -- show most recent records on top
limit 10 offset 0;

This is how you get second page:

select * from table
order by date_field desc -- show most recent records on top
limit 10 offset 10;

This is how you get Nth page:

select * from table
order by date_field desc
limit 10 offset 10*N;

Upvotes: 0

Related Questions