SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict
    Join Date
    Sep 2000
    Location
    Ontario, Canada
    Posts
    320
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    quick date_sub question

    At the start of every month I'm running a script that outputs customer names and how much they owe per month. What I'm trying to do now is add a one time setup fee for each customer. So I was thinking of using mySQL's IF and DATE_SUB to determine whether or not the person signed up within the month and add the setup fee if they do.

    Here's what I have but I don't think my logic is correct. I'm having trouble combining mySQL's IF and DATE_SUB together.

    PHP Code:
    SELECT agentnamesignup,
    IF((
    DATE_SUB(CURDATE(),signup <= INTERVAL 1 MONTH),1,0) AS new_customer
    FROM agents

    I based my query from code given here

    Am I on the right track here? Is an IF/DATE_SUB even appropriate for what I'm trying to do?

    I'm assuming that $signup should be in TIMESTAMP(14) format - correct?

    As always... thanks for any help
    Jason Dulberg
    Extreme MTB
    http://extreme.nas.net

  2. #2
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes your query is a little bit off

    Should be something like

    PHP Code:
    SELECT agentnamesignup,
    IF((
    CURDATE() - INTERVAL 1 month) <= signup,1,0) AS new_customer
    FROM agents

    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  3. #3
    SitePoint Addict
    Join Date
    Sep 2000
    Location
    Ontario, Canada
    Posts
    320
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Freddy!!
    guess I was close... sort of.

    I tried your code but it gave me an error:

    You have an error in your SQL syntax near '1 month) <= signup,1,0) AS new_customer
    FROM agents' at line 2

    The signup field is defined as TIMESTAMP(14)

    Thanks again!
    Jason Dulberg
    Extreme MTB
    http://extreme.nas.net

  4. #4
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm... Should work in MySQL 3.23 at least here this should do the trick.

    PHP Code:
    SELECT agentnamesignup
    IF(
    DATE_SUB(CURDATE(), INTERVAL 1 month) <= signup,1,0) AS new_customer FROM agents
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  5. #5
    SitePoint Addict
    Join Date
    Sep 2000
    Location
    Ontario, Canada
    Posts
    320
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Awesom, it worked! I just checked and I have mySQL version 3.22.32 installed... looks like I should be upgrading.

    Thanks again for your help!
    Jason Dulberg
    Extreme MTB
    http://extreme.nas.net


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
  •