How can I mix PHP variables with SELECT data in a MySQL INSERT query?

Question

I have a DB with table1 (id, name_id, value) and table2 (id, name, other)

then I have some data

$name = 'a name';
$value = 'a value';

I want to use the value $name as table2.name to get table2.id and insert it into table1 as table1.name_id but then I also want to insert $value into table1.

when doing a SELECT I could use JOIN, but the only thing I've found for INSERT is to use a SELECT query instead of VALUES

However, I want to use a value from table2 (id) as well as insert $value but the SELECT method seems to only copy data from one table (table2) to another

Edit: pseudo SQL

<pre>INSERT INTO `table1` (`name_id`, `value`)
VALUES ((`table2`.`id` WHERE `table2`.`name` = $name), $value)</pre>

Please give an example in pseudo SQL because I cannot follow what you are trying to say here at all — RiggsFolly
– RiggsFolly, Commented Aug 26, 2015 at 20:22
updated it, but I think juergen (and Kevin's, although I don't use PDO) are what I'm looking for. — cantsay
– cantsay, Commented Aug 26, 2015 at 20:27

Kevin Nelson · Accepted Answer · 2015-08-26 20:19:58Z

2

Something like this should work (assuming PDO since you didn't specify):

$preparedStmt = $pdo->prepare("INSERT INTO table1 (name_id,value)
    SELECT id, :value FROM table2 WHERE name=:name");
$preparedStmt->execute(array('name'=>$name,'value'=>$value));

answered Aug 26, 2015 at 20:19

Kevin Nelson

7,7134 gold badges34 silver badges42 bronze badges

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

Comments

juergen d · Accepted Answer · 2015-08-26 20:20:49Z

1

insert into table1 (name_id, other)
select id, 'just some static data or data from a variable'
from table2
where name = '$name'

I inserted the variable for better readability. But I recommend using Prepared Statements instead.

edited Aug 26, 2015 at 20:20

answered Aug 26, 2015 at 20:16

juergen d

205k40 gold badges303 silver badges377 bronze badges

2 Comments

cantsay Over a year ago

sorry, I just updated the question. the method you posted is the one I've found, but how could I add another bit of data (which isn't in table2 into a table1 column?

cantsay Over a year ago

ah. I am using prepared statements as well, so would it be select id, ? and then I would just bind params as usual?

Collectives™ on Stack Overflow

How can I mix PHP variables with SELECT data in a MySQL INSERT query?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related