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 3 of 3
  1. #1
    Regular Coder grumpy's Avatar
    Join Date
    Oct 2006
    Location
    Visible light spectrum
    Posts
    121
    Thanks
    5
    Thanked 6 Times in 6 Posts

    Group by and display

    I cannot seem to get this query displayed properly. Here is the code:

    PHP Code:
    $select_stats "SELECT c.ResponderID, COUNT(MsgID), a.SubscriberID, CanReceiveHTML, EmailAddress FROM InfResp_msglogs AS a, InfResp_subscribers AS b, InfResp_responders AS c WHERE b.CanReceiveHTML = 1 AND c.owner='".$_SESSION['logged_user_id']."' AND c.ResponderID = b.ResponderID AND b.SubscriberID = a.SubscriberID GROUP BY c.ResponderID";
        
        
    //echo $select_result;
            
        
    while($row mysql_fetch_array($select_result))
        {
            
    $messages $row['COUNT(MsgID)'];
            
    $campaign $row['ResponderID'];
            
    $subscriber $row['SubscriberID'];
            
    $subscriber_email $row['EmailAddress'];
            
            echo 
    "<br>Campaign = "$campaign ."<br>Sent emails count = "$messages ."<br>Subscriber Nr. = "$subscriber ."<br>Subscriber Email = "$subscriber_email ."<br>";
             } 
    This is the output:

    Campaign = 10
    Sent emails count = 109
    Subscriber Nr. = 95
    Subscriber Email = timothy@gmail.com

    Campaign = 11
    Sent emails count = 16
    Subscriber Nr. = 97
    Subscriber Email = alan@yahoo.com

    As there are three different subscribers under campaign no.10 and one under campaign no.11 in the db (each with its own email addresses of course), I would like the output to be:

    Campaign = 10
    Sent emails count = 109
    Subscriber Nr. = 92
    94
    95
    Subscriber Email = timothy@gmail.com
    anthony@yahoo.com
    josie@hotmail.com

    Campaign = 11
    Sent emails count = 16
    Subscriber Nr. = 97
    Subscriber Email = alan@yahoo.com

    Thanks.
    Last edited by grumpy; 04-12-2011 at 04:20 PM.
    No-bull Bite-sized business strategy and marketing - FreeOfNoise.com

  • #2
    Regular Coder grumpy's Avatar
    Join Date
    Oct 2006
    Location
    Visible light spectrum
    Posts
    121
    Thanks
    5
    Thanked 6 Times in 6 Posts
    Had to figure it out myself. The code is:

    PHP Code:
    $len 8192;
        
    $query "SET group_concat_max_len=$len";
        
    $resulty mysql_query($query);
        if (!
    $resulty)
        {
            die(
    "error! $query<br>".mysql_error());
        } 
        
    $select_stats "SELECT c.ResponderID, COUNT(MsgID), CanReceiveHTML, GROUP_CONCAT(a.SubscriberID) as subIDs, GROUP_CONCAT(EmailAddress) as Eaddrs FROM InfResp_msglogs AS a, InfResp_subscribers AS b, InfResp_responders AS c WHERE b.CanReceiveHTML = 1 AND c.owner='".$_SESSION['logged_user_id']."' AND c.ResponderID = b.ResponderID AND b.SubscriberID = a.SubscriberID GROUP BY c.ResponderID";
        
        
    $select_result mysql_query($select_stats) or die("Invalid query: " mysql_error());
        
    //echo $select_result;
            
        
    while($row mysql_fetch_array($select_result))
        {
            
    $messages $row['COUNT(MsgID)'];
            
    $campaign $row['ResponderID'];
            
            
    $subscriber explode(','$row['subIDs']);
            
    $subscriber array_unique($subscriber);
            
    $subscriber implode(', '$subscriber);
            
            
    $subscriber_email explode(','$row['Eaddrs']);
            
    $subscriber_email array_unique($subscriber_email);
            
    $subscriber_email implode(', '$subscriber_email);
            
            echo 
    "<br>Campaign = "$campaign ."<br>Sent emails count = "$messages ."<br>Subscriber Nr. = "$subscriber ."<br>Subscriber Email = "$subscriber_email ."<br>";
            
        } 
    Will be asking questions on stackoverflow.com from now.

    Time's up!
    No-bull Bite-sized business strategy and marketing - FreeOfNoise.com

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,537
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    He asks the question at 5AM (well, 5AM my time...but that's 8AM even East Coast time) and is upset he doesn't get an answer within three hours.

    Maybe stackoverflow.com is populated mostly by European posters whose time zone matches his more.

    Sheesh.

    Anyway, the query has a major bug in it and he's making PHP do more work than it needs to, so maybe stackoverflow.com doesn't always have the best answers.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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