How do you limit what is shown in pagination

[blink359]

Hi there i wanted to know how i could rule out certain rows from being shown in the pagination in this case i want to approve all information before it's posted i tried

$sql = "SELECT * FROM tablename LIMIT $offset, $rowsperpage WHERE approved = 'Yes'";

But it didnt work

 

Any help would be great,

 

Thanks,

 

Blink359

[johnny86]

I think you should move the LIMIT statement at the end:

$sql = "SELECT * FROM tablename WHERE approved = 'Yes' LIMIT $offset, $rowsperpage";

[blink359]

hmm that works but if i limit it to one row of information per page (Which is what im doing whilst i test it out as i only have 3 records for testing) I have a blank third page anyway to stop this?

[johnny86]

Well here is example:

+------------------------------------+
| ID  | Name                         |
+-----+------------------------------+
| 1   | Johnny                       |
| 2   | Somebody                     |
| 3   | Someone                      |
+------------------------------------+

From that table:
SELECT * FROM table LIMIT 0,1  // Gives you the first row
SELECT * FROM table LIMIT 1,1  // Gives you the second row
SELECT * FROM table LIMIT 2,1  // Gives you the third row

So you should have your offset set as: ($page_number - 1) * $items_per_page
Then if I now wanted one per page for the first page my offset would be:
(1 - 1) * 1 = 0
For the second page:
(2 - 1) * 1 = 1

If I want two rows per page:
(1 - 1) * 2 = 0
(2 - 1) * 2 = 2
(3 - 1) * 2 = 4

So to make it clear do the math in your PHP script, but this is what your SQL statement theorically consists of:
SELECT * FROM table WHERE approved = 'Yes' LIMIT ((page_number - 1) * rows_per_page), rows_per_page;