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”.<\/p>\n
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.<\/p>\n
<\/p>\n
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.<\/p>\n
Fortunately, a little research shows that both MySQL<\/a> and PHP<\/a> 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. <\/p>\n Anyway, for MySQL it turns out that the easiest method seems to be the [[wp:SQL]] statement LAST_INSERT_ID()<\/a>. This will return the last generated AUTO_INCREMENT<\/a> index. A simplistic example is…<\/p>\n INSERT INTO foo2 (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<\/i><\/b> 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.<\/p>\n The other option is to use the built in PHP function mysql_insert_id()<\/a> which has some differences from the previous method. <\/p>\n mysql_query(\"INSERT INTO mytable (product) values ('kossu')\");\n\n
\n INSERT INTO foo (auto,text)
VALUES(NULL,’text’); <\/p>\n
VALUES(LAST_INSERT_ID()<\/b>,’text’);<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n\n\n
\n <?php
$link = mysql_connect('localhost', 'mysql_user','mysql_password');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db('mydb');<\/p>\n
printf(\"Last inserted record has id %dn\", mysql_insert_id());
?> <\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n