CODEWITHSUNDEEP
phppdo
mpen
mpen

Reputation: 282895

Can I use PDO to run multiple SQL queries and retrieve the number of rows affected for each statement?

Here's what I've got so far:

/**
 * @param PDO $db
 * @param string $file
 */
function run_sql_file($db, $file) {
    $sql = file_get_contents($file);
    try {
        $affected = $db->exec($sql);
        println("$affected row(s) affected");
    } catch(PDOException $e) {
        exitmsg($e->getMessage());
    }
}

It does seem to run all the queries, but it always give me back "0 rows affected". Is there a way to get the number of rows affected for each statement? Preferably along with the associated query, and any error messages and warnings.

Upvotes: 4

Views: 1064

Answers (3)

Galileo_Galilei
Galileo_Galilei

Reputation: 127

I met the same issue, for example I am reading a series of mysql drop table sqls from a file and I want to execute them into a single PDO->exec():

    $dbh = new PDO('mysql:host=test;dbname=db1', 'user', 'pass');
  try {

    $dbh->exec('drop table test_table1;drop table test_table2;');
  }
catch(PDOException $e)
{
    echo $e->getMessage();
    die();
}

If the first drop sql is successful then no Exception is rised even if the second one failed(that table name doesn't exist anymore). It seems there is no way you can check wether all of the sqls were executed or not with PDO_MYSQLND; only if the first one fails then a PDO Exception is rised.

Upvotes: 3

D-Rock
D-Rock

Reputation: 2676

making the assumption that you don't have any crazy SQL that will blow up the explode statement...

/**
 * @param PDO $db
 * @param string $file
 */
function run_sql_file($db, $file) {
    $sql = file_get_contents($file);
    $sql_lines = explode(';',$sql);
    try {
        foreach($sql_lines as $line) {
            $affected += $db->exec($line);
        }
        println("$affected row(s) affected");
    } catch(PDOException $e) {
        exitmsg($e->getMessage());
    }
}

Upvotes: -1

cam
cam

Reputation: 798

Try setting the count variable after the execution:

$db->exec($sql);

$affected = $db->rowCount();

Upvotes: 0

Related Questions