I want to send the overdue tasks that are assigned to a specific employee as an email summary. It is possible that multiple todos are assigned to the same employee. So multiple todos can be assigned to the same employee AND are overdue. That's where the problem starts...

So what I did, is grabbing all the overdue tasks from the database and then I grabbed the assigned employees to the tasks. I created an array that consists of the todoID, the employeeID and the employeeEmail. Now, is there a better way to do this and if not, how can I group the rows by email address?

The end result should be an array that shows every overdue todo that's assigned to one employee.

// Get all Todos that are not archived

$sql = "SELECT * FROM todo WHERE archiv = 0";
$abfrage = $db->prepare($sql);
$abfrage->execute();

$overdue_array = array();

// Now we get everything that's overdue from the Database
while ($row = $abfrage->fetch()) {

    if ($row["status"] !== 3) {
        if ($row["archiv"] !== 1) {
            if ($row["durchfuehrung"]) {
                if (strtotime($row["durchfuehrung"]) < strtotime(date("Y-m-d"))) {
                
                    // Here we now get the email from the assiged employee to the todo
                    
                    $sql2 = "SELECT email FROM mitarbeiter WHERE mitarbeiterID = :mitarbeiterFID";
                    $abfrage2 = $db->prepare($sql2);
                    $abfrage2->bindParam("mitarbeiterFID", $row["mitarbeiterFID"]);
                    $abfrage2->execute();

                    while ($row2 = $abfrage2->fetch()) {
                        $overdue_array[] = array("todoID" => $row["todoID"], "mitarbeiterID" => $row["mitarbeiterFID"], "mitarbeiterEmail" => $row2["email"]);
                    }
                }
            }
        }
    }

The result is the following:

SOLUTION:

We improved the SQL statement by using a JOIN and putting all if statements into the statement. Then we added the foreach provided by @lagaart and were able to send the mails. Here is the final code:

$sql = "SELECT todoID, mitarbeiterFID, email, status, archiv, durchfuehrung FROM todo INNER JOIN mitarbeiter ON mitarbeiterID = mitarbeiterFID
WHERE archiv = 0 AND status != 3 AND durchfuehrung < CURRENT_DATE";
$abfrage = $db->query($sql);
$overdue_array = $abfrage->fetchAll();

foreach ($overdue_array as $todo) {

    if (!isset($newArray[$todo["email"]])) {

        // Here we create a new array with the email as a key and put the two first key-values in it with array_slice
        $newArray[$todo["email"]] = [array_slice($todo, 0, 2)];

    } else {

        // Here we push another todo if the email is already declared as a key
        array_push($newArray[$todo["email"]], array_slice($todo, 0, 2));

    }

}

$sql = "SELECT email FROM mitarbeiter";
$abfrage = $db->query($sql);

while ($row = $abfrage->fetch()) {
    if (isset($newArray["$row[email]"])) {
        sendSummary($row["email"],$newArray["$row[email]"], $company, $db);

    }
}

CodePudding user response：

You could strongly improve your request with a JOIN (as said @mickmackusa) and even structure your response with PDO Fetch Statements (like PDO FETCH_GROUP or PDO FETCH ASSOC). You could directly get the result you want with one (bigger but better) request.

Nevertheless, if you want to simply sort your array with PHP, the use of foreach can do the job.

foreach ($array as $todo) {

    if (!isset($newArray[$todo["mitarbeiterEmail"]])) {

        // Here we create a new array with the email as a key and put the two first key-values in it with array_slice
        $newArray[$todo["mitarbeiterEmail"]] = [array_slice($todo, 0, 2)];

    } else {

        // Here we push another todo if the email is already declared as a key
        array_push($newArray[$todo["mitarbeiterEmail"]], array_slice($todo, 0, 2));

    }

}

// Check your new array
print_r($newArray);

You could also avoid foreach by combining array_multisort to sort by emails then array_reduce to remove duplicate elements keeping associated data. Many solutions can be proposed.

CodePudding user response：

Let's pretty up your scripting with some best practices...

• only add columns to your SELECT clause when you have a use for them
• enjoy PDO's very handy fetching modes -- FETCH_GROUP is perfect for your case.
• always endeavor to minimize trips to the database
• always endeavor to minimize the number of loops that you use.

Recommended code (yes, it is just that simple):

$sql = "SELECT email, todoID, mitarbeiterFID
        FROM todo 
        JOIN mitarbeiter ON mitarbeiterID = mitarbeiterFID
        WHERE archiv = 0
          AND status != 3
          AND durchfuehrung < CURRENT_DATE";
foreach ($db->query($sql)->fetchAll(PDO::FETCH_GROUP) as $email => $rows) { 
    sendSummary($email, $rows, $company, $db);
}

For the record, I don't know where $company comes from.