CODEWITHSUNDEEP
phpmysqlpdo
Curly_1874
Curly_1874

Reputation: 23

INSERT INTO SELECT PDO query

I have only just started writing in PHP/MySQl, so you'll understand from the code below, why I'm looking to update it to PDO, there's all kinds on no going on here.

I've been able to perform a basic INSERT using PDO, ie 

$sql=
"INSERT INTO user_collections
('id','user_id','collection_id')
VALUES('',?,?)";

$insertsql=$db_connect->prepare($sql);
$insertsql->execute(array($newUserID,$collection_id));

but I'm stuck on the INSERT INTO... SELECT query below. Bascially, I'm taking information from a form and information from a table and trying to insert into a second table. 

$sql=
"INSERT INTO table1 (id,user_id,collection_id,sticker_id,status)
SELECT '', '$newUserID' ,'".$_POST['collection_id']."',t2.sticker_number,'N')."'
FROM table2 t2
WHERE t2.collection_id='".$_POST['collection_id']."'";
$insertsql=mysqli_query($db_connect ,$sql);

I know that the above works in it's current state, but obviously not the best design. Can anyone help me to translate it into PDO?

Upvotes: 2

Views: 5218

Answers (1)

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324820

Simple enough:

$sql = "
    INSERT INTO `table1` (`id`, `user_id`, `collection_id`, `sticker_id`, `status`)
    SELECT '', ?, ?, `t2`.`sticker_number`, 'N'
    FROM `table2` AS `t2`
    WHERE `t2`.`collection_id` = ?
";

Now just prepare your query and pass in the $newUserID, $_POST['collection_id'] and $_POST['collection_id'] as parameters, and you're all done :)

Upvotes: 3

Related Questions