SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict hiddenpearls's Avatar
    Join Date
    Dec 2007
    Location
    Pakistan
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb query to check IP and date comparison rom mysql

    hi,

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict hiddenpearls's Avatar
    Join Date
    Dec 2007
    Location
    Pakistan
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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 ?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict hiddenpearls's Avatar
    Join Date
    Dec 2007
    Location
    Pakistan
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •