Insert Select Query

Question

I have 2 tables articles and users.

I want to insert title, content and name into the article table.

I have the the users id stored in a session, is there a way using an insert select query to add title, content and name to the article table whilst getting the name from the users table using the id.

Something like:

INSERT INTO article (title, content, name) VALUES ($post['title'], $post['content'], name) SELECT name FROM users WHERE id = mySessionId

Your schema is wrong: you should store the ID and use `JOIN` to get the name. This is basic normalization (http://sqlmag.com/database-performance-tuning/sql-design-why-you-need-database-normalization) or face update anomalies — dtech, May 17 '13 at 14:21
Please escape those values before putting them in your database for the sake of humanity — naththedeveloper, May 17 '13 at 14:22

score 3 · Accepted Answer · edited May 23 '17 at 10:25

use INSERT INTO..SELECT statement,

$title = $post['title'];
$content = $post['content'];
$insertStatement = "INSERT INTO article (title, content, name)  
                    SELECT '$title', '$content', name 
                    FROM users 
                    WHERE id = mySessionId";

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

Insert Select Query

1 Answers1