CODEWITHSUNDEEP
mysqlselectinsert
Dan
Dan

Reputation: 519

MySQL: INSERT and SELECT in same query

I have tricky SQL query to create, which if possible will save me a lot of PHP code.

So I have an HTML form where a visitor can create a user account with the fields name, email, password, interests.

For the name, email and password I have a simple INSERT query to enter this info into the database users, however the interests field can have multiple entries so this data is stored in many-to-many join tables (interests, user_interests).

In order for me enter these interests in the table user_interests, I need to obtain the User ID number which was auto-incremented from the initial INSERT query plus loop over another INSERT for each "interest" the user has chosen. Can I accomplish this all in one SQL query?

Upvotes: 0

Views: 3120

Answers (2)

Vilx-
Vilx-

Reputation: 107002

Well, in MySQL an INSERT statement only inserts into one table. So no, it's impossible to accomplish this with a single query. But you can do it with two queries. MySQL supports a syntax where you can insert multiple rows with a single query:

insert into user_interests (user_id, interest_id) values (1,1), (1,2), (1,3)

P.S. I do understand correctly that the interests are a classifier, and adding new rows to the interests table is beyond the scope of this question, right?

Upvotes: 1

Len
Len

Reputation: 542

If you want to do this with one command, then it sounds like a good case to learn how to write MySql stored procedures. The result will be much faster too.

http://dev.mysql.com/doc/refman/5.0/en/stored-routines-last-insert-id.html

http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-compound-statements.html

Upvotes: 1

Related Questions