# PDO query inconsistency

**URL:** <https://www.phphelp.com/t/pdo-query-inconsistency/34890>\
**Category:** MySQL database\
**Created:** [July 24, 2022, 12:06am UTC](https://www.phphelp.com/t/pdo-query-inconsistency/34890 "2022-07-24T00:06:51Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![mcg](https://www.phphelp.com/user_avatar/www.phphelp.com/mcg/32/4099_2.png) [@mcg](https://www.phphelp.com/u/mcg)\
**Post date:** [July 24, 2022, 12:06am UTC](https://www.phphelp.com/t/pdo-query-inconsistency/34890/1 "2022-07-24T00:06:51Z")

</div>

## First post, so if this is wrong pls forgive: this has had me baffled for a few days now. I’ve declared a mysql (mariadb actually) database function as follows:

## CREATE FUNCTION f\_is\_collection ( trid INT ) RETURNS INT BEGIN RETURN (SELECT COUNT(\*) FROM assoc\_title\_title WHERE collection\_rid = trid); END;

It’s meant to query an associative table that contains columns ‘collection\_rid’ and ‘content-rid’, where a collection record id (say - a book) maps to its content record ids (say - short stories). It works as expected directly from the database: SELECT f\_is\_collection(1) returns 0, since record id 1 is a short story, while SELECT f\_is\_collection(100) returns 8, specifying that record 100 contains 8 short story titles. Well & good.

When I call this function from php as below:

```auto
function fn_is_collection($trid) {
 global $dbh;
try {
  $qry = "SELECT f_is_collection(?)";
  $stmt = $dbh->prepare($qry);
  $stmt->bindParam(1, $trid);
  $stmt->execute();
  var_dump($stmt->fetch(PDO::FETCH_BOTH));
  var_dump($dbh->errorInfo());
}
catch ... etc
```

I get this:  
bool(false) array(3) { [0]=\> string(5) “00000” [1]=\> NULL [2]=\> NULL } NULL  
I’ve tried fetch(), fetchAll(), fetch(PDO::FETCH\_ATTR), fetch(PDO::FETCH-BOTH) - all to no avail. In desperation, I even converted the db function to a stored procedure: same result.

Any kind help is greatly appreciated.

…Apologies: php 7.4.3, mariadb 10.3.34, xubuntu 20.04.1

---

<div class="post-metadata">

**Author:** ![phdr](https://www.phphelp.com/letter_avatar_proxy/v4/letter/p/91b2a8/32.png) [@phdr](https://www.phphelp.com/u/phdr)\
**Post date:** [July 24, 2022, 12:49am UTC](https://www.phphelp.com/t/pdo-query-inconsistency/34890/2 "2022-07-24T00:49:04Z")

</div>

This probably results in multiple result sets. See the PDO nextRowset method to access the data - [PHP: PDOStatement::nextRowset - Manual](https://www.php.net/manual/en/pdostatement.nextrowset.php)

If that doesn’t work, could you post an sql dump of the table with some sample data so that I/we can experiment.

---

<div class="post-metadata">

**Author:** ![mcg](https://www.phphelp.com/user_avatar/www.phphelp.com/mcg/32/4099_2.png) [@mcg](https://www.phphelp.com/u/mcg)\
**Post date:** [July 24, 2022, 1:29am UTC](https://www.phphelp.com/t/pdo-query-inconsistency/34890/3 "2022-07-24T01:29:50Z")

</div>

Tried that - no go either.

The dump is here:  
[https://drive.google.com/drive/folders/1sD6rc61WTNmuHRBZjHu8T2kUBwraug7Q?usp=sharing](https://drive.google.com/drive/folders/1sD6rc61WTNmuHRBZjHu8T2kUBwraug7Q?usp=sharing)

---

<div class="post-metadata">

**Author:** ![phdr](https://www.phphelp.com/letter_avatar_proxy/v4/letter/p/91b2a8/32.png) [@phdr](https://www.phphelp.com/u/phdr)\
**Post date:** [July 24, 2022, 4:10am UTC](https://www.phphelp.com/t/pdo-query-inconsistency/34890/4 "2022-07-24T04:10:13Z")

</div>

This actually works for me. Calling the php function with an input of 1, gives a zero -

```auto
array(2) {
  ["f_is_collection(?)"]=>
  int(0)
  [0]=>
  int(0)
}
array(3) {
  [0]=>
  string(5) "00000"
  [1]=>
  NULL
  [2]=>
  NULL
}
```

Calling it with a 126 (the start of the data in the sql dump), gives a 4 -

```auto
array(2) {
  ["f_is_collection(?)"]=>
  int(4)
  [0]=>
  int(4)
}
array(3) {
  [0]=>
  string(5) "00000"
  [1]=>
  NULL
  [2]=>
  NULL
}
```

How exactly did you run the query directly against the database?

I can recall a case in the past, where a query for a count(\*) returned a false, rather than a zero, when the table is empty. Check to make sure that whatever database is selected in the php code is the same one where you have data.

I am using php8. If I have time I will try the same with php7.

Also, this may be due to the database server’s strict mode being on. You are using an aggerate function, count(), in a query that doesn’t have a GROUP BY term. If strict mode is on, this results in a query error. I don’t know what affect this will have when the query is inside of a db function/stored procedure.

Edit: are you sure the db function/stored procedure got completely created? You should have needed to change the delimiter from ; to something else, then back again to successfully create the function/stored procedure.

---

<div class="post-metadata">

**Author:** ![phdr](https://www.phphelp.com/letter_avatar_proxy/v4/letter/p/91b2a8/32.png) [@phdr](https://www.phphelp.com/u/phdr)\
**Post date:** [July 24, 2022, 4:27am UTC](https://www.phphelp.com/t/pdo-query-inconsistency/34890/5 "2022-07-24T04:27:02Z")

</div>

Okay. I was able to reproduce this. In php7, if the table doesn’t exist in the currently selected database, you get a false value back, i.e. the result you are getting. In php8, this resulted in an actual PDO/sql error - Base table or view not found: 1146 Table ‘my\_db.assoc\_title\_title’ doesn’t exist.

This difference either has to do with how the different versions of the PDO extension are configured or how they deal with errors/warnings sent back from the database server.

In any case, it looks like the database you are selecting in the php code doesn’t have a assoc\_title\_title table in it.

---

<div class="post-metadata">

**Author:** ![mcg](https://www.phphelp.com/user_avatar/www.phphelp.com/mcg/32/4099_2.png) [@mcg](https://www.phphelp.com/u/mcg)\
**Post date:** [July 24, 2022, 6:27pm UTC](https://www.phphelp.com/t/pdo-query-inconsistency/34890/6 "2022-07-24T18:27:39Z")

</div>

Many thanks for playing with this. I understand what you’re saying, but if I “plug” the value, a later query later down the same page retrieves all content records with no issue: the page first pulls the “collection” data, then displays its “contents” by pulling the content\_id records from the same table:  
PHP  
…  
$coll = fn\_is\_collection($rid);// the $rid comes from an HTML $\_POST  
…  
if ($coll) {  
$cont = fn\_title\_contents($rid); // this works fine, calls the sp below  
…  
SQL  
CREATE PROCEDURE p\_title\_contents  
(  
trid INT  
)  
BEGIN  
SELECT att.content\_rid AS rid,  
att.sequence\_a AS sa,  
…  
FROM assoc\_title\_title att  
INNER JOIN titles t  
ON t.rid = att.content\_rid  
WHERE att.collection\_rid = trid  
…etc

The $dbh is a persistent PDO called from a library: all web pages begin with:

\<?php include\_once('baroque.php'); ?\>

The baroque.php library itself begins:  
…//initialize other variables  
$db\_pdo = ‘mysql:host=localhost;dbname=baroquenotes’;  
$db\_user = [username];  
$db\_pass = [password’];  
$dbh = new PDO($db\_pdo, $db\_usr, $db\_pass, array(PDO::ATTR\_PERSISTENT =\> true));  
… continue initializing  
… declare functions

Calling the db procedure or selecting the function from phpMyAdmin or directly from the command line, or from a GUI client like beekeeper studio oddly works just as expected.

There are a couple of other things that have occurred to me to try: I’ll let you know how it goes.

---

<div class="post-metadata">

**Author:** ![phdr](https://www.phphelp.com/letter_avatar_proxy/v4/letter/p/91b2a8/32.png) [@phdr](https://www.phphelp.com/u/phdr)\
**Post date:** [July 24, 2022, 6:39pm UTC](https://www.phphelp.com/t/pdo-query-inconsistency/34890/7 "2022-07-24T18:39:58Z")

</div>

The query for fn\_title\_contents doesn’t (apparently) use an aggerate function. I suspect that the incorrect result is is due to the database server’s strict mode being ON and the count(\*) query is failing, without a GROUP BY. For the case where you executed the query directly against the database server, a tool like phpmyadmin may (unverified) be setting strict mode to off, allowing it to work.

---

<div class="post-metadata">

**Author:** ![mcg](https://www.phphelp.com/user_avatar/www.phphelp.com/mcg/32/4099_2.png) [@mcg](https://www.phphelp.com/u/mcg)\
**Post date:** [July 24, 2022, 6:50pm UTC](https://www.phphelp.com/t/pdo-query-inconsistency/34890/8 "2022-07-24T18:50:52Z")

</div>

Understood - I’ll keep playing with this, and I’ll check strict mode. I’ve tried  
(1) altering the db procedure to return the actual content records themselves so I can just count(the\_array) → doesn’t work  
(2) prefixing the function call with the db name [$qry = “CALL baroquenotes.p\_title\_is\_collection(?)”] → also doesn’t work  
(3) calling the later procedure fn\_title\_contents($rid) and just counting the results first → now this promptly breaks

At this point, I’m beginning to think maybe I’m looking in the wrong place. Will keep you posted, and thanks again.

---

<div class="post-metadata">

**Author:** ![phdr](https://www.phphelp.com/letter_avatar_proxy/v4/letter/p/91b2a8/32.png) [@phdr](https://www.phphelp.com/u/phdr)\
**Post date:** [July 24, 2022, 6:55pm UTC](https://www.phphelp.com/t/pdo-query-inconsistency/34890/9 "2022-07-24T18:55:47Z")

</div>

> [@mcg](#):
>
> SELECT COUNT(\*) FROM assoc\_title\_title WHERE collection\_rid = trid

You can just run this query from php and see if it produces an error. If so, add **GROUP BY collection\_rid**

---

<div class="post-metadata">

**Author:** ![mcg](https://www.phphelp.com/user_avatar/www.phphelp.com/mcg/32/4099_2.png) [@mcg](https://www.phphelp.com/u/mcg)\
**Post date:** [July 24, 2022, 7:04pm UTC](https://www.phphelp.com/t/pdo-query-inconsistency/34890/10 "2022-07-24T19:04:57Z")

</div>

Same, with or without the GROUP BY : bool(false)

---

<div class="post-metadata">

**Author:** ![phdr](https://www.phphelp.com/letter_avatar_proxy/v4/letter/p/91b2a8/32.png) [@phdr](https://www.phphelp.com/u/phdr)\
**Post date:** [July 24, 2022, 7:18pm UTC](https://www.phphelp.com/t/pdo-query-inconsistency/34890/11 "2022-07-24T19:18:25Z")

</div>

I just looked through the PDO defined constants/settings and didn’t see anything that had to do with how database errors were treated.

Since I did get database server error information when using php8, can you temporarily or permanently switch to php8?

---

<div class="post-metadata">

**Author:** ![mcg](https://www.phphelp.com/user_avatar/www.phphelp.com/mcg/32/4099_2.png) [@mcg](https://www.phphelp.com/u/mcg)\
**Post date:** [July 24, 2022, 7:23pm UTC](https://www.phphelp.com/t/pdo-query-inconsistency/34890/12 "2022-07-24T19:23:11Z")

</div>

So this works, I’ve no idea why:  
(1) Altered the php function to query directly instead of invoking a db sp or fn:  
$qry = “SELECT \* FROM assoc\_title\_title WHERE collection\_rid = ?”;  
…  
$data = $stmt-\>fetchAll(PDO::FETCH\_ASSOC);  
return count($data);  
which is chatty, but not the end of the world since the db server is on the same box as the web server.  
(2) On the calling web page, I have to execute fn\_title\_contents($rid) FIRST, then call fn\_is\_collection($rid). I then have to call fn\_title\_contents($rid) AGAIN to get any results:

$cont = fn\_title\_contents($rid);  
$coll = fn\_is\_collection($rid);  
… show entries pertaining to the collection title  
…  
if ($coll) {  
$cont = fn\_title\_contents($rid);  
… loop to show data about each of the content records

If I omit either call to fn\_title\_contents($rid), I get nada. And if I try to count($data) from the first call to fn\_title\_contents($rid), also nada.  
So this will do as a work-around for the time being. I’m now going to get drunk, and will return to this when I sober up.

---

<div class="post-metadata">

**Author:** ![mcg](https://www.phphelp.com/user_avatar/www.phphelp.com/mcg/32/4099_2.png) [@mcg](https://www.phphelp.com/u/mcg)\
**Post date:** [July 24, 2022, 7:24pm UTC](https://www.phphelp.com/t/pdo-query-inconsistency/34890/13 "2022-07-24T19:24:59Z")

</div>

I can try putting 8 on another box and see what happens. Definitely worth a try.

---

<div class="post-metadata">

**Author:** ![mcg](https://www.phphelp.com/user_avatar/www.phphelp.com/mcg/32/4099_2.png) [@mcg](https://www.phphelp.com/u/mcg)\
**Post date:** [July 25, 2022, 4:46pm UTC](https://www.phphelp.com/t/pdo-query-inconsistency/34890/14 "2022-07-25T16:46:20Z")

</div>

Just as an FYI, I’ve since found other anomalies: the sequence\_c and sequence\_d fields don’t come down properly either. Same deal, they pull fine directly from SQL, but not PHP.

I’m just in the process of downloading server 22.04 to install higher versions of db and php.

---

<div class="post-metadata">

**Author:** ![mcg](https://www.phphelp.com/user_avatar/www.phphelp.com/mcg/32/4099_2.png) [@mcg](https://www.phphelp.com/u/mcg)\
**Post date:** [July 27, 2022, 1:35am UTC](https://www.phphelp.com/t/pdo-query-inconsistency/34890/15 "2022-07-27T01:35:08Z")

</div>

Update & resolved: After installing ubuntu srvr 22.04, php8.1 and mariadb 10.3.34, PDO finally said “2014 Cannot execute queries while there are pending result sets.” Irritating to say the least, since the same issue has popped in other places, meaning to be safe I expect I’ll have to $stmt-\>closeCursor() after each and every call. This is apparently documented at [PHP :: Bug #79872 :: Can't execute query with pending result sets](https://bugs.php.net/bug.php?id=79872). On the other hand, random fields (sequence\_c, sequence\_d) now show up properly instead of following the white rabbit.  
Tks to phdr for pointing me in the right direction.