Help with paging, 200$np

[abcde]

I need help with paging since the php code below show everything from the database which takes too long to load.

Could anyone please help me add paging to this code? 200np$ will be donated to you, and rep will be added.

Thanks

<? 
	$result = mysql_query("SELECT * FROM news WHERE live = '1' ORDER BY id")or die(mysql_error());	
		while($row = mysql_fetch_assoc($result)){
			echo "<a href=".$row['link']." target='_blank'>".$row['title']."</a>\n";
			}
?>

 

[enlytend]

I'm on my way out the door so this is gonna be brief - if you want more details I'll elaborate on it later...

First you need to decide how many items per page to show and set up code to determine what page you're on. For example:

$itemsPerPage = 10;
$pagenum = 1; /* the default */
/*  Page to display is being passed in the query string  */
if(isset($_GET['page']))
{
    $pagenum = $_GET['page'];
} 
/*  Do some validation on pagenum  here and send an error if it isn't a valid integer.  Then ...  */
$offset = ($pagenum - 1) * $itemsPerPage;

You also need to know how many pages there will be so, before retrieving your data, run a query to find out how many rows it will retrieve - i.e. "Select count(*) from news where live = '1'". Store this result in a variable.

Add the item to start with ($offset) and the number of items to retrieve ($itemsPerPage) to your data retrieval query:

SELECT * FROM news WHERE live = '1' ORDER BY id  [B][COLOR="Cyan"][COLOR="RoyalBlue"]LIMIT $offset $itemsPerPage[/COLOR][/COLOR][/B]

Using the itemsPerPage, current page and total # of pages, set up your paging links (can be page numbers or "prev" "next" - however you want to do it) before and/or after your item display. Paging links will be a link to the current file with the page number requested in a query string variable (i.e. myfile.php?page=2).

If you need help with that part, I'll give you an example later.

Hope this helps! Make any sense?

 

[abcde]

omg! it's another language to me.

 

[enlytend]

It's all php code. What I gave you should work pretty much as-is except that I didn't give you code for the page links (or prev/next) in the first post (it's in this one).

The first part goes someplace at or near the top of your file:
How many items do you want on a page?

$itemsPerPage = 10;


$pagenum = 1; /* the default */

This gets the value of 'page' if it was passed to your program by someone clicking a paging link:
/* Page to display is being passed in the query string */
if(isset($_GET['page']))
{
$pagenum = $_GET['page'];
}


You should check that it's a number and not someone trying to hack your database with a sql injection attack - I haven't included that code here.

This next part uses the requested page number and the number of items you want on a page and calculates the number of the first item to get from the database:

$offset = ($pagenum - 1) * $itemsPerPage;

You need to know how many pages you have so get the total # of rows and divide by the # of items you will show on a page:

$result = mysql_query("Select count(*) as numrows from news where live = '1'");
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];
$maxPages = ceil($numrows / $itemsPerPage);

(There's a more elegant way to do that, but this will work).

The "LIMIT ..." that I added to your query says to get the # of items you want to display per page, starting with the one we calculated in the last step.

The code below will display a simple "previous" or "next" link if there is a previous and/or next page to display:

$self = $_SERVER['PHP_SELF'];
if ($pagenum > 1) {
   	 $page  = $pagenum - 1;
	 print("<a href=\"" . $self . "?page=" . $page . "> Previous</a> ");
 }

if ($pagenum < $maxPages) {
  	 $page = $pagenum + 1;
  	 print("<a href=\"" . $self . "?page=" . $page . ">Next </a>");
  }

Gotta get back to my real work, but that should do the trick.

 

[Ik]

I would suggest using the following FREE PHP class

PHP Pagination Script

It does all what you want.

 

[Kate]

A couple unrelated suggestions...

Instead of SELECT *... just select the fields you need to minimize the size of the data set ie.
SELECT field1, field2... FROM table

Do you have an index on the fields that are in the WHERE clause ? If not, mySQL must scan the whole table to retrieve the matching rows. Probably you can add more filtering (based on date range etc). If you have a large number of records, indexing is a must-do :gl:

 

[Ray]

Do you still need help?? I have a custom coded paging script that i can provide you.. I coded it myself and it includes many functions.. No cost