Adding COUNT to my sql query returns 0 results.

Mysterious.

[HughMungus]

What's wrong with this? Removing COUNT gives me normal results. Putting it in gives me nothing.

SELECT id, itemdatetime, DATE_FORMAT(itemdatetime, '%M %d, %Y') as itemdate, DATE_FORMAT(itemdatetime, '%M %d, %Y') as displaydate, c1, c2, hlink, title, subtitle, image, thumb, COUNT(*) as numrows FROM items ORDER BY itemdatetime DESC

[jatar_k]

what about

SELECT id, itemdatetime, DATE_FORMAT(itemdatetime, '%M %d, %Y') as itemdate, DATE_FORMAT(itemdatetime, '%M %d, %Y') as displaydate, c1, c2, hlink, title, subtitle, image, thumb, COUNT(id) as numrows FROM items ORDER BY itemdatetime DESC

[HughMungus]

Same result. Nothing...and I can't get a sql error to show with mysql_errno. Grr.

[digitalv]

Try:

SELECT id, itemdatetime, DATE_FORMAT(itemdatetime, '%M %d, %Y') as itemdate, DATE_FORMAT(itemdatetime, '%M %d, %Y') as displaydate, c1, c2, hlink, title, subtitle, image, thumb, numrows=(SELECT count(ID) FROM Items) FROM items ORDER BY itemdatetime DESC

or

SELECT id, itemdatetime, DATE_FORMAT(itemdatetime, '%M %d, %Y') as itemdate, DATE_FORMAT(itemdatetime, '%M %d, %Y') as displaydate, c1, c2, hlink, title, subtitle, image, thumb, (SELECT count(ID) FROM Items) as numrows FROM items ORDER BY itemdatetime DESC

Not sure which one will do the trick, both work in Microsoft SQL but I'm not up to date on the latest mySQL syntax.

[jatar_k]

what if you try just

select COUNT(id) as numrows FROM items

?

[HughMungus]

what if you try just
select COUNT(id) as numrows FROM items

I tried that on a seperate page and I do get a normal and correct count.

I tried cutting the query down to one field and the count and that didn't work either (print_r gives me nothing). It's as if adding COUNT throws off the whole statement...yet I don't get an error.

digitalv, I tried yours and neither worked.

I thought maybe there was something weird about COUNT statements that I'm missing (I'm admittedly learning as I go instead of taking a class :P).

But thanks for your help.

[coopster]

Actually, I'm surprised you are not getting an error reported somewhere here.

COUNT()

is what is called a set function in standard SQL. Whenever you use a set function, there is an implied

GROUP BY

clause. So when you see something like

SELECT COUNT(id) FROM items;

remember that there is an implied

GROUP BY

. Then, when there is a

GROUP BY

clause, there are certain rules that apply for grouping columns. One of those rules is "The Single-Value Rule" -- every column named in the SELECT list must also be a grouping column unless it is an argument for one of the set functions.

You will need to use a GROUP BY clause to get your query to execute without errors, and don't forget, every column named in the

SELECT

list must also be a grouping column:

SELECT 
 id, 
 itemdatetime, 
 DATE_FORMAT(itemdatetime, '%M %d, %Y') as itemdate, 
 DATE_FORMAT(itemdatetime, '%M %d, %Y') as displaydate, 
 c1, 
 c2, 
 hlink, 
 title, 
 subtitle, 
 image, 
 thumb, 
 COUNT(*) as numrows 
 FROM items 
 GROUP BY id, itemdateime, itemdate, displaydate, 
   c1, c2, hlink, title, subtitle, image, thumb 
 ORDER BY itemdatetime DESC 
;

[HughMungus]

Thanks, coop. Group by did the trick (though I'm not sure why a count needs a group by but I'll look that up later).