Page 1 of 1

Query working in phpmyadmin, but not in php script.

Posted: Fri Jun 29, 2012 1:48 pm
by Bill H
I have a small MySQL database with a varchar column named "Reads" and three tinyint columns named "Typ," "Phn" and "Eml."

This code creates one of two SQL calls, which I have echoed to verify.

Code: Select all

mysql_select_db($dbdName, $Link);
$Query = "SELECT * FROM Form" . sprintf("%02d",$Row['Ver']) . " WHERE " . $Psn[$Row['Typ']] . ">0 ORDER BY " . $Psn[$Row['Typ']];  
$Fres =  mysql_query($Query, $Link);
while ($Frow = mysql_fetch_array($Fres))          // for all of the rest of the headings and questions
SELECT * FROM Form02 WHERE Eml>0 ORDER BY Eml
and
SELECT * FROM Form02 WHERE Phn>0 ORDER BY Phn

The second one returns a proper dataset as expected, the first one triggers an error saying that the "mysql_fetch_array(): supplied argument is not a valid MySQL result resource." Both of them, however, yield a valid result when I invoke them within phpmyadmin, producing the expected number of rows, properly ordered.

Any ideas as to wtf?

Re: Query working in phpmyadmin, but not in php script.

Posted: Fri Jun 29, 2012 10:04 pm
by requinix

Re: Query working in phpmyadmin, but not in php script.

Posted: Sat Jun 30, 2012 12:21 am
by Bill H
"mysql_fetch_array(): supplied argument is not a valid MySQL result resource."

It references the line which reads:

Code: Select all

while ($Frow = mysql_fetch_array($Fres))          // for all of the rest of the headings and questions

Re: Query working in phpmyadmin, but not in php script.

Posted: Sat Jun 30, 2012 2:40 am
by requinix
requinix wrote:And what is the MySQL error message?

Re: Query working in phpmyadmin, but not in php script.

Posted: Sat Jun 30, 2012 2:52 pm
by Bill H
I guess I'm not understanding the question. Is the phrase, "mysql_fetch_array(): supplied argument is not a valid MySQL result resource" with a line number not a MySQL error message? Because that's all I'm getting. When I run the queries in phpmyadmin they both execute without any error, so that's not going to produce any error messages, and when I run them in PHP one produces a valid result and the other produces the essage I stated and nothing other than that. I don't know what message I can provide to you. If you will advise me what I am missing I will be glad to provide what you are asking me for.

Here's the whole thing, minus the full address for security reasons.

Code: Select all

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in ... form.inc.php on line 196

Re: Query working in phpmyadmin, but not in php script.

Posted: Sat Jun 30, 2012 5:06 pm
by requinix
That link I posted was to a function: mysql_error(). You're supposed to figure out how to use that function (just echo out what it returns) and where to put it (just after the mysql_query() that fails). That will show you a different error message. That message will tell you why the query had problems - which is why PHP and mysql_fetch_array() had problems too.

Re: Query working in phpmyadmin, but not in php script.

Posted: Sat Jun 30, 2012 7:22 pm
by Bill H
Thanks, I didn't notice the link.

Code: Select all

SELECT * FROM Form02 WHERE Eml>0 ORDER BY Eml
It returns 1054, mysql_error() returns " Unknown column 'Eml' in 'where clause'" which is bogus because there is such a column and when the same SQL statement is executed in phpmyadmin it returns a dataset.

Re: Query working in phpmyadmin, but not in php script.

Posted: Sat Jun 30, 2012 7:35 pm
by califdon
Are you sure that it's not an upper/lower case issue in the code? That's the only reason I can think of that a script that is otherwise identical to a manual input to phpmyadmin on the same exact database would produce an error, when it doesn't in phpmyadmin (which, of course, uses your input in its own PHP script. I'm assuming that your script really does run against the very same database.

Re: Query working in phpmyadmin, but not in php script.

Posted: Sat Jun 30, 2012 9:55 pm
by Bill H
Yeah, I'm sure. It's baffling. The two SQL statements are pasted here from echo() lines inserted into the script. One works, the other does not. The "Eml" column is definitely "Eml," and in both cases I Ctrl-C copied the echo() output and Ctrl-P pasted it into phpmyadmin. Both produced valid datasets. I'm going to try putting backticks on the column name in a bit and see if that solves it. It shouldn't, because Eml is not a reserved word (I checked) but...

Re: Query working in phpmyadmin, but not in php script.

Posted: Sun Jul 01, 2012 1:15 am
by Bill H
Well, backticks didn't solve it either but your comment, califdon, about "your script really does run against the very same database" got me to looking more closely at:

Code: Select all

mysql_select_db($dbdName, $Link);
The $dbdName and $Link variables are set in an include file, different for each set of forms I generate from its own database, and maybe the wrong include file got put into this particluar directory. I'm not sure whether or not that was the case, but I just re-uploaded all of the correct ones and that corrected the issue, so it appears that I was indeed connecting to the wrong database. It was wierd, though, because the dataset that the "Phn" call was producing (apparently from a different form set's data) was essentially identical to the dataset for this form. had it been sufficiently different I would have noticed, "Hey, not only is it crashing on the Email form, it's listing the wrong questions on the Phone form."

It's late and I'm getting too old for this.

Re: Query working in phpmyadmin, but not in php script.

Posted: Sun Jul 01, 2012 1:11 pm
by califdon
Glad you sussed it out, Bill. I used to work with a colleague who, when things were going wrong unaccountably, would always say, "The computers Gods are angry this morning!"

Re: Query working in phpmyadmin, but not in php script.

Posted: Sun Jul 01, 2012 1:14 pm
by Bill H
Yeah, they weren't angry, they were just feeling "Puckish." You do read Shakespeare?

Re: Query working in phpmyadmin, but not in php script.

Posted: Sun Jul 01, 2012 2:04 pm
by califdon
Not if I can help it. :D