使用PDO将多个INSERT分成3个表

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

如何使用PDO执行3个不同的表INSERT,通过for循环生成SQL查询,例如:

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

我的脚本非常庞大,因此要将其缩小到代码的主要因素。

$date_sql = '';
for($i = 0; $i < 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.

我有3个for循环,与我给出的这个循环相同,但是不同的$ _POST值和不同的表:月,年。它将从$ * _ sql变量生成多行SQL查询。

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

完成3个循环后,我将3个sql变量加入到一个字符串中:

$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:

然后我希望它执行处理它的SQL并将值插入表中,如下所示:

$dbh->beginTransaction();

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

$dbh->commit();

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

但这是否是正确有效的方法呢?

3 个解决方案

#1

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.

PDO这样做的方法就是使用预先准备好的语句。准备好之后,只需更改值就可以执行多次。

$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]));
}

#2

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]

#3

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()

不,这不是正确,首选和有效的方法。您想看看PDO的预处理语句:pdo-> prepare(),pdoStatement-> bindParam(),pdostatement-> execute()和pdostatement-> fetch()

#1