How to insert, if I need to pick up Primary id for other table

Question

SELECT * FROM tableFoo
LEFT JOIN tableBar ON tableFoo.commonColumn = tableBar.commonColumn
WHERE tableBar.commonColumn IS NULL

Above will select records, I would like to insert Code below will insert id to third table.

INSERT IGNORE INTO jos_mt_links (link_id,link_name,alias)
VALUES(NULL,'tex2','hello');         # generate ID by inserting NULL
INSERT INTO jos_mt_cl (link_id,cat_id)
VALUES(LAST_INSERT_ID(),'88');  # use ID in second table

How do I combine these too? Thx

Again, I add this note here, how do I combine these queries tohether. All of them, including the first query (SELECT *....)

Something like this: SELECT records, then insert them, and also insert this....

Answer 1

Cursor is perfect for this. check this simple example

http://www.kbedell.com/2009/03/02/a-simple-example-of-a-mysql-stored-procedure-that-uses-a-cursor/

Answer 2

if you're using php you can just do two querys with mysql_insert_id() in between

mysql_query("INSERT IGNORE INTO jos_mt_links (link_id,link_name,alias)
VALUES(NULL,'tex2','hello')");
$newid=mysql_insert_id();
mysql_query("INSERT INTO jos_mt_cl (link_id,cat_id)
VALUES(LAST_INSERT_ID(),'".$newid."')");

---

re:comment - sorry, total missed that, so you need to run 3 queries

$result=mysql_query("SELECT * FROM tableFoo LEFT JOIN tableBar ON tableFoo.commonColumn = tableBar.commonColumn WHERE tableBar.commonColumn IS NULL");
$row=mysql_fetch_assoc($result);
mysql_query("INSERT IGNORE INTO jos_mt_links (link_id,link_name,alias)
    VALUES(NULL,'".$row['linkfield']."','".$row['aliasfield']."')");
mysql_query("INSERT INTO jos_mt_cl (link_id,cat_id)
    VALUES(LAST_INSERT_ID(),'".$row['catidfield']."')");

i don't know what are the names of your fields in tableFoo and tableBar, so just replace linkfield/aliasfield/catidfield with those names.