Group multiples rows of result set data by a column value

Question

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:

What is the format of the `durchfuehrung` column values? You should not be executing iterated queries on your database. Instead JOIN related data and make just one trip to the db. You don't need a prepared statement if there are no bound parameters. When performing looped executions of a prepared statement, only declare the prepared statement once. — mickmackusa, Oct 23 '21 at 16:10
That's a good point. Will use a JOIN here. durchfuehrung is just a date column. We compare the date in the database with the current date to see if it's overdue or not — chrisi0801, Oct 23 '21 at 16:17
I am asking about what the date format is. Is it already (properly) `Y-m-d`? If so, just compare it against `date('Y-m-d')` because both can be compared as simple strings. I explain this reasoning on this different page https://stackoverflow.com/a/67124505/2943403 — mickmackusa, Oct 23 '21 at 21:54
Ultimately, this task should be completely resolved using sql. When your sql is perfected, you should only need to call mysqli's `fetch_all()`. We need better details as part of your [mcve]. Please create a small sql fiddle including sample data, share the link, and also express your exact desired result set. — mickmackusa, Oct 23 '21 at 22:01
The problem is solved. See the post edit. And yes the date format was already Y-m-d. But problem solved and thank's for your tips with the statement. Really appreciate your help — chrisi0801, Oct 24 '21 at 06:55
Where does `$company` magically come from? Your snippet can still be further refined. I wish you would provide an sql fiddle with sample data and explain how many emails should be sent based on the sample data. — mickmackusa, Oct 24 '21 at 10:38

score 0 · Answer 1 · answered Oct 23 '21 at 18:08

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.

Thank you so much @lagaart for your help. I improved the SQL statement, added Joins and the foreach from you and boom I was able to send the mails. If anyone wants to see details on what I changed, I edited my post. — chrisi0801, Oct 24 '21 at 06:49

mickmackusa · Accepted Answer · 2021-10-24T12:52:38.850

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.

Yes this also solves the problem. Thank you so much for these great tips. $company is a variable that's used to create a dynamic link to the todo. But has nothing to do with the problem. — chrisi0801, Oct 24 '21 at 15:02

Group multiples rows of result set data by a column value

2 Answers2