php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #16295 # is not a valid resource
Submitted: 2002-03-26 18:38 UTC Modified: 2002-04-01 15:49 UTC
From: joyeux at 1984 dot cz Assigned:
Status: Not a bug Package: MySQL related
PHP Version: 4.1.2 OS: MDK 8.2
Private report: No CVE-ID: None
 [2002-03-26 18:38 UTC] joyeux at 1984 dot cz
This SOMETIMES produces the error 
"3 is not a valid mysql resource on line 2"
(or other numbers)

First fetch_array is usually good. It looks like php forgot a resource link when calling other query.

1| $sql=mysql_query("SELECT id FROM tbl");
2| while($row = mysql_fetch_array($sql, MYSQL_ASSOC)) {
3|    mysql_unbuffered_query("DELETE FROM tbl2 WHERE 
4|    id=".$row["id"]);
5| }


When I use an array to store data from tbl1 instead direct fetching, it works.

1| $ARR=array();
2| $sql=mysql_query("SELECT id FROM tbl");
3| while($row=mysql_fetch_array($sql, MYSQL_ASSOC)) $ARR[]=$row;
4| foreach($ARR as $row) mysql_unbuffered_query("DELETE FROM tbl2 WHERE id=".$row["id"]);


I can't find where is the problem. I try to put some sleep() after mysql_query but it's the same.

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2002-03-27 05:28 UTC] joyeux at 1984 dot cz
This SOMETIMES produces an error 
"3 is not a valid mysql resource on line 2"
(or other error numbers)

First fetch_array is usually good. It looks like php forget a resource link when calling other query (line 3).

1| $sql=mysql_query("SELECT id FROM tbl");
2| while($row = mysql_fetch_array($sql, MYSQL_ASSOC)) {
3|    mysql_unbuffered_query("DELETE FROM tbl2 WHERE id=".$row["id"]);
5| }


When I first fetch all data to an array and then use them by the same way, it works.


1| $ARR=array();
2| $sql=mysql_query("SELECT id FROM tbl");
3| while($row=mysql_fetch_array($sql, MYSQL_ASSOC)) $ARR[]=$row;
4| foreach($ARR as $row) mysql_unbuffered_query("DELETE FROM tbl2 WHERE id=".$row["id"]);

I can't find where is the problem. I try to put some sleep() after queries but nothing happens.
 [2002-03-27 06:15 UTC] georg@php.net
Don't use mysql_unbuffered_query and mysql_query mixed.
mysql_unbuffered_query is for fetching rows in SELECT, 
SHOW or DESCRIBE statements without buffering it.

Use mysql_query ("DELETE ... instead.

 [2002-03-27 06:16 UTC] georg@php.net
Don't mix mysql_unbuffered_query and mysql_query.
mysql_unbuffered_query is for fetching rows in SELECT, 
SHOW or DESCRIBE statements without buffering it.

Use mysql_query ("DELETE ... instead.

 [2002-03-27 09:30 UTC] joyeux at 1984 dot cz
I try to put mysql_query everywhere (there are no mysql_unbuffered_queryies)

Still the same.

What's important? mysql_errno is always null and the error occures in various resources.

Result of faulty operation in a database is sometimes ok and sometimes not.
 [2002-03-27 10:53 UTC] jtate@php.net
I don't think this is a PHP problem.  Try this:
Modify your script so that it looks like:
1| $sql=mysql_query("SELECT id FROM tbl");
2| while($row = mysql_fetch_array($sql, MYSQL_ASSOC)) {
3|    mysql_unbuffered_query("DELETE FROM tbl2 WHERE 
4|    id=\"".$row["id"]."\"");
5| }

If this works, change this bug to bogus.

 [2002-03-27 10:53 UTC] joyeux at 1984 dot cz
I more specify my problem:

There are two identical blocks like this


1| $sql=mysql_query("SELECT id FROM tbl");
2| while($row = mysql_fetch_array($sql, MYSQL_ASSOC)) {
3|    mysql_query("DELETE FROM tbl2 WHERE 
4|    id=".$row["id"]);
5| }

8| $sql=mysql_query("SELECT id FROM tbl");
9| while($row = mysql_fetch_array($sql, MYSQL_ASSOC)) {
10|    mysql_query("DELETE FROM tbl2 WHERE 
11|   id=".$row["id"]);
12| }


fetching array on line 8 writes "# is not a valid mysql result resource"

But when I put whatever query between these blocks, invalid resource from the second block disappears and the query works good as well.

It looks like I can't use DELETE query when selecting


So, this is ok:

1| $sql=mysql_query("SELECT id FROM tbl");
2| while($row = mysql_fetch_array($sql, MYSQL_ASSOC)) {
3|    mysql_query("DELETE FROM tbl2 WHERE 
4|    id=".$row["id"]);
5| }

6| $sql=mysql_query("SELECT id FROM tbl"); //this does nothing but prevent error from next block
7| $row = mysql_fetch_array($sql, MYSQL_ASSOC);

8| $sql=mysql_query("SELECT id FROM tbl");
8| while($row = mysql_fetch_array($sql, MYSQL_ASSOC)) {
9|    mysql_unbuffered_query("DELETE FROM tbl2 WHERE 
10|   id=".$row["id"]);
11| }
 [2002-04-01 15:49 UTC] sniper@php.net
This is not a bug. You should open two separate connections
to the database and use the link identifier in mysql_query().

Also, this is not very efficient. It would be better to
first fetch the whole array containing the id's and then
make a comma separated list of them and use 'IN()' function
in the query.

ie. "DELETE FROM tbl2 WHERE id IN($list_of_ids)"

 
PHP Copyright © 2001-2022 The PHP Group
All rights reserved.
Last updated: Thu Aug 11 17:05:44 2022 UTC