PDO Array from MySQL Query into Insert Queries

Question

I have an array which I am getting from the following PDO:

$sqlQry = "SELECT Devices.dID FROM Friends LEFT OUTER JOIN Devices ON Friends.fID = Devices.dUID WHERE Devices.dID IS NOT NULL GROUP BY Devices.dID";
$db = getConnection();
$sth = $db->prepare($sqlQry);
$sth->execute();
print("Fetch all of the remaining rows in the result set:\n");
$result = $sth->fetchAll();
print_r($result);

the array returns:

Array ( [0] => Array ([dID] => 2[0] => 2 ) [1] => Array ( [dID] => 3 [0] => 3 ))

For each one of the dID values I need to run an insert query that takes the dID and inserts it into a table in the same database with outer values.

$sqlIns = "INSERT INTO messages (dID, message, status") VALUES (?,?,?);

The message and status will be held in a variable

Can any one help me out with this one?

Why don't you do this all in one query?

D'Arcy Rittich
– D'Arcy Rittich

2012-07-17 13:04:18 +00:00
Commented Jul 17, 2012 at 13:04 — D'Arcy Rittich
– D'Arcy Rittich, Commented Jul 17, 2012 at 13:04

D'Arcy Rittich · Accepted Answer · 2012-07-17 13:11:00Z

2

You can do this all in one query. See the documentation: INSERT ... SELECT Syntax

INSERT into messages (dID, message, status) 
SELECT Devices.dID, ?, ? 
FROM Friends 
LEFT OUTER JOIN Devices ON Friends.fID = Devices.dUID 
WHERE Devices.dID IS NOT NULL 
GROUP BY Devices.dID

The PDO would look something like this:

// database connection
$conn = new PDO(...);

// new data
$message = 'xxx';
$status = 'yyy';

// query
$sql = "INSERT into messages (dID, message, status) 
    SELECT Devices.dID, ?, ? 
    FROM Friends 
    LEFT OUTER JOIN Devices ON Friends.fID = Devices.dUID 
    WHERE Devices.dID IS NOT NULL 
    GROUP BY Devices.dID";
$q = $conn->prepare($sql);
$q->execute(array($message,$status));

edited Jul 17, 2012 at 13:11

answered Jul 17, 2012 at 13:04

D'Arcy Rittich

172k41 gold badges297 silver badges287 bronze badges

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

1 Comment

Justin Erswell Over a year ago

thanks would you post how you would do it I am new to PDO and it would really help me

hjpotter92 · Accepted Answer · 2012-07-17 13:06:01Z

0

foreach( $result as $idValue ) {
    $sqlIns = "INSERT INTO messages (dID, message, status) VALUES ($idValue[0],?,?)";
}

That should do the trick. Though I am not sure why are you using fetchAll.

answered Jul 17, 2012 at 13:06

hjpotter92

81k36 gold badges148 silver badges188 bronze badges

Collectives™ on Stack Overflow

PDO Array from MySQL Query into Insert Queries

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related