Query working in db, but not in php

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 db, but not in php

Post by Bill H »

This is the query string, which looks okay to me.

Code: Select all

SELECT id,Sid FROM Reports WHERE Stat>1 AND Stat<4 ORDER BY Numbr
When executed in phpmyadmin, it returnd records as expected.
When executed by this following code, it returns zero records.

Code: Select all

$Query = "SELECT id,Sid FROM Reports WHERE Stat>1";     // all reviewed reports

if ($_SESSION['Method'] > 2)
     $Query .= " AND YEAR(Date)=" . $_SESSION['Cyr'] . " AND MONTH(Date)=" . $_SESSION['Cmth'];
else if ($_SESSION['Method'] > 1)
     $Query .= " AND Sid=" . $_SESSION['Cstr'];              // for selected store
else $Query .= " AND Stat<4";                                // not previously viewed

$Query .= " ORDER BY Numbr";
$Result = mysql_query($Query, $Link);
while ($row =  mysql_fetch_array($Result))
     // etc
(I did a "echo $Query" to be sure the query string is correct, and it is.)
The other two constructs result in similar query strings and they return results just fine
from the same table, so database is selected properly and all that.

Any help from any of you gurus?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

tried chaning the query call to

Code: Select all

$Result = mysql_query($Query, $Link) or dir(mysql_error());
for giggles? for whatever reason, you may have something in there it doesn't like.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post by Bill H »

Worthwhile suggestion (I assume you meant "die").
I did that and it didn't die.

Anything else to suggest?

Anybody?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

yes 'die', sorry.. try removing the conditional stuff, so simply run the first creation of the query.. what's the table structure?
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post by Bill H »

Don't really need to remove the conditionals, because I have echo'd $Query to verify its accuracy.
(That's one of the first things I do, because I build query strings wrong rather often.)
It echo's precisely as I showed in my initial post.

Table structure:
id = bigint(6)
Sid = int(6)
Stat = tinyint(1)
Numbr = varchar(7)

There's a bunch more in the table, but...
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I realize you did echo it, but that part is the only thing variable in the equation at this point.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post by Bill H »

Code: Select all

$Query = "SELECT id,Sid FROM Reports WHERE Stat>1 AND Stat<4 ORDER BY Numbr";
$Result = mysql_query($Query, $Link) or die(mysql_error());
while ($row =  mysql_fetch_array($Result))
Does not die, returns zero records.
Copy the SQL statement and exectue it in phpmyadmin it returns several hundred records.
?????
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I know this sounds odd, but try retyping the entire line, no copy and paste.. rewrite it all.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post by Bill H »

Typed it letter by letter in phpmyadmin and got:
Showing rows 0 - 29 (756 total, Query took 0.0610 sec)
Typed it letter by letter in the code and got 0 records.

Both places I typed it letter by letter using two fingers, and verified each letter as I typed it.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

okay, that's just bizarre. Could you put the file up somewhere? If you need security, you could pm me the url, or whatever.. (I'd suggest not sanitizing it for security reasons because that may compromize what I think may be wrong, but I have to verify it. Using Windows to copy the file to a new filename (changing it to .txt or something that php won't parse would be helpful.)

alternately, we can take this to an instant messenger if you like.
Post Reply