Multiple INSERTs into 3 tables with PDO

How can you do a 3 different table INSERTs with the PDO that a SQL query is generated through a for loop, e.g.

My script is well huge so going to narrow it down to the main factors of the code.

$date_sql = '';
for($i = 0; $开发者_如何学Pythoni < count($_POST['date_range']); $i++)
{
    // codez

    $date_sql .= " INSERT INTO dates (date_one, date_two) VALUES('" . $_POST['date_range'][$i] . "', '" . $_POST['date_range_end'][$i] . "'); ";

    // more codez
}

I have 3 for loops which is same as this loop I've given, but different $_POST values and different tables: months, years. It would generated a multi line SQL query from the $*_sql variable.

After the 3 loops are done, I join the 3 sql variables into a string:

$main_sql = $date_sql . $month_sql . $year_sql;

Then I want it to execute the SQL that processes it and inserts the values into the tables, like so:

$dbh->beginTransaction();

$sth = $dbh->exec($main_sql);

$dbh->commit();

But is this the right, effective way of doing this?

The more PDO way of doing this would be to use a prepared statement. After you've prepared it, you can execute it multiple times just changing the values.

$sql = "INSERT INTO dates (date_one, date_two) VALUES(:one, :two)";
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));

for($i = 0; $i < count($_POST['date_range']); $i++) {
   $sth->execute(array(':one' => $_POST['date_range'][$i], ':two' => '$_POST['date_range_end'][$i]));
}

The way you are descripting will work, it will execute three commands, but since they are very small it will not impact the database that much.

Another way to go would be to format a query that looks like:

INSERT INTO dates (date_one, date_two)
SELECT $_POST['date_range'][0] , $_POST['date_range_end'][0]
UNION SELECT $_POST['date_range'][1] , $_POST['date_range_end'][1]
UNION SELECT $_POST['date_range'][2] , $_POST['date_range_end'][2]

No, this is not the right, preferred and efficient way to do it. You'd like to have a look at PDO's prepared statements: pdo->prepare(), pdoStatement->bindParam(), pdostatement->execute() and pdostatement->fetch()