mysql_query successful, but still getting "invalid" warning

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
JeffG
Forum Commoner
Posts: 35
Joined: Wed Jan 30, 2008 1:42 pm
Location: Newbury, UK

mysql_query successful, but still getting "invalid" warning

Post by JeffG »

All responses I have seen so far to problems with the warning "supplied argument is not a valid MySQL result resource" have been to the effect that the query is returning false, and the OP has not tested this first. This is not so in my case.

Here is the code:

Code: Select all

$home = AT_HOME;
$query = "select address_opt_id, area_opt_id from {$db_prefix}address where
        (address_id = \"$from_id\" or address_id = \"$to_id\")
        and address_opt_id != $home";
$rs = mysql_query($query, $conn) or db_error(__FILE__, __LINE__, $query, "");
while ($row = mysql_fetch_assoc($rs))
{
    // (process result set)
}
 
(AT_HOME is a constant, value -2. db_error is a function that displays an error page with the file name, line number, mysql_errno(), mysql_error() and the query, but this is not called in this case since the call to mysql_query() is not returning false.)

This only happens occasionally, but in the most recent case, I had 33 consecutive "supplied argument is not a valid MySQL result resource" warnings in the Apache log file at the call to mysql_fetch_assoc(), implying that 33 records were returned.

This is the address table:

Code: Select all

$query = "CREATE TABLE {$db_prefix}address (
        address_id SMALLINT UNSIGNED AUTO_INCREMENT,
        created DATETIME,
        address1 VARCHAR(30) NOT NULL, address2 VARCHAR(30) NOT NULL,
        town VARCHAR(30) NOT NULL, postcode VARCHAR(8) NOT NULL,
        phone VARCHAR(15) NOT NULL, organisation VARCHAR(30) NOT NULL,
        address_opt_id SMALLINT NOT NULL,
        area_opt_id SMALLINT NOT NULL,
        journeys_this SMALLINT UNSIGNED NOT NULL DEFAULT 0,
        journeys_last SMALLINT UNSIGNED NOT NULL DEFAULT 0,
        journeys_prev SMALLINT UNSIGNED NOT NULL DEFAULT 0,
        last_journey DATE NOT NULL DEFAULT 0,
        FOREIGN KEY(address_opt_id) REFERENCES {$db_prefix}address_options(address_opt_id),
        FOREIGN KEY(area_opt_id) REFERENCES {$db_prefix}area_options(area_opt_id),
        PRIMARY KEY(address_id))";
$rs = mysql_query($query) or dberr(__LINE__);
 
Versions:
Apache 2.2.6
MySQL 5.0.45
PHP 5.2.5
on Windows Vista.

I really don't understand what the problem is. Please can you help?
BomBas
Forum Commoner
Posts: 41
Joined: Wed Mar 04, 2009 1:04 pm

Re: mysql_query successful, but still getting "invalid" warning

Post by BomBas »

Use die( mysql_error() );

Code: Select all

$rs = mysql_query($query, $conn) or die( mysql_error() );
DIE for get rid of the error below.

Now tell us what is your error.
JeffG
Forum Commoner
Posts: 35
Joined: Wed Jan 30, 2008 1:42 pm
Location: Newbury, UK

Re: mysql_query successful, but still getting "invalid" warning

Post by JeffG »

BomBas wrote:Use die( mysql_error() );

Code: Select all

$rs = mysql_query($query, $conn) or die( mysql_error() );
DIE for get rid of the error below.

Now tell us what is your error.
Perhaps you should read my post before making a non-helpful reply. The check is in there already.
(Or even just read the topic title!)

Edit: Sorry, that does come across as a bit aggressive, but I do say quite clearly in my post that the call to mysql_query was successful (so no error), otherwise it wouldn't have got as far as the call to mysql_fetch_assoc. In fact the query is returning at least 33 records.

I'd really appreciate some help with this weird problem, and whether it is PHP-related or MySQL-related.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: mysql_query successful, but still getting "invalid" warning

Post by Benjamin »

I'd like to check if there is an error code. Please put this in your while loop and post the results.

Code: Select all

 
echo "error #" . mysql_errorno($conn) . ' - ' . mysql_error($conn) . "<br />";
 
JeffG
Forum Commoner
Posts: 35
Joined: Wed Jan 30, 2008 1:42 pm
Location: Newbury, UK

Re: mysql_query successful, but still getting "invalid" warning

Post by JeffG »

Just before I do that - I have narrowed the problem down with some trace statements and cross checking the Apache log.

An error in my original post: the query was not returning 33 records - it was being executed 33 times, returning 1 record each time (hard to distinguish in the Apache log alone).

The error is reported when it returns to the top of the while loop for the second time - if I put a break at the end of the while loop there is no error reported.

So it seems that instead of mysql_fetch_assoc just returning false at the start of the second loop, it is also reporting the error.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: mysql_query successful, but still getting "invalid" warning

Post by Benjamin »

There is only one while loop in the code you posted.
JeffG
Forum Commoner
Posts: 35
Joined: Wed Jan 30, 2008 1:42 pm
Location: Newbury, UK

Re: mysql_query successful, but still getting "invalid" warning

Post by JeffG »

Sorry, I meant at the start of the second time around the loop. (There is an outer loop but I didn't think it was relevant here - I can expand on my post if needed).
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: mysql_query successful, but still getting "invalid" warning

Post by Benjamin »

Yeah I would post that because it seems that may be where the problem lies.
JeffG
Forum Commoner
Posts: 35
Joined: Wed Jan 30, 2008 1:42 pm
Location: Newbury, UK

Re: mysql_query successful, but still getting "invalid" warning

Post by JeffG »

astions wrote:I'd like to check if there is an error code. Please put this in your while loop and post the results.

Code: Select all

 
echo "error #" . mysql_errorno($conn) . ' - ' . mysql_error($conn) . "<br />";
 
Eek! You broke my code :D. Changing that to mysql_errno, I get:

Code: Select all

 
error #0 - 
 
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: mysql_query successful, but still getting "invalid" warning

Post by Benjamin »

I always do that with mysql_errno, for some reason I think it's mysql_error with no appended.
JeffG
Forum Commoner
Posts: 35
Joined: Wed Jan 30, 2008 1:42 pm
Location: Newbury, UK

Re: mysql_query successful, but still getting "invalid" warning

Post by JeffG »

OK - the whole shooting match. I warn you, it's huge.

(Edit - not quite so large now, since it was the content of the inner loop that was relevant and causing the problem. I have removed the outer code.)

Code: Select all

 
        if (!$cancelled)
        {
            // Update journey counts and dates, unless cancelled
            // Move select from address to before update address to try and avoid
            // MySQL warning (it doesn't).
            $home = AT_HOME;
            $query = "select address_opt_id, area_opt_id from {$db_prefix}address where
                    (address_id = \"$from_id\" or address_id = \"$to_id\")
                    and address_opt_id != $home";
            echo "<small>$query</small><br>";
            $rs = mysql_query($query, $conn) or
                    db_error(__FILE__, __LINE__, $query, "");
            echo "resource type = ".get_resource_type($rs)."<br>";
            echo "count = ".mysql_num_rows($rs)."<br>";
            while ($row = mysql_fetch_assoc($rs))
            {
echo "error #" . mysql_errno($conn) . ' - ' . mysql_error($conn) . "<br />";
                $address_opt_id = $row['address_opt_id'];
                $area_opt_id = $row['area_opt_id'];
                $query = "update {$db_prefix}address_options set
                        journeys_this = journeys_this +1,
                        last_journey = \"$job_date\"
                        where address_opt_id = \"$address_opt_id\"";
                $rs = mysql_query($query, $conn) or
                        db_error(__FILE__, __LINE__, $query, "");
                $query = "update {$db_prefix}area_options set
                        journeys_this = journeys_this +1,
                        last_journey = \"$job_date\" where
                        area_opt_id = \"$area_opt_id\"";
                $rs = mysql_query($query, $conn) or
                        db_error(__FILE__, __LINE__, $query, "");
            }
            $query = "update {$db_prefix}client set
                    journeys_this = journeys_this +1,
                    last_journey = \"$job_date\" where client_id = \"$client_id\"";
            $rs = mysql_query($query, $conn) or
                    db_error(__FILE__, __LINE__, $query, "");
            $query = "update {$db_prefix}driver set
                    journeys_this = journeys_this +1,
                    last_journey = \"$job_date\" where driver_id = \"$driver_id\"";
            $rs = mysql_query($query, $conn) or
                    db_error(__FILE__, __LINE__, $query, "");
            $home = AT_HOME;
            $query = "update {$db_prefix}address set
                    journeys_this = journeys_this +1,
                    last_journey = \"$job_date\" where
                    (address_id = \"$from_id\" or address_id = \"$to_id\")
                    and address_opt_id != $home";
            $rs = mysql_query($query, $conn) or
                    db_error(__FILE__, __LINE__, $query, "");
        }
 
Edit: the problem is at lines 25 and 31.
Last edited by JeffG on Sat Mar 07, 2009 4:12 pm, edited 1 time in total.
JeffG
Forum Commoner
Posts: 35
Joined: Wed Jan 30, 2008 1:42 pm
Location: Newbury, UK

Re: mysql_query successful, but still getting "invalid" warning

Post by JeffG »

Aaaarrgh! I see it already - I am re-querying inside the loop using the same resource variable. :banghead: :banghead:

(line 64)

The irony is, I didn't even need to assign the result.
Last edited by JeffG on Sat Mar 07, 2009 4:02 pm, edited 1 time in total.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: mysql_query successful, but still getting "invalid" warning

Post by Benjamin »

Cool, now I don't have to look through all the code :)
JeffG
Forum Commoner
Posts: 35
Joined: Wed Jan 30, 2008 1:42 pm
Location: Newbury, UK

Re: mysql_query successful, but still getting "invalid" warning

Post by JeffG »

Thanks for your help! Without this discussion I probably would have never spotted it.

I will also go back and remove the outer code which is not relevant. What was relevant was the content of the loop which I omitted from my original post.
Post Reply