there is a table with the following structure:

----------------------------- ID IP Timestamp ----------------------------- 1 127.0.0.1 1111111111 2 127.0.0.1 1111111112

You need to do the following: new entries should not be recorded in the table, whose IP corresponds to the IP in the table, and the Timestamp in the table should be updated , otherwise a new entry is added with the new IP

 ----------------------------- ID IP Timestamp ----------------------------- 1 127.0.0.1 1111111112

I tried to set the индекс->unique , but then the Timestamp is not updated.

How to implement this business without sending 2 requests?

  • I would do this: 1. $ ip = '127.2.3.0 "; 2. We look for this ip in the table. 2. If we don’t find a request to add to the database. If we find we update Timestamp - iKey
  • @ Denis without sending 2 requests - cmd
  • 3
    Possible duplicate question: Prompt with database query - Dmitriy Simushev

3 answers 3

On the ip column, I suggest putting a unique key, put AUTO_INCREMENT on the id ( but it’s better to remove this column altogether) and use the INSERT ... ON DUPLICATE KEY UPDATE query INSERT ... ON DUPLICATE KEY UPDATE

 insert into `table_name` (`ip`, `last_modified`) values ($ip, UNIX_TIMESTAMP()) on duplicate key update `last_modified`=UNIX_TIMESTAMP()
  • 2
    @maschine, Timestamp column was called so in vain. - Visman

I would do this:

  • removed the id column
  • would make the ip column primary key
  • would use the query REPLACE INTO `table` VALUES ('% ip%',% timestamp%)

Something like this.

    can be implemented via a trigger to insert an after or before

     CREATE TRIGGER ip_insert ON table_ip INSTEAD OF INSERT AS begin if exists(select 1 from table_ip where table_ip.ip=inserted.ip) update table_ip set Timestamp=99999 where table_ip.ip=inserted.ip else insert into table_ip selec ID,IP,Timestamp from inserted end