What's wrong with this mysql query?

[usbworkshop]

Hi, can anyone tell me if there's anything wrong with this query?

$today = date("Ymd");

$query = "SELECT products, productid, COUNT(productid) AS count, DATE_FORMAT(date, '%Y%m%d') AS verbaldate FROM clicks GROUP by productid WHERE verbaldate = $today ORDER BY count DESC LIMIT 25";

I want to rank my products according to the number of times they appear in the database.

Table variables include: products, productid, and date.

Thanks in advance!

[Kevin Yank]

Looks okay at a glance. Are you having any trouble with it?

[freddydoesphp]

I do believe the GROUP BY clause should go after the WHERE clause


$query = "SELECT products, productid, COUNT(productid) AS count, DATE_FORMAT(date, '%Y%m%d') AS verbaldate FROM clicks WHERE verbaldate = $today GROUP by productid ORDER BY count DESC LIMIT 25";

[usbworkshop]

Still doesn't work.

I tried everything.Can I use sub-query to do this? If so, how does it work?

[freakysid]

This is just a guess - but does by formating the date usiing DATE_FOMRAT mean that you should be comparing the string verbaldate to $today and therefor you need to enclose $today in quotes?

[usbworkshop]

Thanks to all...I got that thing to work...I cannot use the DATE_FORMAT afterall. Don't know why. I always have to use LIKE to get things working in a mySQL query.

hmm...

[freddydoesphp]

Did you try it like this:

$query = "SELECT products, productid, COUNT(productid) AS count, DATE_FORMAT(date, '%Y%m%d') AS verbaldate FROM clicks WHERE DATE_FORMAT(date, '%Y%m%d') = '$today' GROUP by productid ORDER BY count DESC LIMIT 25";