First and Last ID of a table

--Apple-Mail-13--281266186
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
delsp=yes;
format=flowed

I have a table where I need to figure out the very first ID and the
very last ID, so here is what I wrote:

$first_query = "SELECT id FROM mytable ORDER BY id LIMIT 1";
$first_result = mysql_query($first_query,$con);
$first_id = mysql_result($first_result,0,'id');

$last_query = "SELECT id FROM mytable ORDER BY id DESC LIMIT 1";
$last_result = mysql_query($last_query,$con);
$last_id = mysql_result($last_result,0,'id');

I'm just wondering if there was any way to do this more efficiently,
like with one query instead of two. Or is this about as simple as I
can do it?

Thanks.



--
Kevin Murphy
Webmaster: Information and Marketing Services
Western Nevada College
www.wnc.edu
775-445-3326



--Apple-Mail-13--281266186--

Re: First and Last ID of a table

SELECT MIN(id), MAX(id) FROM mytable

:)

Hope that helps!

-TG

= = = Original message = = =

I have a table where I need to figure out the very first ID and the
very last ID, so here is what I wrote:

$first_query = "SELECT id FROM mytable ORDER BY id LIMIT 1";
$first_result = mysql_query($first_query,$con);
$first_id = mysql_result($first_result,0,'id');

$last_query = "SELECT id FROM mytable ORDER BY id DESC LIMIT 1";
$last_result = mysql_query($last_query,$con);
$last_id = mysql_result($last_result,0,'id');

I'm just wondering if there was any way to do this more efficiently,
like with one query instead of two. Or is this about as simple as I
can do it?

Thanks.



--
Kevin Murphy
Webmaster: Information and Marketing Services
Western Nevada College
www.wnc.edu
775-445-3326


___________________________________________________________
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: First and Last ID of a table

wrote in message
news:20070711201352.905EE10A008@spaceymail-a2.g.dreamhost.co m...
> SELECT MIN(id), MAX(id) FROM mytable

As an aside, is you are using associative arrays, be sure to use the
following keys:

$array['MIN(id)'] and $array['MAX(id)']

Just something I figured out recently :)

Matt

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: First and Last ID of a table

Sounds about right... you can also do something like this (syntax should be right):

SELECT MIN(id) as minid, MAX(id) as maxid FROM mytable


$array['minid'] and $array['maxid']


Basically it's going to be whatever the heading of that column is. Using "as" gives it an alias for less ugly headings.

If you did SELECT COUNT(Qty) FROM SomeTable

Then you might get:

$array['Count of Qty'] or something goofy like that. I forget the exact circumstances but there's times you get goofy stuff like that.

If you run the SQL through some DB client (like mysql's command line stuff or I use WinSQL Lite in Windows to connect to our MySQL database across the network) you can usually see what the heading name is going to end up being, if you don't explicitly set it with an "AS" clause.

"AS" also works on table names:

SELECT l.LeadID, ld.FirstName FROM Leads as l, LeadData as ld
WHERE l.LeadID = ld.LeadID

(actually a lot of the time you can leave out the "as" and just do "Leads l")

Fun times..

-TG


= = = Original message = = =

wrote in message
news:20070711201352.905EE10A008@spaceymail-a2.g.dreamhost.co m...
> SELECT MIN(id), MAX(id) FROM mytable

As an aside, is you are using associative arrays, be sure to use the
following keys:

$array['MIN(id)'] and $array['MAX(id)']

Just something I figured out recently :)

Matt

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


___________________________________________________________
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: First and Last ID of a table

On Wed, 11 Jul 2007, Matt Leonhardt wrote:

> wrote in message
> news:20070711201352.905EE10A008@spaceymail-a2.g.dreamhost.co m...
>> SELECT MIN(id), MAX(id) FROM mytable
>
> As an aside, is you are using associative arrays, be sure to use the
> following keys:
>
> $array['MIN(id)'] and $array['MAX(id)']
>
> Just something I figured out recently :)

or use select min(id) min, max(id) max from mytable

then access as $array['min'] and $array['max']

------------------------------------------------------------ ---------------
Peter Beckman Internet Guy
beckman@angryox.com http://www.angryox.com/
------------------------------------------------------------ ---------------
** PLEASE NOTE PurpleCow.com IS NOW AngryOx.com DO NOT USE PurpleCow.com **
** PurpleCow.com is now owned by City Auto Credit LLC as of May 23, 2007 **
------------------------------------------------------------ ---------------

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php