6

I am new to php and sql, and i have one tiny question about how to realize sql query , that can:

  • Take for example 5 entries from DB, insert them on 1st page (1-5)
  • Than take next 5 entries from same DB and insert them on another page (5-10)
    and so on :)

Thank you )

Konerak
  • 37,118
  • 11
  • 93
  • 114
user313216
  • 107
  • 1
  • 6
  • possible duplicate of [MySQL pagination without double-querying?](http://stackoverflow.com/questions/818567/mysql-pagination-without-double-querying) – OMG Ponies Jun 22 '10 at 17:28

3 Answers3

5
SELECT col FROM table LIMIT  0,5; -- First page, rows 1-5
SELECT col FROM table LIMIT  5,5; -- Second page, rows 6-10
SELECT col FROM table LIMIT 10,5; -- Third page, rows 11-15

Read the LIMIT section on the MySQL SELECT helppage. If you want to display the total number of rows available, you can either do an extra count, or use the ROW_COUNT function.

Konerak
  • 37,118
  • 11
  • 93
  • 114
0

There are several ways to do this, I think the fastest performance is following one:

$ItemsPerQuery = 5;
$CurrentPage = 0; //here you can add the current site through $_GET array or other ways, but don't forget to use mysql_real_escape_string(), because of mysql injection 

$Query = mysql_query("SELECT * FROM table LIMIT ".($ItemsPerQuery*$CurrentPage).",".$ItemsPerQuery);

while($row = mysql_fetch_assoc($query))
{
    echo $row['column_name'];
}

Stuff you should use:

therufa
  • 2,001
  • 2
  • 25
  • 39
  • Yeah, well thank you ) I got something like this in my head, but did not have any idea how to realize it in proper way. – user313216 Jun 22 '10 at 17:33
  • This is not right, page 1 won't show the first 5 entries, the starting limit is 1*5, should be 0 and altogether it should be ($ItemsPerQuery*$CurrentPage - $ItemsPerQuery) – cypher Jun 22 '10 at 17:45
  • uhm, true :) the `$CurrentPage` should be 0, because 0*5=0. sorry – therufa Jun 22 '10 at 17:48
  • And please, if the variables come from $_GET or any other userinput, mind SQL injection. Oden, if you want to build SQL queries using string concatenation, can you use or link to real_escape_string? – Konerak Jun 22 '10 at 18:53
0

If your query does not return a prohibitive amount of results, consider using Jquery to page it with a Tablesorter function. It takes a properly formatted HTML table and pages, sorts, etc on the fly...no need for additional query variables if you don't wish. It's SO much easier than going the full-blown PHP paging route, and is much faster for the user.

My personal favorite example is here: http://datatables.net/

It may not fully suit your needs, but it is cool for many applications. If you do it, just don't forget to format your table with thead and tbody so that it can properly pick up the table elements.

bpeterson76
  • 12,721
  • 4
  • 45
  • 81