Last insert id in autoincrement field

[hryan]

Hi!
1/ I want to read a field in last record. How can I know which one
or
How can I recognize what is the last id number into its autoincrement field?

(It is not possible with mysql_insert_id() in PHP
or LAST_INSERT_ID() in mySQL
because they return a status of MY ACTIONS and a current connection)

2/ Can I create a query like this

SELECT aa,ss,dd from tbl WHERE MAX(id)
or
I have to make 2 queries
SELECT MAX(id) FROM tbl
+
SELECT aa,ss,dd from tbl WHERE id=upper_find_value

3/ Can I execute more of one query from PHP with one command?

Thanks!

[Mouldy_Goat]

Hi hryan,

I'm not sure if there's a better way but I've tried a couple of things out in phpMyAdmin and from what I can gather the way to do it would be something like:

<?php
# put your stuff to connect to your database here

$sql = "SELECT MAX(ID) FROM tbl";
$result = mysql_query($sql);
$row = mysql_fetch_row($result);
$max_id = $row[0];

$sql = "SELECT * FROM tbl WHERE ID=$max_id";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);

It works fine...

Not sure what your 3rd question was about.
Hope that helps a bit...

[justlearning]

To answer your third question, you can do multiple commands in one statement by separating them with a semi-colon. However, I do not see how it will help in this instance.

To do a multiple statement, it could be written like this:

$sql = "SELECT * FROM table1; SELECT * FROM table2";

You would not be able to read information on one command and then use it in the next command on the same line.

To select the highest id number in one statement, you would need to use subselects, a function that is too limited in the current version of MySQL.

The SQL statement would be something like:

SELECT * FROM table1 WHERE id = (SELECT MAX(id) FROM table1);

As per the MySQL manuel at http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html , MySQL curruntly only supports the subselect in insert and replace statments rather than in select statement. Version 4.1 should have a stronger implementation of subselets.

Until then, the best way to look up the record with the highest ID is the method shown by Mouldy_Goat.

JustLearning

[hryan]

THANKS! This works fine!