homepage Welcome to WebmasterWorld Guest from 54.211.235.255
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Results set incorrect
Rows are in results that shouldnt be
alexelisenko




msg:4039197
 2:07 am on Dec 8, 2009 (gmt 0)

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




msg:4039650
 8:32 pm on Dec 8, 2009 (gmt 0)

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




msg:4040972
 9:46 pm on Dec 10, 2009 (gmt 0)

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




msg:4047891
 10:01 pm on Dec 22, 2009 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved