John Snow John Snow - 1 month ago 4
MySQL Question

The best way to count visits in php

I have got the mysql table which includes data about users who visited my web service (IP address, date). The one IP address can be logged in my database only once per day.

What is the best way to create the table contains visits from last month? I want something like:

30.10.2016 | 1457

31.10.2016 | 1604

01.11.2016 | 1590

etc

Sorry for English and maybe the very simple question, first time on stack :)

Answer

use this sql code to create a table for last month visitor count.

CREATE TABLE last_mon_visit AS 
SELECT DISTINCT date, COUNT(ip_address)
FROM visit
WHERE YEAR(date) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(date) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
GROUP BY date

i would suggest to create view instead of table.

Comments