[SOLVED] get the autoincrement value of newly inserted row

[anatak]

Hello,

Is there a function to get the value of an autoincrement (or any field) back after inserting a new row ?

example
INSERT INTO tbl_name (col2,col3) VALUES(firstname, lastname);
col1 is an autoincrement.

How can you get the value of col1 of the new row back ?

kind regards
anatak

[simcoweb]

Yep, you can do something like this:

$newid = mysql_insert_id();

[suzzane2020]

u cn do it this way too..

just ater inserting the new row


INSERT INTO tbl_name (col2,col3) VALUES(firstname, lastname);

write a select query on the whole table

$query=mysql_query("select * from tbl_name");
while($row=mysql_fetch_array($query))
{
  extract ($row);
}

echo $id;

//it wud the (autoincrement)id u just inserted

[anatak]

$newid = mysql_insert_id();

Do you know how to do this with ADOdb connections ?
I got the message Warning: mysql_insert_id(): A link to the server could not be established
wich was to be expected.

[trq]

I assume you mean adobc? And no, there is no such function. You would need to use a method something like what suzzane2020 posted, but no need for any loop. Also note that this might be pretty unreliable.

What are you trying to do exactly?

[anatak]

I have a table called article_info which holds all the non text information about an article (Author, Publish date, type of article)
and I have a table called article_text which holds the text information of the article (Title, subtitle, text, language)
Because the article can be translated in different languages I need to separate the Information and the Text of the articles.

The first time an article gets inserted into the database I need to insert the information into the article_info table get the ID of the new row and then insert the text into the article_text table with the ID of the freshly inserted article_info table.

I know that the way suzzanna describes is not the most reliable.

I was thrilled to learn about the mysql_insert_id() because that would save me some trouble.

I know that the best way would be to insert a new row to start
select that row and get the ID
lock the row
update the row with the information values
insert the text into the text table
insert a new row into the information table.

The problem is that I have no idea how you can lock a row.

Any other ways you think I could do this ?

kind regards
anatak

[anatak]

Does anybody know a good solution for this ?

thanks
anatak

[sachavdk]

Well, "SELECT * FROM tbl_name" can be a damn heavy query.
Probably you don't have transactions within milliseconds, so what you simply can do is just selecting the highest id from the table.

SELECT MAX(id_column_name) FROM table_name

It will return just one row with the latest inserted id.
[color=red]Quick edit:[/color] execute the queries directly after eachother:

....
mysql_query("INSERT ....");
mysql_query("SELECT ....");
// do the rest of your code
.....

[Jessica]

[quote author=anatak link=topic=123788.msg512243#msg512243 date=1169644815]
$newid = mysql_insert_id();

Do you know how to do this with ADOdb connections ?
I got the message Warning: mysql_insert_id(): A link to the server could not be established
wich was to be expected.
[/quote]

I use ADOdb and I can use mysql_insert_id. What's the exact code you used?

[flaquito]

[quote author=anatak link=topic=123788.msg512094#msg512094 date=1169623676]
Hello,

Is there a function to get the value of an autoincrement (or any field) back after inserting a new row ?

example
INSERT INTO tbl_name (col2,col3) VALUES(firstname, lastname);
col1 is an autoincrement.

How can you get the value of col1 of the new row back ?

kind regards
anatak
[/quote]

Have a look at
[code]SELECT LAST_INSERT_ID()[/code]

You don't have to return the entire table (?!?), and it's connection based, so you don't have to worry about race conditions. Check the documentation at [url=http://href=http://dev.mysql.com/doc/refman/5.0/en/information-functions.html]href=http://dev.mysql.com/doc/refman/5.0/en/information-functions.html[/url], about halfway down the page.

[anatak]

Jesirose,

Here is what I try to do

$query_test="INSERT INTO article (ArticleAuthor) VALUES('1');";
echo $query_test;
$Result = $dbupdate->execute($query_test);
$newid = mysql_insert_id($Result);
echo $newid;

the query executes correctly but I have no idea how I have to get the value of the autoincrement.

Here is my dbupdate connection in case you need it
//update connection
$dbupdate = &ADONewConnection('mysql');
$dbupdate->Connect($Host, $UpdateUser, $UpdatePassword, $Database);
$dbupdate_charset = $dbupdate->Execute( "SHOW VARIABLES LIKE 'character_set_database'" );
$dbupdate->Execute( "SET NAMES '" . $dbupdate_charset->fields['Value'] . "'" );
$dbupdate->debug = $debug;


thank you for looking

anatak

[anatak]

jesirose,

Could you post your code how to get the autoincrement ?

thanks
anatak

[anatak]

bump

[Jessica]

Dude, you PMed me and I pointed out your error, even though it's against the rules to PM for help!
Take the $Result out of the mysql_insert_id() and TURN ON error reporting.

[anatak]

mysql_insert_id() does not work with ADODb connections in case someone is stumbling on this tread

what you have to do is call a function on the result of your query

 

$QueryInsert="INSERT INTO $TableName03 (CategoryId, SubCategoryId) VALUES ($content_category, $content_subcat);";

//echo $QueryInsert;

$Result01=insert_1_row_check($dbupdate, $QueryInsert);

$id=$dbupdate->Insert_ID();