Insert into 2 tables from 1 form

Question

How do I insert from 1 form into 2 tables?

I want to submit into tables: gallery and cat.

This is the code I have for just submitting into gallery. How do I add info to the cat table simultaneously?

$sql = 'INSERT INTO gallery (image_url, image_name, image_description)
VALUES (?, ?, ?)';


$stmt = $conn->stmt_init();
if ($stmt->prepare($sql)) {

$stmt->bind_param('sss', $_FILES['upload']['name'], $_POST['image_name'],     
$_POST['image_description']);
$OK = $stmt->execute();

}

What do I do with this:

$sql_2 = 'INSERT INTO cat (cat_name) VALUES (?)';

So where you stuck ? Go ahead & bind category in $sql_2 & execute it. — Rikesh, Mar 27 '13 at 04:28
ok, where do I that? inside that if statement? or make a new one? — Renee Rose-Perry, Mar 27 '13 at 04:36
Your Common Sense (your advice is not helping, I wish I could down vote you) — Renee Rose-Perry, Apr 17 '13 at 07:58

score 1 · Answer 1 · answered Mar 27 '13 at 05:07

After the code you have above, just initialize a new statement, bind it, and execute. You can use the existing connection ($conn). The $sql and $stmt variables have already done their job by inserting into the first table, so just reset them for the second insert.

... your code as posted above, followed by:

$sql = 'INSERT INTO cat (cat_name) VALUES (?)';
$stmt = $conn->stmt_init();
if ($stmt->prepare($sql)) {
  $stmt->bind_param('s', put your cat_name value here);
  $OK = $stmt->execute();
}

The important thing to remember is that the second insert is just like the first: you have to initialize it, bind it, and execute it.

Thanks, Captain, for the answer employing common sense ;) – Your Common Sense Mar 27 '13 at 05:25 — Your Common Sense, Mar 27 '13 at 05:25
Unfortunately, your answer does not solve the problem. – Renee Rose-Perry Apr 17 '13 at 07:56 — Renee Rose-Perry, Apr 17 '13 at 07:56

score 0 · Answer 2 · edited May 23 '17 at 11:57

0

You should use transactions as demonstrated here.

edited May 23 '17 at 11:57

Community

1
1

answered Mar 27 '13 at 04:36

tixastronauta

404
4
17

I really would like to understand the downvote. Care to elaborate? – tixastronauta Apr 04 '13 at 05:30

score -1 · Answer 3 · answered Mar 27 '13 at 04:51

No, you can't insert into multiple tables in one MySQL command. You can however use transactions.

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;

Have a look at LAST_INSERT_ID to reuse autoincrement values. Edit: you said "After all this time trying to figure it out, it still doesn't work. Can't I simply put the just generated ID in a $var and put that $var in all the MySQL commands?"

Let me elaborate: there are 3 possible ways here:

1/ Is the code you see above. This does it all in MySQL, and the LAST_INSERT_ID in the second statement will automatically be the value of the autoincrement-column that was inserted in the first statement.

Unfortunately, when the second statement itself inserts rows in a table with an auto-increment column, the LAST_INSERT_ID will be updated to that of table 2, and not table 1. If you still need that of table 1 afterwards, we will have to store it in a variable. This leads us to ways 2 and 3:

2/ Will stock the LAST_INSERT_ID in a MySQL variable:

A/ INSERT B/ SELECT LAST_INSERT_ID into @mysql_variable_here C/ INSERT INTO table2 (@mysql_variable_here D/ INSERT INTO table3 (@mysql_variable_here ...

3/ Will stock the LAST_INSERT_ID in a php variable (or any language that can connect to a database, of your choice):

A/ INSERT B/ Use your language to retrieve the LAST_INSERT_ID, either by executing that literal statement in MySQL, or using for example php's mysql_insert_id() which does that for you C/ INSERT ()

http://stackoverflow.com/questions/15651864/insert-into-2-tables-from-1-form/15652135#15652135 this link will help u — Naveen, Mar 27 '13 at 05:02
check this link http://stackoverflow.com/questions/15651864/insert-into-2-tables-from-1-form/15652135#15652135 u will get ur answer — Naveen, Mar 27 '13 at 05:12

Insert into 2 tables from 1 form

3 Answers3