IASK: Legacy

Tech break: MySQL last inserted record index?

โ€”

by

senseiken
in

While working on the website for Client “A” I came across this problem. I need to so a split insert of two newly created records to two tables. In this case a series of “pages” must be linked to one and only one “article”.

Except of course that when both the article and the initial page are being created at one time, I don’t have the luxury of knowing in advance what the article_id of the article record is so that I can set it in the page_article_id. The article_id field is an automatically incrementing one. The number is not available toll after the insertion has happened.

Now, normally if there was something else unique about the record you could insert it, select it and then read the article_id field, but in this database only the article_id is guarenteed to be unique. I know, not the theoretically perfect way to lay it out but there are many other factors at play. Trust me, this is the best option.

Fortunately, a little research shows that both MySQL and PHP have ways for me to access the information I need. Similar abilities exist in all major databases by the way as far as I know, so the principle holds true.

Anyway, for MySQL it turns out that the easiest method seems to be the [[wp:SQL]] statement LAST_INSERT_ID(). This will return the last generated AUTO_INCREMENT index. A simplistic example is…

INSERT INTO foo (auto,text)
    VALUES(NULL,’text’);             

INSERT INTO foo2 (id,text)
    VALUES(LAST_INSERT_ID(),’text’);

This is pretty useful, and it seems to be correctly tied to the connection/session of your context so that a insert by another process in the time between the first and second inserts. You should check and make sure that the first insert actually did insert a record, and you need to be sure that the first insert did trigger a new index or this will not operate as you expect.

The other option is to use the built in PHP function mysql_insert_id() which has some differences from the previous method.

<?php
    $link = mysql_connect('localhost', 'mysql_user','mysql_password');
    if (!$link) {
       die('Could not connect: ' . mysql_error());
    }
    mysql_select_db('mydb');

    mysql_query("INSERT INTO mytable (product) values ('kossu')");
    printf("Last inserted record has id %dn", mysql_insert_id());
?>

One of the advantages here for my use is that this will fit a little better into the generated PHP code from Dreamweaver and it handles some of the details for me as it will return 0 if the previous insert did not generate an index increment. As a bonus, it may be slightly more aware of the PHP session/connection structure.

Anyway, I had to put all this here so I could find it in the future, and I figure it might help someone else out ๐Ÿ™‚