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

Question

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 1

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++;
    }
}

Answer 2

I have been thinking of the same issue, and come to conclusion that I don't need no automation in this matter.

The only use for such an auto-detect is some magic function which will return number of affected rows. But such a magic, although adding a little sugar to the syntax, always makes code support a nightmare:
When you're calling a function, and it can return values of different types depends on the context, you cannot tell which one is returned at every particular moment. So, it makes debugging harder.

So, for sake of readability, just call appropriate function to get the result you need at the moment - affectedRows or numRows. It won't make your code bloated, but make it a lot readable.

Answer 3

I'm using this:

substr($statement->queryString, 0, strpos($statement->queryString, ' '));

where $statement is a PDOStatement object, a few things to note here are that you should verify before using this that $statement is a PDOStatement object, also we should probably take the strpos out of the substr statement in case strpos returns false, which would probably cause an error, finally, this only works with one word statement types, like SELECT, INSERT, etc and not multi-word statement types like ALTER TABLE