MC Emperor MC Emperor - 2 months ago 17
PHP Question

How to get the type of a query statement in PDO?

In the MySQL Reference Manual, there's distinction between data definition statements and data manipulation statements.

Now I want to know if a query inserts a database record, updates one, deletes one or modifies the table structure and so on, or, more precisely, the exact number of affected rows, but only if it is applicable.

For example, the statement

SELECT *
FROM SomeTable
WHERE id=1 OR id=2


returns a number of affected rows (in this case 2), but with the
SELECT
statement, there's nothing modified in the database, so that number would be 0.

How to get the type of query?

Answer

I was looking for the same answer and stumbled across this article. It was last updated in August. In it, there is a section: "Determining the Type of a Statement" You basically can make the following assumptions: (copied from the article)

  • If columnCount() is zero, the statement did not produce a result set. Instead, it modified rows and you can invoke rowCount() to determine the number of affected rows.
  • If columnCount() is greater than zero, the statement produced a result set and you can fetch the rows. To determine how many rows there are, count them as you fetch them.

I'll save you the trouble and just paste the code sample here

$sth = $dbh->prepare ($stmt);
$sth->execute ();
if ($sth->columnCount () == 0)
{
    # there is no result set, so the statement modifies rows
     printf ("Number of rows affected: %d\n", $sth->rowCount ());
}
else
{
    # there is a result set
    printf ("Number of columns in result set: %d\n", $sth->columnCount ());
    $count = 0;
    while ($row = $sth->fetch (PDO::FETCH_NUM))
    {
    # display column values separated by commas
       print (join (", ", $row) . "\n");
       $count++;
    }
}
Comments