Paging in php displaying data over multiple pages

Question

Hi I am trying to display my users data over pages

Here is my code:

//Run a query to select all the data from the users table
$perpage = 2;
$result = mysql_query("SELECT * FROM users LIMIT $perpage");

It does display this the only two per page but I was wondering how you get page numbers at the bottom that link to your data

here is my updated code

 $result = mysql_query("SELECT * FROM users");  // Let's get the query    
 $nrResults=mysql_num_rows($result); // Count the results    
if (($nrResults%$limit)<>0) {       
 $pmax=floor($nrResults/$limit)+1;  // Divide to total result by the number of query 
// to display per page($limit) and create a Max page    
 } else {     
$pmax=floor($nrResults/$limit); 
}     
$result = mysql_query("SELECT * FROM users LIMIT 2 ".(($_GET["page"]-1)*$limit).", $limit");  
// generate query considering limit    
while($line = mysql_fetch_array( $result )) 
{   
?>

error

Parse error: syntax error, unexpected $end in E:\xampp\htdocs\Admin.php on line 98

Do you mean links so that the next page will show the next two results? Or what do you mean exactly by "link to your data"? — mdoyle, Apr 25 '12 at 14:05

Jimmy · Answer 1 · 2012-04-25T20:23:37.933

In order to do this you also need to use the offset value in your SQL Statement, so it would be

SELECT * FROM users LIMIT $offset, $perpage

Example:

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

Then to get the links to put on the bottom of your page you would want to get a count of the total data, divide the total by the per page value to figure out how many pages you are going to have.

Then set your offset value based on what page the user clicked.

Hope that helps!

Update:

The unexpected end most likely means that you have an extra closing bracket } in your code which is causing the page to end and still has more code after it. Look through your code and match up the brackets to fix that. There are a few other issues in the code sample you pasted.

$result = mysql_query("SELECT * FROM users" ); //Note if you have a very large table you probably want to get the count instead of selecting all of the data... 
$nrResults = mysql_num_rows( $result ); 
if( $_GET['page'] ) {
  $page = $_GET['page']
} else {
  $page = 1;
}
$per_page = 2;
$offset = ($page - 1) * $per_page; //So that page 1 starts at 0, page 2 starts at 2 etc.
$result = mysql_query("SELECT * FROM users LIMIT $offset,$per_page");
while( $line = mysql_fetch_array( $result ) ) 
{
//Do whatever you want with each row in here
}

Hope that helps

You can then use the nrResults number to figure out how many pages you are going to have... if you have 10 records and you are displaying 2 per page you would then have 5 pages, so you could print 5 links on the page each with the correct page # in the URL...

The first query should ALWAYS be `SELECT COUNT(*) FROM users`. Even if it's not "very large", reading the *entire table* into PHP just to retrieve a single integer is incredibly inefficient! — Doin, Jan 07 '13 at 15:06

score 1 · Answer 2 · 2012-04-25T14:32:37.770

1

Use requests ! http://php.net/manual/en/reserved.variables.request.php

if (((isset($_GET['page'])) AND (is_int($_GET['page']))) {
$perpage = $_GET['page'];
}
$result = mysql_query("SELECT * FROM users LIMIT $perpage");

...

Link http://yourwebsite.com/userlistforexample.php?page=3
or
http://yourwebsite.com/userlistforexample.php?somethingishere=21&page=3

edited Apr 25 '12 at 14:32

answered Apr 25 '12 at 14:07

1

`$perpage = (int) $_GET['page'];`, please – binarious Apr 25 '12 at 14:08
oh yes! it should also check if $_GET['page'] is integer! – Apr 25 '12 at 14:09
error Parse error: syntax error, unexpected '{' in E:\xampp\htdocs\Admin.php on line 12 – Natalie Webb Apr 25 '12 at 14:25
insert ((isset($_GET['page'])) AND (is_int($_GET['page'])) in another brackets.. like this (((isset($_GET['page'])) AND (is_int($_GET['page']))) also edited answer – Apr 25 '12 at 14:33
still another error Parse error: syntax error, unexpected T_VARIABLE in E:\xampp\htdocs\Admin.php on line 12 – Natalie Webb Apr 25 '12 at 16:06
per page shouldn't be = to the $_GET['page'] varialbe. – Jimmy Apr 25 '12 at 17:55
per page should stay the same. The offset should adjust based on which page is passed in. So if you are on page 5, the offset would be 5*per_page so you 5th page would start later. Without the offset variable this code will just continue to display 1 more record each page... – Jimmy Apr 25 '12 at 17:57

Paging in php displaying data over multiple pages

2 Answers2