How to read the last ID # from the table?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
silverme
Forum Newbie
Posts: 13
Joined: Tue Sep 27, 2005 4:24 pm

How to read the last ID # from the table?

Post by silverme »

Currently I have two tables.

In the first table, the table1.1_ID is set to be auto_increment, but table2.2_ID is not.

I am trying to insert a row in each of the two tables. The data from these two new rows are related to each other. So I want the new value of table2.2_ID is equal to value of table1.1_ID which is just created by auto increment.

Here is my approach. There is a hidden field on the submit page. After clicking the submit botton, it will insert a row into table1, then insert a row into table2.

the value of table2.2_ID from the new row will read from the hidden field.

The problem is, I don't know how to let the hidden field to fetch the ID value from table1. I am not familiar with the lanuage syntax.

approach 1:

Code: Select all

<input name="getIDHid" type="hidden" id="getIDHid" value="<? echo last_insert_ID() ?>" />
or

Code: Select all

<input name="getIDHid" type="hidden" id="getIDHid" value="<? echo mysql_insert_ID() ?>" />
neither of them work. That's what I thought: A row has been inserted to table1. The last_insert_ID() function would output the # created by the last auto_increment.

approach 2:

Code: Select all

<input name="getIDHid" type="hidden" id="getIDHid" value="<? echo (mysql_fetch_row(SELECT lg_ID FROM logon ORDER BY lg_ID DESC LIMIT 1))[0] ?>" />
or

Code: Select all

<input name="getIDHid" type="hidden" id="getIDHid" value="<? 
$rowID=mysql_fetch_row(SELECT lg_ID FROM logon ORDER BY lg_ID DESC LIMIT 1);
echo $rowID[0];
?>" />
don't work. I think I may have syntax error but don't know where.
I have a question here. Is there a quicker way to fetch the last row from a table?

May I know how to fix this problem? Are there any other better ways?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

your syntax error is you don't have a query, nor is the sql you are trying to use in a string.

http://dev.mysql.com/doc/refman/4.1/en/ ... elect.html may be of interest.
Post Reply