Query to check IP and date comparison rom mysql


I have 2 fields (IP, date_added) in mysql.
date_added is TIMESTAMP which is added/update when record is added.

before inserting new record, I want to make a check no new record will enter within 24 hours from same IP.
need SQL help, thanks

the way you’ve set it up, the only way to implement this is to do a query with your application language (php, yes?)

retrieve the latest row for the specific IP, and if it’s older than 24 hours, then go ahead an do the insert

however, with just a slight change of design, you can simplify things a bit

if you change the TIMESTAMP to a DATE column, and define the primary key as a composite of the IP plus date, then you can use INSERT IGNORE, thus using only one database call instead of two

  1. with my app langauge i.e php, Yes I got it working
  2. but how if I make it composite key IP + Date. but I’m making a check of 24 hours. like someone from same IP entered at 2pm then again enter after exactly 24 hours. got my point ?

my suggestion was “one entry per date per IP”

so if IP + 2 pm, then you could make another entry 10 hours later, on the next date

if you really want 24 hour separation, you will have to do it in two steps – select, calculate, then insert or reject

ok Thanks.