Page 1 of 1

Query working in db, but not in php

Posted: Wed Sep 07, 2005 2:08 pm
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?

Posted: Wed Sep 07, 2005 2:12 pm
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.

Posted: Wed Sep 07, 2005 2:43 pm
by Bill H
Worthwhile suggestion (I assume you meant "die").
I did that and it didn't die.

Anything else to suggest?

Anybody?

Posted: Wed Sep 07, 2005 2:56 pm
by feyd
yes 'die', sorry.. try removing the conditional stuff, so simply run the first creation of the query.. what's the table structure?

Posted: Wed Sep 07, 2005 4:00 pm
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...

Posted: Wed Sep 07, 2005 5:53 pm
by feyd
I realize you did echo it, but that part is the only thing variable in the equation at this point.

Posted: Wed Sep 07, 2005 6:58 pm
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.
?????

Posted: Wed Sep 07, 2005 7:38 pm
by feyd
I know this sounds odd, but try retyping the entire line, no copy and paste.. rewrite it all.

Posted: Wed Sep 07, 2005 7:51 pm
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.

Posted: Wed Sep 07, 2005 8:02 pm
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.