MySql PHP Insert with join

Question

I can't get the following Insert to work although the syntax seems correct..

INSERT INTO views T
    JOIN members T2
        ON '$username' = T2.username 
(ITEM_ID, ITEM_TYPE, USER_ID, USER_TYPE) 
VALUES('$itemview', '$type', T2.id, '$usertype')

All the variables are predefined of course..
What am I doing wrong here?

The Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'T JOIN members T2 ON 'testuser' = T2.username (ITEM_ID, IT' at line 1

How can you join two tables if the record hasn't been created yet? — Ja͢ck, Jul 11 '13 at 23:45
[This](http://stackoverflow.com/questions/1382842/mysql-insert-joins) question's answer may be able to help you out. — Jon, Jul 11 '13 at 23:46

score 2 · Accepted Answer · answered Jul 11 '13 at 23:49

If I'm understanding you right, you want this syntax instead:

INSERT INTO views
  (ITEM_ID, ITEM_TYPE, USER_ID, USER_TYPE)
SELECT '$itemview', '$type', id, '$usertype'
FROM members
WHERE username = '$username'

It inserts a record into views with the partial contents of members. The number of rows returned with the SELECT also determines the number of inserted records; you may wish to use LIMIT if that's actually a problem.

MySql PHP Insert with join

1 Answers1