PDO query inconsistency

MySQL database
#1

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:

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

#2

This probably results in multiple result sets. See the PDO nextRowset method to access the data - PHP: PDOStatement::nextRowset - Manual

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

#3

Tried that - no go either.

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

#4

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

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 -

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.

#5

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.

#6

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.

#7

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.

#8

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.

#9

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

#10

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

#11

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?

#12

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.

#13

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

#14

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.

#15

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. 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.

Sponsor our Newsletter | Privacy Policy | Terms of Service