[SOLVED] MySQL Wizards - Need Help with Query

[ryanschefke]

Hello - I need help with a query. I am programming in PHP and trying to make a MySQL query. I have one table, visitors, and a second table, hits. I am trying to display the results of the visitors table. The columns in the visitors table are organization, city, region, country, dateTime, visitorID, and searchWords. In the output as I display each row, I want to add a column from the hits table that adds all entries in hits with the same vistorID as the particular row I am displaying from the visitors table. I am trying the following query but the COUNT seems to not work and I am not sure if the "hits.visitorID=visitors.visitorID" statement is right. Logically, you can get an idea of what I am trying to do with the erroneous query below. Thanks so much for your help!!!

 

$query = "SELECT visitors.organization, visitors.city, visitors.region, visitors.country, DATE_FORMAT(visitors.dateTime, '%m/%d/%Y %l:%i %p'), visitors.visitorID, visitors.searchWords, COUNT(hits.hitID) FROM visitors, hits WHERE visitors.customerID='$customerID_ck' AND cast(visitors.dateTime as date)='$sGMTMySqlDate' AND hits.visitorID=visitors.visitorID ORDER BY $sort LIMIT $start, $displayToday";

 

Regards,

Ryan

[GingerRobot]

No doubt you're getting an error that says you can't mix GROUP and no GROUP columns without a GROUP BY clause? Try adding one. Also, you can make your query a bit shorter by using an alias for your table names:

 

$query = "SELECT v.organization, v.city, v.region, v.country, DATE_FORMAT(v.dateTime, '%m/%d/%Y %l:%i %p'), v.visitorID, v.searchWords, COUNT(h.hitID) FROM visitors as v, hits as h WHERE v.customerID='$customerID_ck' AND cast(v.dateTime as date)='$sGMTMySqlDate' AND h.visitorID=v.visitorID GROUP BY h.visitorID ORDER BY $sort LIMIT $start, $displayToday";

[ryanschefke]

You are a genious! That solved everything!!!

 

Thank you so much.

 

Ryan