php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #43586 Unable to query again after foreach on MySQL
Submitted: 2007-12-13 07:08 UTC Modified: 2008-01-20 17:10 UTC
Votes:2
Avg. Score:3.0 ± 0.0
Reproduced:1 of 2 (50.0%)
Same Version:0 (0.0%)
Same OS:0 (0.0%)
From: vicrry at yahoo dot com dot hk Assigned:
Status: Not a bug Package: PDO related
PHP Version: 5.2.5 OS: Linux
Private report: No CVE-ID: None
View Developer Edit
 [2007-12-13 07:08 UTC] vicrry at yahoo dot com dot hk 
Description:
------------
It seems the cursor didn't close after iterated through with foreach statement, but I don't insist any other causes.

I don't know if it is expected, but it really cause inconvenience and reduce readability to add a fetchAll() line after every query.

Reproduce code:
---------------
$Pdo = new PDO('mysql:host=localhost,dbname=test');

$res=$Pdo->query('SELECT * FROM TEST_TABLE');
foreach($res as $row) print_r($row);

$res=$Pdo->query('SELECT * FROM TEST_TABLE2');
foreach($res as $row) print_r($row);

Expected result:
----------------
Prints out each row in table 'TEST_TABLE' and 'TEST_TABLE2'.

Actual result:
--------------
The first query seems works fine, though it might have error info left unprinted.

The second query doesn't work at all, and the query() statement didn't even report an error.

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2007-12-18 15:17 UTC] msaraujo@php.net 
Hello,

Can you please send me your SQL definition for TEST_TABLE and TEST_TABLE2?
 [2007-12-21 12:52 UTC] vicrry at yahoo dot com dot hk 
I believe the following definition will surely cause what I mentioned.

-- ------------------------------------ --
TEST_TABLE ( id int(12) PRIMARY KEY );
TEST_TABLE2 ( id int(12) PRIMARY KEY );
-- ------------------------------------ --

In fact it occurs on every foreach I used on any table in my database.
 [2008-01-04 06:49 UTC] vicrry at yahoo dot com dot hk 
I found a simple solve for this error.

Unset the PDOStatement object each time before your query again with the same variable.

Reproduce code:
---------------
$Pdo = new PDO('mysql:host=localhost,dbname=test');

$res=$Pdo->query('SELECT * FROM TEST_TABLE');
foreach($res as $row) print_r($row);

unset($res);

$res=$Pdo->query('SELECT * FROM TEST_TABLE2');
foreach($res as $row) print_r($row);
---------------

But this seems to be a bug anyway, it DO causes inconveniences.
 [2008-01-04 07:08 UTC] vicrry at yahoo dot com dot hk 
Another potential cause for this bug is the MySql specific attribute 
"PDO::MYSQL_ATTR_USE_BUFFERED_QUERY".

I found it totally solved when I create my PDO object with this attribute set to true.

The shared buffer in the PDOStatement object is probably not released after foreach statements.

Not sure memory leaks occurs or not.
 [2008-01-20 17:10 UTC] msaraujo@php.net 
This is not a bug.
 
PHP Copyright © 2001-2025 The PHP Group
All rights reserved. 		Last updated: Sat Oct 25 11:00:01 2025 UTC