Jump to content

[SOLVED] get the autoincrement value of newly inserted row


anatak

Recommended Posts

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

$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.

Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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
.....
Link to comment
Share on other sites

[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?
Link to comment
Share on other sites

[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.
Link to comment
Share on other sites

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
Link to comment
Share on other sites

  • 9 months later...

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();

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.