stack stack - 6 months ago 11
MySQL Question

How to insert into table based on other one?

I have these two tables:

// users
+----+-------+-----------------------+--------+
| id | name | email | active |
+----+-------+-----------------------+--------+
| 1 | peter | peter12@hotmail.com | NULL |
| 2 | jack | most_wanted@gmail.com | NULL |
| 3 | john | john_20016@yahoo.com | NULL |
+----+-------+-----------------------+--------+

// activate
+----+---------+---------------------+
| id | post_id | random_string |
+----+---------+---------------------+
| 1 | 2 | fewklw23523kf |
+----+---------+---------------------+


Also I have these two variables:

$random string = 'gergflkw4534l';
$email = 'peter12@hotmail.com';


And I want to insert a new row into
activate
table based on those two variables. Here is expected result:

// activate
+----+---------+---------------------+
| id | post_id | random_string |
+----+---------+---------------------+
| 1 | 2 | fewklw23523kf |
| 2 | 1 | gergflkw4534l |
+----+---------+---------------------+


As you see, I get
post_id
from
users
table based on
email
column. How can I do that?




INSERT INTO activate VALUES (null, /* I don't know */, :random_string)
INNER JOIN users ON /* I don't know */
WHERE email = :email

Answer

Your syntax is very much off. You need a WHERE clause before you can actually JOIN anything to it. I'm going to assume that the id column in the Activate table is an autonumber column.

INSERT INTO Activate (post_id, random_string)
SELECT
    id,
    :random_string
FROM
    Users
WHERE
    email = :email