SQL relational insert into 2 tables in one query without using mysql_insert_id ()

Question

I'm not quite new to SQL, but rusty and struggling with MYSQL (using PhPMyAdmin) ... looked at this: MySQL inserts into several tables (relational) and some other related topics, but could not find the answer. I simplify my example to understand the point.

If you have two tables:

'table1' has: id (primary key, auto-increment), description (text) 'table2' has: id (primary key, auto-increment), title (text), description_id (int) 

How to create a singe INSERT statement so that description_id retains the value for table1.id ?

I know there are php ways to make 2 queries, but I would like to do all this in SQL, should there be a way? Should I customize my tables differently? I read something about foreign keys and don’t know if this applies.

Thanks!

Answer 1

thanks @hackattack, who found this? answered already in another place .

 BEGIN INSERT INTO users (username, password) VALUES('test', 'test') INSERT INTO profiles (userid, bio, homepage) VALUES(LAST_INSERT_ID(),'Hello world!', 'http://www.stackoverflow.com'); COMMIT; 

BUT ALAS - it didn't work. The MySQL 5 link shows a slightly different syntax:

 INSERT INTO `table2` (`description`) VALUES('sdfsdf');# 1 row affected. INSERT INTO `table1`(`table1_id`,`title`) VALUES(LAST_INSERT_ID(),'hello world'); 

And, lo / behold - it works!

More problems to come. Although the request will succeed in phpMyAdmin, my PHP installation complains about the request and throws a syntax error. I resorted to this php-way and made 2 separate requests and used mysql_insert_id()

I find this annoying, but I think there is not much less server load than a transaction.

Answer 2

You cannot insert more than one table (the recorded views are toward *, but AFAIK MySQL does not support them) with the insert statement.

What might interest you is transactions that allow you to group statements into a single atomic single. "

^{_{* Which, in fact, would only allow updates, not inserts (I suppose, but now I'm starting to doubt it ... anyway, it's not a problem here anyway)}}

Answer 3

You probably need to use triggers so that whenever table2 receives an insert operation, the trigger executes the insert in table1.