Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    mwm
    mwm is offline
    Regular Coder
    Join Date
    Oct 2005
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    how to get next date?

    I have a contact form that inserts the date the last time a sales person has made contact with the buyer. I want to display this in a report, so when they run the report it show the last date contact was made. something like select next closes date to current date. any ideas how this could be done?

    Thanks

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    SELECT MAX(contactDate) FROM contactTable where buyer = $buyer will select the highest (most current) date. Is that what you're after?

  • #3
    mwm
    mwm is offline
    Regular Coder
    Join Date
    Oct 2005
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes that's what I was going for, but still can't make it work. Here is the query

    PHP Code:
    $mysql = ("select cust.id,cust.fname,cust.lname,cust.address,cust.city,cust.state,cust.zip,cust.hphone,cust.community,date_format(cust.regdate, '%m-%d-%Y'), sum(followup.tpcount) as t_count, MAX(followup.tpdate) from cust, followup where cust.regdate < curdate() - interval 0 day and community = '$community' and cust.id = followup.id and status = 'Prospect'  group by lname having t_count < 1"); 

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    SUM() and MAX() are column functions so they can't be mixed with non-column functions... basically when you do a "GROUP BY" you you can only select columns that are in that GROUP BY clause.

    A way around this is to do a subquery, but your version of MySQL must be 4.1 or greater.

    Code:
    SELECT *
    FROM table1 as a
    WHERE date1 = (select MAX(date1) from table1 as b WHERE a.id = b.id)


  •  

    Posting Permissions

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