php - 使用MySQL将多行插入数据库

Question

我有以下代码将 1 行插入数据库:

public function fixed($fieldDay, $fieldNight) {

    $pdo = new SQL();
    $dbh = $pdo->connect(Database::$serverIP, Database::$serverPort, Database::$dbName, Database::$user, Database::$pass);

    $this->sql = "INSERT INTO tblfixedfare (SELECT NULL, MAX(FixedFareID)+1, '1', '$fieldDay' FROM tblfixedfare)";

try {
        // Query
        $stmt = $dbh->prepare($this->sql);

        $stmt->execute();

        $count = $stmt->rowCount();

        echo $count.' row(s) inserted by SQL: '.$stmt->queryString;

        $stmt->closeCursor();

    }

    catch (PDOException $pe) {
        echo 'Error: ' .$pe->getMessage(). 'SQL: '.$stmt->queryString;
        die();
    }

    // Close connection
    $dbh = null;
}

}

在执行的同时，我想在同一个表中插入另一行:

$this->sql = "INSERT INTO tblfixedfare (SELECT NULL, MAX(FixedFareID)+1, '2', '$fieldNight' FROM tblfixedfare)";

我该怎么做？

编辑

此外，对于两个插入，MAX(FixedFareID)+1 的值需要是相同的值。

Answer 1

将 SQL 查询定义更改为:

$this->sql = "
    INSERT INTO tblfixedfare 
        SELECT NULL, MAX(FixedFareID) + 1, '1', :fieldDay
        FROM tblfixedfare
        UNION
        SELECT NULL, MAX(FixedFareID) + 1, '2', :fieldNight
        FROM tblfixedfare
";

...并将 execute() 调用更改为:

$stmt->execute(array(
    'fieldDay' => $fieldDay,
    'fieldNight' => $fieldNight
));