Results set incorrect

Rows are in results that shouldnt be

[alexelisenko]

Hello,

Im having an odd issue, and im sure im doing something wrong, so here we go!

I have a table with colms = Title, Desc, Date, Approved

Approved is set to INT(2) w/ Default of 0

I am pulling the rows as such:

$string = "SELECT * FROM `tblname` WHERE `Approved` = '1'"

$result = mysql_query($string);

while($ouput = mysql_fetch_array($result)){

//Do output here

}

//Rest of code//// You get the point.

My problem is that even rows that have 0 as the Approved value show up when I output the info in a while loop. What might be the problem?

Any thought would be appreciated.

[rocknbil]

Thought for sure someone would see what I'm not seeing . . . I cannot see a single reason why this doesn't work.

I set up a test. I thought it might be the backticks - which are really only needed if your table fields contain anything but a-z or _, or the names conflict with function names or other reserved words - but this made no difference.

Which really can only mean one thing. If you are getting BOTH rows with Approved = 1 and Approved = 0, the summary below is WRONG . . . but I don't see any other possibility.

I always suggest no quotes on queries for numeric fields. If the value is populated with a variable, and that variable is not a number,

$var = NULL;
select * from test where Approved='$var';
-> returns Approved=0 rows
$var = 'blah';
select * from test where Approved='$var';
-> returns Approved=0 rows
$var = '';
select * from test where Approved='$var';
-> returns Approved=0 rows, seeing a trend?

You're querying a value which will evaluate to zero. If you are returning only zero records when you think you are querying 1, this may be the problem.

But if you do not quote queries on numeric fields,

$var = '';
select * from test where Approved=$var;
-> returns "error in mySQL syntax"

You get an error, which is often a good thing - it means you did not properly test your variables before the query (baaad coder! :-) )

Otherwise, something has to be broken in your mySQL, I don't see any other possibility.

[whoisgregg]

The only thing I see is the quoting of the integer in the sql statement and a missing semicolon at the end of that same line. The 1 is wrapped in single quotes, meaning it's cast as a string:

$string = "SELECT * FROM `tblname` WHERE `Approved` = '1' "

If it's an INT column, it should not have quotes around it:

$string = "SELECT * FROM `tblname` WHERE `Approved` = 1 ";

However, that shouldn't break anything... As far as I know (and I just checked on my own server) MySQL will automatically cast the query to the right type to match the column type. This only gets messy when you are dealing with an ENUM or SET field with numeric values, as you can use either integers or strings to mean different things.

I'd echo the $string variable *right before the mysql_query* to make sure it's set to what you think it's set to.

[alexelisenko]

Thanks guys for your input, I will try some more debugging and let you know how it goes. Since posting this I have rewritten my entire search engine script so I haven't tested this problem yet.

Thanks.