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
