MySQL: INSERT and SELECT in same query

Question

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?

You'll have to use 2 queries or a transaction. The LAST_INSERT_ID() will be useful: dev.mysql.com/doc/refman/5.1/en/… — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Oct 22, 2011 at 16:49
Probably yes, using INSERT INTO (cols. . .) SELECT cols. . . FROM . . ., but you'll need to state what information is in what tables when you want to execute this SQL. — Larry Lustig
– Larry Lustig, Commented Oct 22, 2011 at 16:49

Len · Accepted Answer · 2011-10-22 17:06:10Z

1

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

answered Oct 22, 2011 at 17:06

Len

5421 gold badge5 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vilx- Over a year ago

And how do you intend to pass a list of ID's to the stored procedure? Perhaps I'm missing something, but I can't find any array-like datatypes in the MySQL manual.

Len Over a year ago

I would pass it as a comma separated list, then parse out the data and build a single insert statement with multiple rows. There are definitely MySql functions to easily do that. Learning how to write SP is one of the most valuable skills in programming, because they are universal across all languages (you just call them with parameters)

Len Over a year ago

more hints:dev.mysql.com/doc/refman/5.0/en/loop-statement.html forums.mysql.com/read.php?98,235478,235683#msg-235683

Vilx- · Accepted Answer · 2011-10-22 17:07:40Z

1

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?

edited Oct 22, 2011 at 17:07

answered Oct 22, 2011 at 16:56

Vilx-

107k93 gold badges294 silver badges432 bronze badges

3 Comments

user330315 Over a year ago

Oracle has a way to insert into more than one table with a single INSERT: download.oracle.com/docs/cd/B19306_01/server.102/b14200/…

Vilx- Over a year ago

Ahh, well then, I take my words back. :P

Dan Over a year ago

That's correct, the interests table is a set number of choices the user can select.

Collectives™ on Stack Overflow

MySQL: INSERT and SELECT in same query

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related