PDO equivalent of mysql_num_rows or mssql_num_rows

问题:

I know this question has been asked before but it seems like the solutions have been specific to the problem presented.

I have a codebase with hundreds of instances where mssql_num_rows is used.

Code example:

$db->execute($sql);

if ($db->getRowsAffected() > 0) {
    $total = $db->fetch();

In db class:

$this->rowsaffected = mssql_num_rows($this->query_result);

• I can't create generic SELECT count(*) FROM table queries as I have too many specific select statements.
• I could run a preg_replace to remove everything between SELECT and FROM and then replace with a COUNT(*) and run a second query but this assumes all queries are setup a certain way.
• I could fetchAll first then count() the results but that means upgrading all instances of the if statements.

So what is the best all around replacement to a *_num_rows function if people are updating their code to PDO. Not something that solves a specific problem, but something that replaces the functionality of *_num_rows. If that's not possible what allowed it to be possible before?

回答1:

If you want to count the rows you can do this with PDO:

$sql = 'select * from users';
$data = $conn->query($sql);
$rows = $data->fetchAll();
$num_rows = count($rows);

There is no way to directly count rows when using a SELECT statement with PDO.

回答2:

So with everyone's help this is what I built.

function getRowsAffected() {

    $rawStatement = explode(" ", $this->query);
    $statement = strtoupper($rawStatement[0]);

    if ($statement == 'SELECT' || $statement == 'SHOW') {

        $countQuery = preg_replace('/(SELECT|SHOW)(.*)FROM/i', "SELECT count(*) FROM", $this->query);
        $countsth = $this->pdo->prepare($countQuery);

        if ($countsth->execute()) {
            $this->rowsaffected = $countsth->fetchColumn();
        } else {
            $this->rowsaffected = 0;
        }
    }

    return $this->rowsaffected;
}

$this->rowsaffected is already being updated in the execute phase if the statement is an INSERT, UPDATE, or DELETE with $sth->rowCount() so I only needed to run this second query on SELECT and SHOWS.

if ($statement == 'INSERT' ||  $statement == 'UPDATE' || $statement == 'DELETE') {
    $this->rowsaffected = $this->sth->rowCount();
}

I feel bad though, because just as I mentioned in my question, that I was looking for an overall solution I seem to have stumbled onto a specific solution that works for me since the code already asks for the number of rows using a function. If the code was doing this:

if (mysql_num_rows($result) > 0) {

then this solution would still create work updating all instances to use that custom function.