Problem with PHP/MySQL Insert

Question

How to insert data into two or more tables?

Currently I am using this trick. (Let just assume that the syntax is correct)

$Sql1 = "insert into user";
$Sql2 = "insert into files";
$Sql3 = "insert into messages";

query $Sql1
query $$ql2
query $Sql3

So I am using this and I need to optimize.

Also, I think I need to use Foreign key? But I don't know how to do it on phpmyadmin.

and lastly:

This is all I want.

To insert data on the "user" table and then automatically the table "messages" and "files" will have also an ID which is the same with the user.. That is why I use to 3 queries in my code.

Please help me.

Thanks in ADVANCE!

myb

Why do you need to optimize this? Are the three queries posing an actual performance problem? — Pekka
– Pekka, Commented Sep 19, 2011 at 10:46

Community · Accepted Answer · 2023-11-17 19:32:19Z

It is perfectly fine to use multiple queries.

However, you should wrap the 3 queries into one transaction. That is, if one query fails for some reason, the effects of all previous queries (if any) can be rolled back so that your database stays in a consistent state.

Note: Transactions are not possible with MySQL's MyISAM tables. Additionally you have to use PHP's Mysqli (or PDO) to be able to use transactions. (I assume that you're not using some high-level data base abstraction layer.)

As to your question regarding foreign keys: As far as I know, it makes no difference when you're using the MyISAM engine for the tables in your database (which I presume), because MyISAM doesn't even support the explicit modeling of foreign keys. Other table engines like InnoDB, however, can use foreign keys to check certain constraints like "a message must always be associated with an existing person".

Here are some nice examples regarding foreign keys: http://www.postgresql.org/files/documentation/books/aw_pgsql/node131.html

Louis · Accepted Answer · 2011-09-19 10:54:01Z

1

This is the answer, I think : http://dev.mysql.com/doc/refman/5.1/en/example-foreign-keys.html

answered Sep 19, 2011 at 10:54

Louis

2,9012 gold badges21 silver badges25 bronze badges

Comments

CountZero · Accepted Answer · 2011-09-19 11:00:47Z

1

With mysql u can use LAST_INSERT_ID() or in php mysql_insert_id() function. Checkout this manual entries:

http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html

http://php.net/manual/en/function.mysql-insert-id.php

answered Sep 19, 2011 at 11:00

CountZero

2,8924 gold badges22 silver badges20 bronze badges

Collectives™ on Stack Overflow

Problem with PHP/MySQL Insert

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related