CODEWITHSUNDEEP
phpmysqldatabasedatabase-optimization
bEj ni c bEj
bEj ni c bEj

Reputation: 51

Problem with PHP/MySQL Insert

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

Upvotes: 0

Views: 102

Answers (3)

robert
robert

Reputation: 3615

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

Upvotes: 3

CountZero
CountZero

Reputation: 2882

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

Upvotes: 1

Louis
Louis

Reputation: 2890

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

Upvotes: 1

Related Questions