Why am I getting this MYSQL error in PHP script when the query is working perfectly?

jwshome
jwshome used Ask the Experts™
on
I'm definitely not an expert, and I cannot figure out why this is happening.  When I run a particular script I get the following error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/dev1/public_html/scheduler_forced.php on line 47

Normally, I would check the mysql_error(), find where I had messed up, and fix the problem.  But in this case the query proceeds from the point of error, and works perfectly, and mysql_error returns a blank.

	$db = mysql_connect($mysql_host,$mysql_user,$mysql_password);
	mysql_select_db($mysql_database,$db);

	$sql = "INSERT INTO cronlog (runtime, ip, daily, weekly, comment) VALUES ('$runtime', '$ip', '', '', 'Forced scheduler')";
	$result = mysql_query($sql,$db);

	// loop through the towns and prepare summary
	$sql = "SELECT townID, dailySummaryTime, weeklySummaryDay, weeklySummaryTime FROM towns";
	$result = mysql_query($sql, $db);

	while ($row = mysql_fetch_array($result))
		{
		$townNumber = $row['townID'];
		$dailySummaryTime = $row['dailySummaryTime'];
		$weeklySummaryDay = $row['weeklySummaryDay'];
		$weeklySummaryTime = $row['weeklySummaryTime'];

... and the code continues on from here...

Open in new window


The error occurs in the 'while' command in line 11 above (that's line 47 in my actual script).

I cannot figure out why I am getting an error.  The connection to the database is identical code that I use literally hundreds of times on this site.  The query on lines 4 and 5 works properly.  The query on lines 8, 9 and 11 works fine also.  mysql_num_rows returns 1 (which is correct), and the variables $townNumber, $dailySummaryTime, etc.  all retrieve the proper values from $row.  When I look at $result is shows "Resource ID #14", which I think is correct.

If it were not for the error message that pops up on the page when I run the script, I would have no idea anything was wrong, as the script does what it is supposed to do and finishes all of its necessary steps.

I must be missing something incredibly obvious, but I cannot tell what it is.  Anyone have any ideas?

Thanks in advance for your help...
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
php and Mysql version ?
Software Developer
Commented:
The part to fix should be somewhere in the loop you don't fully show, it may be rather lengthy, look out for code changing $result, for example, when you do another query within that loop. This $result is simply a handle to the result, and must never be changed during fetching from it, otherwise you fetch from either another result or get the error you see, that the result handle is invalid.

Bye, Olaf.
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
I think you should change the name of the first $result to something else like $result1.  You're reusing that variable in the next query and that may be causing a problem if the two items are not of the same type.

Author

Commented:
Oh gosh, of course that is the problem.  Olaf, thanks - I usually rename $result when used repeatedly in loops, but for some reason here I did not.  Below the code I showed were two additional queries in the same loop, both of which reused $result.  What was confusing was that only one record was being selected, so the query itself worked the first time through, and there was never a second time through to deliver bad data.  So all the data retrieved looked good, but the error was still there and MySQL caught it.

Dave, I would give you points also, but in fact the first $result is not the cause of the problem.  That one is disposable, so to speak - once the first query is completed I no longer need $result and can reuse it.

Thanks for the quick response - this was driving me crazy.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial