INSERT ON DUPLICATE KEY UPDATE IF语句

Question

I'm trying to create a script for stats about visitors to my site. To do this, I record the visitor's IP, along with the date of the day and the number of times it has passed. If this is the first visit, on all records in the database. But I want to count 1 pass per person per day.

What I am trying to do : If the IP already exists, and the date is different from the day : we assign the date of the day, and increment the number of passing (+1).

The Problem : When the date is different from the day, it is changed, BUT: the number of passing continues to increment even if the IP has already been counted that day.

It should only be done the next day, when the date changes...

Here is my table structure :

--
-- Table structure for table `ChartsGuests`
--

CREATE TABLE `ChartsGuests` (
    `IP_Guest` varchar(39) NOT NULL,
    `Date` varchar(10) NOT NULL,
    `Total` int(11) NOT NULL,
    PRIMARY KEY (`IP_Guest`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB DEFAULT CHARSET=utf8;

Here is the code :

$IP_NewGuest = $_SERVER['REMOTE_ADDR'];

$Today = date('d/m/Y');


$SQL = "INSERT INTO `ChartsGuests` (`IP_Guest` , `Date`, `Total`) VALUES ('".$IP_NewGuest."' , '".$Today."', 1)
        ON DUPLICATE KEY UPDATE     
        Date = IF(Date != '".$Today."', VALUES(Date), '".$Today."'),
        Total = IF(Date != '$Today', VALUES(Total), Total + 1 )";



$REQ = $DB->prepare($SQL);
$REQ->execute() or die(var_dump($REQ->errorInfo()));

// echo $SQL;

It should only be done the next day, when the date changes... I do not know where the problem comes from, and this is the first time I use the "ON DUPLICATE KEY" with an "IF" ...

Thank you in advance !

Answer 1

Your problem is that your duplicate key is just on the IP address, but your table is really unique per IP Address/Date combo. As a result, visits on subsequent days overwrite the rows for the previous day.

If you change the logic of your table to have composite unique key on those two fields, the query will generate inserts for new (IP,Date) combos, and updates for (IP,date) combos that have been seen already.

If you fix that, you don't need the conditional (nor PHP for the current date), and you can just make this your SQL:

INSERT INTO `ChartsGuests` (`IP_Guest` , `Date`, `Total`)
 VALUES ('".$IP_NewGuest."' , CURDATE(), 1)  
ON DUPLICATE KEY UPDATE Total =  Total + 1 )";