[Resolved] SELECT count(*) giving strange result

bunner_bob

I'm running the following query on a table:

$query = "SELECT count(*) as count from photos where img_type='story' AND article_id='1' and sortorder='r' AND img_id != 115";

and I keep getting a count of 1.

Problem is, in the table sortorder is either an integer or NULL. When I set sortorder to a number it works as expected - if there's another photo with that sortorder I get a count, if there isn't I get no count. Why would setting sortorder to a letter give me a result?

Most puzzling....

If this worked as expected, I have a second validation routine that confirms the entry is an integer, within a set range. It should be falling right through the first check (the 'count') with a count of zero - shouldn't it?

bubblenut

Why not change it to select * and then have a look at the result. You might get a better idea of how it's matching.

cheese

Your query was;

[PHP
$query = "SELECT count(*) as count from photos where img_type='story' AND article_id='1' and sortorder='r' AND img_id != 115";

[/code]

If the article_id is what it suggests wouldn't you only ever get the one row where article_id='1'. I am assuming that this column is different for each article.

Neil.

bunner_bob

bubblenut - I'll try that. I was trying it but for some reason my query wasn't working. I'll try harder.

cheese - multiple photos per article. The query looks to make sure there isn't already a photo with that sortorder, within that particular article.

bunner_bob

OK - I get a match if the existing item (in the db table) has a sortorder value of "0" (this is the flag I use to indicate items that will sort at the end of the article, instead of being placed within the article.

Why would a query asking for items with "sortorder='r'" (or any other letter) match a db entry with "sortorder='0'"?

The field type is 'smallint(6)', btw.

nemonoman

Since the sort field is a number, mysql converts the search element to from a string to a number.

If you said sortorder='2'

MySQL would convert the string '2' to 2, its numerical equivalent.

Since the string 'r' has the numerical equivalent of NULL or 0,

sortorder='r'

equates to

sortorder=0

nemonoman

by the way, here's a newbie error that happens all the time:

$result=mysql_query('SELECT COUNT(*) FROM mytable');
$howmany=mysql_num_rows();

no matter how many rows are in mytable, the variable $howmany will ALWAYS BE SET TO 1.

Can you see why?

Hint: COUNT(*) returns a number, like 8 or 2222222.

bunner_bob

Yeah - I always do 'count(*) as count' or some such.

I see what you're saying about it converting 'r' to 0. Guess I'll have to put my 'make sure it's an integer' validation before my 'make sure it isn't the same as something else' validation.

Thanks for the he'p!