Page 1 of 1
mysql_query successful, but still getting "invalid" warning
Posted: Sat Mar 07, 2009 6:04 am
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?
Re: mysql_query successful, but still getting "invalid" warning
Posted: Sat Mar 07, 2009 7:22 am
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.
Re: mysql_query successful, but still getting "invalid" warning
Posted: Sat Mar 07, 2009 7:45 am
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.
Re: mysql_query successful, but still getting "invalid" warning
Posted: Sat Mar 07, 2009 3:14 pm
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 />";
Re: mysql_query successful, but still getting "invalid" warning
Posted: Sat Mar 07, 2009 3:26 pm
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.
Re: mysql_query successful, but still getting "invalid" warning
Posted: Sat Mar 07, 2009 3:28 pm
by Benjamin
There is only one while loop in the code you posted.
Re: mysql_query successful, but still getting "invalid" warning
Posted: Sat Mar 07, 2009 3:30 pm
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).
Re: mysql_query successful, but still getting "invalid" warning
Posted: Sat Mar 07, 2009 3:34 pm
by Benjamin
Yeah I would post that because it seems that may be where the problem lies.
Re: mysql_query successful, but still getting "invalid" warning
Posted: Sat Mar 07, 2009 3:37 pm
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

. Changing that to mysql_errno, I get:
Re: mysql_query successful, but still getting "invalid" warning
Posted: Sat Mar 07, 2009 3:43 pm
by Benjamin
I always do that with mysql_errno, for some reason I think it's mysql_error with no appended.
Re: mysql_query successful, but still getting "invalid" warning
Posted: Sat Mar 07, 2009 3:47 pm
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.
Re: mysql_query successful, but still getting "invalid" warning
Posted: Sat Mar 07, 2009 3:52 pm
by JeffG
Aaaarrgh! I see it already - I am re-querying inside the loop using the same resource variable.
(line 64)
The irony is, I didn't even need to assign the result.
Re: mysql_query successful, but still getting "invalid" warning
Posted: Sat Mar 07, 2009 4:01 pm
by Benjamin
Cool, now I don't have to look through all the code

Re: mysql_query successful, but still getting "invalid" warning
Posted: Sat Mar 07, 2009 4:04 pm
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.