fetchAll() returns empty array, looping with fetch() gets all results

Question

Anyone have any idea why this would happen

$stmt = $this->prepare($this->sql);
$exec = $stmt->execute($this->bindings);
// The below returns an empty array()
return $exec->fetchAll(PDO::FETCH_OBJ);
// This however will return rows if looped.
return $exec->fetch(PDO::FETCH_OBJ);

Even weirder is that this is only happening with certain ID ranges within our database, for example if you search for various information about an item with product id 552, the above code works with fetchAll() no problem, but change that to 553 and it fails (empty array). 554 then works again. There is virtually no difference in the data within the DB between the items (it is all just integers and timestamps [geolocation data]).

add some debugging: check the rowcount for your $stmt after it's executed. if there was an outright failure, fetch/fetchall return boolean false. if there's no results (but a valid result handle), you get an empty array. so most likely fetch/fetchall are working FINE, and it's the query that's somehow not finding anything. — Marc B
– Marc B, Commented Jul 20, 2016 at 16:40
rowCount returns 23, columnCount returns 2. Neither fetch nor fetchAll return false. Executing the raw sql command (emulated from the prepared stmt) works fine in the command line. — varbrad
– varbrad, Commented Jul 20, 2016 at 17:05
Ah, restarted our server and stopped and restarted MySQL and it's working now. Weird issue. — varbrad
– varbrad, Commented Jul 20, 2016 at 17:22

varbrad · Accepted Answer · 2016-07-20 21:56:07Z

Thought i would post back with an answer here as our server hiccuped again earlier this evening. Turns out it was an issue with character encoding between a previous set of item data (I have literally no idea what encoding it was previously in) and the newer utf-8 encoding our item table now uses. PHP was getting hung up on several characters that hadn't converted nicely (A few edge case and odd characters), and PHP was conversely returning an array that was valid, but json_encode could not deal with and would crash the script and silently fail (odd) in both our jQuery client-side implementation and our own error checking php backend (even more odd, more investigation needed there but that's outside the scope of this question).

My solution has been to re-copy the data across to the new encoding directly from the API and item schema, which are natively in utf-8 to begin with so no cross-encoding issues to worry about. Tested with a small subset and the issue finally seems to have been put to bed. :)

mrmert · Accepted Answer · 2021-08-13 08:47:05Z

1

you have to follow this to solve the problem; charset=utf8

your database connection code should be like this

$db = new PDO("mysql:host=localhost;dbname=testdb;charset=utf8", "testuser", "testpass");

answered Aug 13, 2021 at 8:47

mrmert

111 bronze badge

Collectives™ on Stack Overflow

fetchAll() returns empty array, looping with fetch() gets all results

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related