#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    6
    Rep Power
    0

    Complex MySQL limit Range


    I have the following complex query :

    Code:
    $query = "SELECT c.id as contact_id, c.email, c.name, c.website_name, c.website_url, COUNT(*) as total_emails,
    MAX(sent_date) as recent_email, t.clicks, t.views, MAX(t.status) as status, t.post_url, MAX(t.post_date) as post_date
    FROM contacts c
     LEFT JOIN emails_sent es ON c.id = es.contact_id 
    LEFT JOIN tracking t ON c.id = t.contact_id
    GROUP BY c.email
    HAVING COUNT(*) = {$_POST['emails_sent']} AND {$condition}
    LIMIT 0, {$_POST['contacts']}";
    Somewhere in this code I need to limit the results by a date range. The date is stored in the emails_sent DB. Where can this go?

    Code:
    WHERE sent_date < {$edate} AND sent_date > {$sdate}
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,927
    Rep Power
    378
    Something like this - but you should GROUP BY c.id, and I'm not sure if clicks and views is going to give you what you want...
    Code:
    $query = "
    SELECT c.id contact_id
         , c.email
         , c.name
         , c.website_name
         , c.website_url
         , COUNT(*) total_emails
         , MAX(es.sent_date) as recent_email
         , t.clicks
         , t.views
         , MAX(t.status) status
         , t.post_url
         , MAX(t.post_date) post_date
      FROM contacts c 
      LEFT 
      JOIN emails_sent es 
        ON c.id = es.contact_id 
       AND sent_date BETWEEN $sdate AND $edate
      LEFT 
      JOIN tracking t 
        ON t.contact_id = c.id
     GROUP 
        BY c.email
    HAVING COUNT(*) = {$_POST['emails_sent']} 
       AND {$condition}
     LIMIT 0, {$_POST['contacts']}";
    Last edited by cafelatte; November 20th, 2012 at 12:41 PM.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    6
    Rep Power
    0
    Thanks for the quick response. This does not change the results at all, though.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    6
    Rep Power
    0
    I think that I see what is happening here. Is this correct?

    The AND BETWEEN statement, when false, is just falling to append the emails_sent data to the current contact_id.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    6
    Rep Power
    0
    Sorry for the multiple posts....I am trying to rewrite this so the emails_sent table is queried first. The following throws an error unless the WHERE statement OR the JOIN are removed.

    Code:
    SELECT DISTINCT contact_id
      FROM emails_sent 
       WHERE sent_date 
         BETWEEN 
         $sdate AND $edate
    
     LIMIT $con
     LEFT
      JOIN contacts c
        ON c.id = contact_id
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    6
    Rep Power
    0

    [solved]


    Alright! Your first response, that you have since edited, almost nailed it.

    Code:
    WHERE es.sent_date BETWEEN $sdate AND $edate
    before the GROUP BY worked like a charm.

    Thanks so much for your help!!!
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,927
    Rep Power
    378
    That renders your LEFT [OUTER] JOIN as an [INNER] JOIN, so you may as well specify it that way to begin with!

IMN logo majestic logo threadwatch logo seochat tools logo