Query working in phpmyadmin, but not in php script.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Query working in phpmyadmin, but not in php script.

Post 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?
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

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

Post by requinix »

User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

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

Post 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
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

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

Post by requinix »

requinix wrote:And what is the MySQL error message?
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

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

Post 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
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

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

Post 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.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

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

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

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

Post 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.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

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

Post 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...
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

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

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

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

Post 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!"
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

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

Post by Bill H »

Yeah, they weren't angry, they were just feeling "Puckish." You do read Shakespeare?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

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

Post by califdon »

Not if I can help it. :D
Post Reply