Lastest 20 record from a table + pagination ?

PHPLOVER07

Hi,

How i can dot he following with my script :-
Lastest 20 record from a table
+
pagination ?

Thanks

rulian

3 ways:
A) 1 query that retrieves all rows, use PHP to limit display - kills sql time
😎 use 2 queries, 1 to retrieve rows, 1 to count matches - can be optimal depeding on situation
or C) use this: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows
its a little wierd to get use to at first, and there's some debate over which is better, but you be the judge

bpat1434

I'd advise some research before you institute option (A) as that can kill your CPU 😉 Letting SQL actually do the limiting can make your application faster. One thing you could do, is run a very quick query to get the IDs of those records you want. Then, when the IDs are returned, you can count() the array, divide that count by the number you want displayed, then figure out where you should "start" in the rows by some simple math:
($page - 1) $num_per_page
which would give you the correct number to start with. For example:
(1 - 1) 25 = 0 (first page, start at the first returned record)
(2 - 1) * 25 = 25 (second page, start at the 25th record)

Then run a query selecting records with IDs only in the previously retrieved IDs, and starting at the start which was calculated by you, and limiting it to 25 rows. With larger datasets, this can really speed things up.

Unless you can control your environment, I'd be weary of option (c) as well. If two queries go off one right after the other, and you run "SELECT FOUND_ROWS()" expecting the result of the first, you'd actually get the result of the second. Here's a quick break-down in time:

[17:08:05.00 GMT] - UserA runs query1 (SELECT FROM table LIMIT 0, 100)
[17:08:05.50 GMT] - UserB runs query2 (SELECT FROM alt_table LIMIT 0, 50)
[17:08:05.90 GMT] - UserA executes query3 (SELECT FOUND_ROWS())
[17:08:05.99 GMT] - UserB executes query3

Now, both UserA and UserB would see the exact same row count even though they both queried different tables for different information. I could be wrong (by that I mean MySQL may be able to differentiate between multiple sessions of your script), but it's still something you want to be careful about.

PHPLOVER07

I need more help !!!

1) How i can create a pagination ?

2) How i can view the last 10 record from a table?

cgraz

search for the forum for pagination
select * from <your_table> order by <someField> desc limit 10

rulian

PHPLOVER:

Pagination is actually quite simple,
Figure out how many results you want to display at a time: lets say 20
Get the total number of rolls - lets say 130
Divide: 230 / 20 = 11.5
round UP : 12
This would be 12 pages.
so you would do a for loop:
for ($i=0; $i<$pages; $i++)
{
echo "<a href='mypage.php?page=".($i + 1)."'>Page ".($i + 1)."</a>";
}

and when somebody clicks on a page, you take that page offset and multiply it by your number of results displays (in this case 20) that would be your start number, so lets say I clicked on page 3
3 x 20 = 60, so my query would be LIMIT 60, 20

The only trouble spot is getting the count or all your results without comprimising your application. Taht is what we're dicussing right now.

foyer

I'll copy paste some code I recently wrote up maybe it can help you.. sort of adding on what rulian said.

//$page is what page you are on
//$display is how many to display at once
//$atotal is how many rows are in the database table
function show_pages($page,$display,$atotal) {
	if ($display < $atotal) {	
		$html = "Pages: ";

	$total = ceil($atotal / $display);
	//^ will tell you how many pages are needed.
	//now print the pages
	for ($i=1;$i<=$total;$i++) {
		if ($i == $page) {
			$html .= "$i ";
		} else {
			$html .= "<a href=\"?page=$i\">$i</a> ";
		}
	}
} else {
	if (!$atotal) $atotal = 1;
	$html = "Displaying 1 of $atotal";
}

return $html;
}

In my function that displays the results..

	$lastnum = $page * $display;
	$firstnum = ($lastnum - $display);	

$sql .= " LIMIT $firstnum,$display";