Asked  7 Months ago    Answers:  5   Viewed   21 times

PDO apparently has no means to count the number of rows returned from a select query (mysqli has the num_rows variable).

Is there a way to do this, short of using count($results->fetchAll()) ?

 Answers

52

According to the manual, there is a PDOStatement->rowCount method ; but it shouldn't be used (quoting) :

For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement.
Instead, use PDO::query() to issue a SELECT COUNT(*) statement with the same predicates as your intended SELECT statement, then use PDOStatement::fetchColumn() to retrieve the number of rows that will be returned.
Your application can then perform the correct action.


If you already have a recordset, and want to know how many lines are in it, you'll have to fetch the data, using one of the fetch* methods ; and use count -- like you suggested.

Wednesday, March 31, 2021
 
MannfromReno
answered 7 Months ago
64

It seems that PDO::MYSQL_ATTR_FOUND_ROWS is a mysql connection option. Thus, it works only as PDO connection option as well. So, set it up this way

$opt  = array(
    PDO::MYSQL_ATTR_FOUND_ROWS   => TRUE,
    // you may wish to set other options as well
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
);
$this->_db = new PDO($dsn,DB_USER,DB_PASS,$opt);
Wednesday, March 31, 2021
 
shivam
answered 7 Months ago
69

Possible Bug: http://www.php.net/manual/en/mysqli-result.num-rows.php#104630

Code is from source above (Johan Abildskov):

$sql = "valid select statement that yields results"; 
if($result = mysqli-connection->query($sql, MYSQLI_USE_RESULT)) 
{ 
          echo $result->num_rows; //zero 
          while($row = $result->fetch_row()) 
        { 
          echo $result->num_rows; //incrementing by one each time 
        } 
          echo $result->num_rows; // Finally the total count 
}

Could also validate with the Procedural style:

/* determine number of rows result set */
$row_cnt = mysqli_num_rows($result);
Wednesday, March 31, 2021
 
dirigibleplum
answered 7 Months ago
68

The solution is to ensure that you are using the mysqlnd driver for php.

How do you know that you're not using mysqlnd?

When viewing php -i, there will be no mention of "mysqlnd". The pdo_mysql section will have something like this:

pdo_mysql

PDO Driver for MySQL => enabled Client API version => 5.1.72

How do you install it?

Most installation guides for L/A/M/P suggest apt-get install php5-mysql but the native driver for MySQL is installed by a different package: php5-mysqlnd. I found that this was available with the ppa:ondrej/php5-oldstable.

To switch to the new driver (on Ubuntu):

  • Remove the old driver:
    apt-get remove php5-mysql
  • Install the new driver:
    apt-get install php5-mysqlnd
  • Restart apache2:
    service apache2 restart

How do I check that the driver is being used?

Now php -i will mention "mysqlnd" explicitly in the pdo_mysql section:

pdo_mysql

PDO Driver for MySQL => enabled
Client API version => mysqlnd 5.0.10 - 20111026 - $Id:      e707c415db32080b3752b232487a435ee0372157 $

PDO settings

Ensure that PDO::ATTR_EMULATE_PREPARES is false (check your defaults or set it):
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

Ensure that PDO::ATTR_STRINGIFY_FETCHES is false (check your defaults or set it):
$pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);

Returned values

  • Floating-point types (FLOAT, DOUBLE) are returned as PHP floats.
  • Integer types (INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT †) are returned as PHP integers.
  • Fixed-Point types (DECIMAL, NUMERIC) are returned as strings.

† BIGINTs with a value greater than a 64 bit signed int (9223372036854775807) will return as a string (or 32 bits on a 32 bit system)

    object(stdClass)[915]
      public 'integer_col' => int 1
      public 'double_col' => float 1.55
      public 'float_col' => float 1.5
      public 'decimal_col' => string '1.20' (length=4)
      public 'bigint_col' => string '18446744073709551615' (length=20)
Tuesday, June 8, 2021
 
SkyNet
answered 5 Months ago
29

Your PDO is configured to emulate prepared queries, whereas mysqli is using true prepared queries.

The prepared query binds the string ''1'' as an integer parameter value. PHP coerces it to an integer using something like intval(). Any string with non-numeric leading characters is interpreted as 0 by PHP, so the parameter value sent after prepare is the value 0.

The fake prepared query uses string interpolation (instead of binding) to add the string ''1'' into the SQL query before MySQL parses it. But the result is similar, because SQL also treats a string with non-numeric leading characters in an integer context as the value 0.

The only difference is what ends up in the general query log when the parameter is bound before prepare versus after prepare.

You can also make PDO use real prepared queries, so it should act just like mysqli in this case:

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

PS: This may demonstrate a good reason why it's customary to start id values at 1 instead of 0.

Wednesday, August 18, 2021
 
derobert
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share