Is this mix of php and mysql translatable into pure MySQL ?

[roparzhhemon237]

I have following piece of code below, and I would like to be able to express it pure SQL, something that could go into a .sql file :

$request_string='SELECT topic_forum_id FROM topic_table WHERE topic_id= 2014';
$query=database->prepare($request_string);
$query->execute();
$data=$query->fetch();
$query->closeCursor();
$forum=$data['topic_forum_id'];

$request_string='INSERT INTO post_table
(post_topic,post_forum) VALUES (2014,:forum)';
$query=database->prepare($request_string);
$query->bindValue(':forum',$forum,PDO::PARAM_INT);
$query->execute();
$data=$query->fetch();
$query->closeCursor();

 

Is it possible ?

[Barand]

If that hard-code "2014" never needs to change

INSERT INTO post_table (post_topic, post_forum) 
    SELECT 2014, topic_forum_id 
    FROM topic_table
    WHERE topic_id= 2014;

[roparzhhemon237]

 

If that hard-code "2014" never needs to change

INSERT INTO post_table (post_topic, post_forum) 
    SELECT 2014, topic_forum_id 
    FROM topic_table
    WHERE topic_id= 2014;

This sort of code is completely new to me. I understand SELECT acts a subquery in it ? Where is that construct explained in the MySQL manual ?

[ginerjm]

acts?  What's a 'acts'?

[Ch0cu3r]

An insert select statement is explained here

 

Basically the rows returned by the select statement will used as the values to insert into the table.

[Barand]

acts?  What's a 'acts'?

 

act:

verb

• behave
• perform
• operate
• function

 

@roparzhhemon237,

 

It isn't a subquery, it is part of the INSERT...SELECT syntax, as Ch0cu3r's link shows

Edited October 28, 2014 by Barand

[roparzhhemon237]

An insert select statement is explained here

 

Basically the rows returned by the select statement will used as the values to insert into the table.

 

Thank you for your helpful answer.

Can I use this construct with UPDATE also ? E.g, can I write something like

UPDATE post_table SET post_forum_id=(SELECT topic_forum_id FROM table_topic WHERE topic_id=2014) WHERE post_id=237;
 

This looks like a real subquery this time.

[Barand]

Probably. Let us know.

 

Or, you could

UPDATE post
    JOIN topic ON topic_id = 2014
SET post_forum_id = topic_forum_id
WHERE post_id = 237;

Edited October 29, 2014 by Barand