hiddenpearls — 2012-03-26T15:31:35-04:00 — #1
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
r937 — 2012-03-26T16:17:40-04:00 — #2
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
hiddenpearls — 2012-03-27T08:39:41-04:00 — #3
- with my app langauge i.e php, Yes I got it working
- 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 ?
r937 — 2012-03-27T08:42:20-04:00 — #4
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
hiddenpearls — 2012-03-27T08:44:33-04:00 — #5