Bulk insert from multiple data sources using PDO

Question

I am trying to insert rows into a MySQL table using PHP/PDO, from two sources, namely :

1. Another Table (Same database)
2. PHP

The table I am filling looks like this :

   Table Name : data_sink

+-------+-----------+--------------+-----------+
|  ID   |   data1   |    data2     |   data3   |
+-------+-----------+--------------+-----------+
|  1    |  text_1   |      aa      |        8  |
|  2    |  text_2   |      bb      |        8  |
|  3    |  text_3   |      cc      |        8  |
|  4    |  text_4   |      dd      |        8  |
|  5    |  text_5   |      ee      |        8  |
|  6    |  text_6   |      ff      |        8  |
+-------+-----------+--------------+-----------+

In this, data1 and data2 are taken from another table, however data3 comes from the PHP code. I used the following query to get data from just the table :

$insert_rows = $db->prepare("INSERT INTO data_sink (data1, data2)
SELECT username, usergroup FROM data_origins WHERE <condition>");

I am unsure of how to add data3 into this query. I have found a workaround for this by adding another column to my origin table, and filling all rows with the value "replaceme", and then running the query as follows :

$insert_rows = $db->prepare("INSERT INTO data_sink (data1, data2, data3)
SELECT username, usergroup, temp_value FROM data_origins WHERE <condition>");

I then run an additional query, updating the table wherever data3 is set to "replaceme"

    $update_rows = $db->prepare("UPDATE data_sink SET data3 = :data3 
WHERE data3 = 'replaceme'");

Is there a single step method of solving this problem, to combine inputs from MySQL and PHP? [NOTE : For every batch of inserts, all rows share a common value for data3]

Answer 1

Could be as simple as

$insert_rows = $db->prepare("INSERT INTO data_sink (data1, data2, data3)
SELECT username, usergroup, ? FROM data_origins WHERE <condition>");

$insert_rows->bindParam(1, $data3);
$insert_rows->execute();

This of course assumes that your $data3 is the same for all rows that are being inserted. It's also possible to do a simple arithmatic operation or function call to ensure that each row gets a different value based on $data3