0

I have a simple piece of code:

$sql = "SELECT * FROM users";
$stmt = $this->conn->prepare($sql);
$stmt->execute();
echo $stmt->rowCount();

This outputs 0 when run with PHP7.
However, this is the wrong/unexpected result.
The expected result is 15.
If I run the above code in PHP8 I get the expected result.

If I duplicate the line $stmt = $this->conn->prepare($sql);, as in the code below, I then get the correct value in PHP7.

$sql = "SELECT * FROM users";
$stmt = $this->conn->prepare($sql);
$stmt = $this->conn->prepare($sql);
$stmt->execute();
echo $stmt->rowCount();

My connection is established in the following way:

$this->conn = new PDO('mysql:host=' . DB_SERVER . ';dbname=' . DB_NAME . ';charset=utf8',
    DB_USER, DB_PASS,
    [PDO::MYSQL_ATTR_INIT_COMMAND =>"SET NAMES utf8;SET time_zone = '" . TIMEZONE . "'"]);

Clearly something is wrong as the second prepare shouldn't be necessary, but I don't know where to start.
Perhaps I'm missing something obvious?

PHP versions I'm using specifically:
PHP 7.2.34
PHP 8.0.8

Clarification

I would like the user rows returned (not the number - that's just to show the problem) - but with PHP7 I get no rows, with PHP8 I get all the requested/existing rows.

Clarification #2

Yes, sorry, I was trying to provide a minimal working example. I do fetch and iterate through the users. For simplicity though, I'm currently just checking the number of rows/users returned:

$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo count($users);

The use of rowCount() was meant to simplify the issue.

The MySQL driver versions are:
mysql (mysqlnd 5.0.12-dev - 20150407
mysql (mysqlnd 8.0.13)

Clarification #3

$stmt->execute(); does return false. My bad - sorry.

However, looking at this question, the output of print_r($stmt->errorInfo()); is:

Array
(
    [0] => 00000
    [1] => 
    [2] => 
)

I've added the following to my DB connection but I'm not getting any more information than the above.

$this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Clarification 4

False trail as "$pdo->errorInfo() refers to the last statement that was successfully executed" https://stackoverflow.com/a/12438685/1489538.

Cyrille
  • 3,427
  • 1
  • 30
  • 32
  • 1
    The backend (mysqlnd vs libmysql) or sql emulation state (and MYSQL_ATTR_FOUND_ROWS) are probably more relevant than version number superstition. – mario Dec 02 '21 at 10:08
  • This might depend on the database driver version, I suppose? The manual has some user comments about similar issues, like this one, https://www.php.net/manual/en/pdostatement.rowcount.php#113608, _"In some drivers rowCount() only works when using the prepare() with PDO::CURSOR_SCROLL"_ - maybe a second `prepare` call has a similar effect, dunno. – CBroe Dec 02 '21 at 10:08
  • See I get the number of rows returned with only one prepare using 7.2.34 and 8.0.13 – RiggsFolly Dec 02 '21 at 10:12
  • Does `$stmt->execute()` return false when you get no rows? – Salman A Dec 02 '21 at 10:39
  • @SalmanA Yes, yes it does return false. Thanks for suggesting this. I've added clarifications to the post as the error code is not helpful. – Cyrille Dec 02 '21 at 11:11
  • This may be the path to explore for me: https://stackoverflow.com/questions/11813911/php-pdo-error-number-00000-when-query-is-correct – Cyrille Dec 02 '21 at 11:37
  • 1
    Please reproduce the problem on https://phpize.online/ because we need to know the exact steps that need to be taken to reproduce this. – Dharman Dec 05 '21 at 11:09

0 Answers0