[SOLVED] Reporting for expired entries

[Rednarb]

I'm sorry - I can't even think of a good title for this one. Heck, I'm not sure I even have it in the right forum. Its database related but I think the code I'm looking for is not database specific...

 

Background: I have a database that includes tasks each with a due date. The tasks are assigned to different people but some people have multiple tasks.

 

I'm trying to run a report on expired tasks (due_date < NOW()), and send an email to the assigned person.

 

Finding overdue tasks is easy.

Getting the user that is assigned is just as easy.

 

Grouping the overdue tasks by user has my brain fried. If Joe Bob has 10 overdue tasks, I want to sent him one email with the 10 tasks listed.

 

I've tried a 2-dimensional array, but when I FOR EACH it I get an email to the user with the multiple tasks once for each multiple. I bet that made no sense... What I mean is 2 emails went to Joe Bob, each listing the two tasks that are overdue.

 

Maybe I'm going about it the wrong way; First I search the DB for any tasks with a due date less than NOW() and assign them to an associative array. I could just FOR EACH that array and poke an email out for each entry. But like I said, users with multiple overdue tasks will then get multiple emails.

 

So I'm looking for suggestions on how to only send one email to a user even if they have more than one task.

 

Here's what I have so far:

//find assigned changes that are past due
$query = "SELECT chgidx,assigned_to,date_scheduled,short_desc FROM change_request ";
$query.= "WHERE `current_status` = 'Approved' AND `date_scheduled` < NOW() ORDER BY `date_scheduled`";
$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());
$count = mysql_num_rows($result);
if ($count > 0) {
  while($change = mysql_fetch_assoc($result)) {
    $user = $change['assigned_to'];
    $output[$user][] = $change['chgidx']." ".$change['short_desc']." scheduled ".$change['date_scheduled'];    
  }
}
mysql_free_result($result);

 

That gives me a cool array called $output and it has keys for each user with overdue tasks. Each value has what I plan to put in HTML code, and in the effort of shortening the line for demonstration, I've shortened it significantly in this example.

 

Results of print_r($output):

Array
(
    [joebob] => Array
        (
            [0] => 166 test1 scheduled 2008-01-01 20:00:00
            [1] => 172 test2 scheduled 2008-01-01 20:00:00
        )

    [maryjane] => Array
        (
            [0] => 173 test3 scheduled 2008-01-01 20:00:00
        )

)

 

So I tried to FOREACH the array and it just blew all to hell. Can you suggest either a better way to do the whole thing or a way I can pull the data from this array lumped by user ID?

 

TIA!

 

Eric

 

 

[edit: MySQL 5, PHP 5]

[Barand]

try something like this

 

<?php
$sql = "SELECT p.name, p.email,
        GROUP_CONCAT(idtask,' ',short_desc,' ', date_scheduled SEPARATOR '<br>') as tasks
        FROM task t
        INNER JOIN person p ON t.assigned_to = p.id
        WHERE date_scheduled < CURDATE()
        GROUP BY p.name";
?>

[Rednarb]

Thank you very much. I guess I did come to the right forum. I was thinking I had to work this out in PHP but you've shown me a much better way in SQL and it works great!

 

Thanks again!