How to insert multiple lines with PDO-based instructions based on a SELECT query?

advertisements

This question already has an answer here:

  • is insert based on select on one of the column in MySQL possible? 3 answers

I've found all of my employee ids (empid) who are in a specified group:

$groupid = $_GET['groupid'];
$myquery=$con->prepare("SELECT e.id AS empid FROM employees e WHERE e.groupid=?");
$myquery->execute([$groupid]);

I would now like to insert a new row into a different table for EACH employee ID (empid) i found above.

Would anyone be able to explain this to me?


As you're trying to select all rows from one table, and insert into another, there are actually various ways of doing this.

Using the code you've started above, you could use a prepared query:

$groupid = $_GET['groupid'];
$myquery = $con->prepare("SELECT e.id AS empid FROM employees e WHERE e.groupid = :groupid");
$myquery->execute([':groupid' => $groupid]);

$insert_query = $con->prepare("INSERT INTO new_table (empid) VALUE (:empid)");
$insert_query->bindParam(':empid', $empid);

while ($result = $myquery->fetch()) {
    if ($empid = $result['empid']) {
        $insert_query->execute();
    }
}

However, this is rather slow, and instead, I'd recommend a single query:

$groupid = $_GET['groupid'];
$con->prepare("INSERT INTO new_table (empid) SELECT e.id FROM employees WHERE groupid = :group_id");
$con->execute(['group_id' => $group_id]);

This has the advantage of passing off all the logic to the SQL server, so you're not passing data back to the PHP only for it to be passed back to the SQL server. Obviously, if you need to do any PHP processing, then you'll need the first option.