Pagination... A matter of opinion.

[bokeh]

Which is the better of the following two from an SQL point of view and why?$page = isset($_GET['page']) ? $_GET['page'] : null ;

$results_per_page = 10;

connect();
$query = "SELECT * FROM `news_items` ORDER BY `start` DESC";
$result = mysql_query($query) or die(mysql_error());
if($num_rows = mysql_num_rows($result))
{
// make sure page is within range
$total_pages = $num_rows ? ceil($num_rows / $results_per_page) : 1 ;
$page = ((is_numeric($page)) and ($page >= 1) and ($page <= $total_pages)) ? (int)$page : 1 ;

// find the starting row
mysql_data_seek($result, ($results_per_page * $page) - $results_per_page);
$i = $results_per_page;
$content .= '<div class="left_col">'."\n\n";
while($row = mysql_fetch_assoc($result) and $i)
{Or this:$page = isset($_GET['page']) ? $_GET['page'] : null ;

$results_per_page = 10;

connect();

$query = "SELECT COUNT(*) as `count` FROM `news_items`";
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_assoc($result);
$num_rows = $row['count'];

// make sure page is within range
$total_pages = $num_rows ? ceil($num_rows / $results_per_page) : 1 ;
$page = ((is_numeric($page)) and ($page >= 1) and ($page <= $total_pages)) ? (int)$page : 1 ;


$query = "SELECT * FROM `news_items` ORDER BY `start` DESC LIMIT $results_per_page OFFSET ".(($results_per_page * $page) - $results_per_page);
$result = mysql_query($query) or die($query .' - '. mysql_error());
if(mysql_num_rows($result))
{
$i = $results_per_page;
$content .= '<div class="left_col">'."\n\n";
while($row = mysql_fetch_assoc($result))
{

[chazzy]

the first one will run faster since php is calling mysql's library to determine the # of rows. no need to use the sql parser.

[NogDog]

My one concern with the first method is that you're retrieving the entire contents of the table and storing it in memory (RAM or disk), when all you actually need are 10 rows. How that balances out against only making one query versus 2 I have no idea, though I'm sure it depends on the number of rows in the table and the amount of data per row.

[bokeh]

My one concern with the first method is that you're retrieving the entire contents of the tableDoes that mean if the DB and PHP were on separate boxes the whole table is transfered from one to the other? I was under the impression the rows were downloaded on demand but it looks like I'm wrong.

[NogDog]

I don't know for sure how and where the query results are stored. The only hint I've run across so far is from the documentation for the mysql_free_result (http://www.php.net/mysql_free_result) page:
mysql_free_result() will free all memory associated with the result identifier result.

mysql_free_result() only needs to be called if you are concerned about how much memory is being used for queries that return large result sets. All associated result memory is automatically freed at the end of the script's execution.
This seems to imply the entire result set is stored somewhere, but doesn't say whether it's on the DB server or the web server.

[chazzy]

no, entire result sets are sent upon request.

but this is normal in all databases - most don't have a LIMIT keyword.

[bokeh]

Ok! I've done a test with a packet sniffer. I ran the following:$query = "SELECT * FROM $tablename";
$query_result = mysql_query($query) or die (mysql_error());and it downloads the whole table to local which shows the rows are not pulled on demand. I can't see how downloading a 100,000 row table to local could be a good thing if I only want 10 rows so it look like method 2 wins... unless I am missing something.

[chazzy]

Ok! I've done a test with a packet sniffer. I ran the following:$query = "SELECT * FROM $tablename";
$query_result = mysql_query($query) or die (mysql_error());and it downloads the whole table to local which shows the rows are not pulled on demand. I can't see how downloading a 100,000 row table to local could be a good thing if I only want 10 rows so it look like method 2 wins... unless I am missing something.

you could cache it locally.

[bokeh]

Seams a lot of trouble to me.