HTML table populated with PHP, how to limit rows per page?

Question

0.00/5 (No votes)

See more:

Hello,

I populated a html table this way:

sqlquery = ...
echo "<table width='100%'><tr>";
 if (mysql_num_rows($result)>0)
 {
         //loop thru the field names to print the correct headers
         $i = 0;
         while ($i < mysql_num_fields($result))
         {
               echo "<th>". mysql_field_name($result, $i) . "</th>";
                  $i++;
           }
           echo "</tr>";

...
 echo "<tr>";
                 foreach ($rows as $data)
                    {
                        echo "<td align='center'>". $data . "</td>";
...

The problem is: in the page I have that table I want to appear only ten rows (ten results), and then I have below a 1,2, .... next page and in that section where table is, appears next 10 results (rows).

How can I accomplish that ? I thing I have to use JS but I dont know how to do it, so I really appreciate your help.

Posted 7-Jan-11 1:06am

Paula Freire

Updated 7-Jan-11 1:18am

Sandeep Mewara

v3

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

E.F. Nijboer · Answer 1 · 2011-01-07T01:24:00

Solution 1

You can use SQL to do this:

SELECT * FROM `table` LIMIT 0, 10

will result in rows 1 to 10

SELECT * FROM `table` LIMIT 10, 20

will result in rows 11 to 20

Of course you need to keep track of the current position so you send it back when the user clicks on next or previous.

[Edit]

But you would not leave the page but reload is. User is at: www.site.com/data.php?start=0 and for example redirected to www.site.com/data.php?start=10 when clicking next. The user is still om the same page but loads with another set of data. Another way to do something like that is using AJAX. This means that you reload a some part of the page asynchronously without reloading the complete page.

Have a look here for this:
w3cschool: http://www.w3schools.com/ajax/default.asp[^]
ajax php: http://www.tizag.com/ajaxTutorial/ajaxphp.php[^]

[/Edit]

Good luck!

Posted 7-Jan-11 1:24am

E.F. Nijboer

Updated 7-Jan-11 20:53pm

v3

Comments

Paula Freire 7-Jan-11 7:32am

But my problem is not the SQL.. is the next and previous button.

because I want to select "next 10 results", mas not leaving the page.

Any ideas how to do that ?

E.F. Nijboer 7-Jan-11 9:21am

But you would not leave the page but reload is. User is at: www.site.com/data.php?start=0 and for example redirected to www.site.com/data.php?start=10 when clicking next. The user is still om the same page but loads with another set of data.
Another way to do something like that is using AJAX. This means that you reload a some part of the page asynchronously without reloading the complete page. Have a look here for this:
w3cschool: http://www.w3schools.com/ajax/default.asp
ajax php: http://www.tizag.com/ajaxTutorial/ajaxphp.php

thatraja 8-Jan-11 2:54am

@E.F. Nijboer : Hi update the things in your answer & notify enquirer(instead of pasting your valuable explanations in comments). I'll update now for demo :-). cheers.

Dalek Dave 9-Jan-11 14:26pm

Excellent answer.

adocas · Answer 2 · 2011-01-07T01:27:00

You can do it directly in the database:

SQL

SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr, ...
[INTO OUTFILE 'file_name' export_options
| INTO DUMPFILE 'file_name']
[FROM table_references
[WHERE where_definition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_definition]
[ORDER BY {col_name | expr | position}
[ASC | DESC] , ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}][PROCEDURE procedure_name(argument_list)]
[FOR UPDATE | LOCK IN SHARE MODE]]

You can page the results in function of the current page:

Example:

int page = 1;
int maxrows = 10;

"SELECT *
FROM table
WHERE 1=1
LIMIT " & 1 * page & ", " & maxrows * page 6 ";";

This code is paging the results in groups of 10 rows.

fly904 · Answer 3 · 2011-01-09T06:46:00

Solution 3

I wrote a Tip/Trick to do this last year.

Check it out - Dynamic Pagination[^]

Posted 9-Jan-11 6:46am

fly904