I have a table which records visitors by IP address, date first visited and date last visited, for multiple websites. Both dates are in datetime format like 2016-08-07 12:14:44. Each record is specific to a website reference.
I am trying to list the last 6 months by year and month like (Y-m)
2016-08
2016-07
2016-06
etc... I have managed to do this although am struggling on the code to count distinct visitors, here is what I have so far:
$this_month = date('Y-m');
$count = mysqli_query($conn, "SELECT COUNT(ip_address)) as count FROM `all_website_stats` WHERE `website_ref` = '$show_website_ref' AND EXTRACT(YEAR_MONTH FROM date_first_viewed) = '$this_month' AND `date_first_viewed` = `date_last_viewed`");
$rowcount=mysqli_num_rows($count);
echo $rowcount;
This will be performed 6 times to get the final count of visitors who have not returned. Although this code is returning errors. And when I did manage to get it working, it only counted 1 or 0 which is incorrect.
It shown the error once I added date_first_viewed = date_last_viewed which I guess is probably wrong. Although I need to make sure that the date_first_visited is the same as date_last_visited in the database, else the result is a returning visitor instead.
Can somebody point me in the right direction here and let me know what I'm doing wrong? I'm not great with these kind of date checks with MySQL.
You say that you want all unique visitors per month. However, I would describe your query as getting all one-time visitors per month. If so, it seems like you need a GROUP BY:
SELECT EXTRACT(YEAR_MONTH FROM date_first_viewed) as yyyymm,
COUNT(ip_address)) as count
FROM `all_website_stats` aws
WHERE `website_ref` = '$show_website_ref' AND
`date_first_viewed` = `date_last_viewed`
GROUP BY EXTRACT(YEAR_MONTH FROM date_first_viewed);
You can add a condition to the WHERE clause to specify the time period for the returned rows.
The WHERE clause would look something like this:
WHERE date_first_viewed >= '2016-01-01'