Page 1 of 1

Interesting Query

Posted: Thu May 22, 2003 12:15 am
by dstefani
I am trying to create a PDF type of navigation using PHP and MySQL.
Everything is going fine, but I thought I see if there is a way to do part of this more elegantly.

This would be the part were I get the table ID to use in my display query condition. Here's what I need:

The total count of my recordset
The starting ID value
The last ID value

Right now to make it work I do this:

Code: Select all

// get the first ID field
// get total row count
$sql  = "SELECT dealership_id FROM dealerships ORDER BY dealership_id ASC";
$result1 = mysql_query($sql, $dblink) or die(mysql_error() . "<br><b>Query:</b> $sql");

// PAGE COUNTER  /////////////////////////////
$total = mysql_num_rows($result1); // total fields


while($t_rows = mysql_fetch_assoc($result1))
&#123;
	// the starting ID value
    $starting_id = $t_rows&#1111;'dealership_id'];
    break;
&#125;

$sql  = "SELECT dealership_id FROM dealerships ORDER BY dealership_id DESC";
$result2 = mysql_query($sql, $dblink) or die(mysql_error() . "<br><b>Query:</b> $sql");
while($t_rows = mysql_fetch_assoc($result2))
&#123;
	// the starting ID value
    $ending_id = $t_rows&#1111;'dealership_id'];
    break;
&#125;
My instincts tell me that this is very cludgy and there is a much nicer way to do this.

I feel like I'm having brain block, I need a kick!
Thanks!

- D

Posted: Thu May 22, 2003 12:38 am
by McGruff
A common way to paginate is to do one query to get the total number of rows (need that for the page nav links), and then do a LIMIT query to get just the rows for the page.

However, some people recommend just one query - could use mysql_data_seek() to pick out the rows you need.

Posted: Thu May 22, 2003 12:46 am
by dstefani
Ahh, mysql_data_seek()
I have not used that one. Taking a quick look at the manual has got me curious.

I am not one who must do it in one query, but I also like to write sexy code when ever possible. Remember, "chicks dig sexy code"!

Thanks,
- D

A good brain kick, this is what I need during allergy season! 8O