How to efficiently handle LEFT OUTER JOIN results with PHP / PDO
Long time to find the answer, first time here. I tried to find an answer to this question, but either it doesn't exist (doubtful) or I just can't come up with the correct terminology to search for (most likely).
I am using PHP / PDO to get data from MariaDB database. The database has tables with a many-to-many relationship and a table of connections between them:
people attendees events
+----+------+ +------+------+ +----+-------------------------+
| id | name | | p_id | e_id | | id | name |
+----+------+ +------+------+ +----+-------------------------+
| 1 | Bob | | 1 | 1 | | 1 | Company Christmas party |
| 2 | Anne | | 2 | 1 | | 2 | Christmas party cleanup |
| 3 | John | | 3 | 1 | | 3 | John birthday party |
+----+------+ | 3 | 3 | +----+-------------------------+
+------+------+
I am using LEFT OUTER JOIN to select events and their participants:
SELECT events.id, events.name, people.id, people.name
FROM events
LEFT OUTER JOIN attendees
ON events.id = attendees.e_id
LEFT OUTER JOIN people
ON attendees.p_id = people.id
Now the "problem" is that the query returns the cardinality of the same event for each participant as follows:
+----+-------------------------+------+------+
| id | name | id | name |
+----+-------------------------+------+------+
| 1 | Company Christmas party | 1 | Bob |
| 1 | Company Christmas party | 2 | Anne |
| 1 | Company Christmas party | 3 | John |
| 2 | Christmas party cleanup | NULL | NULL |
| 3 | John birthday party | 3 | John |
+----+-------------------------+------+------+
Now I am a bit obsessed with loops though results. I want to create event objects from results, but I want one event to be only as one object, adding participants to it.
I could loop through my array of existing event objects and check if the id exists when looking at the result set, but that seems a little inefficient. Is there a more elegant solution perhaps built into PDO?
source to share
I would do something like this:
-
Modify your query a bit (we now have different names for the event name and person name):
SELECT events.id, events.name as event, people.id, people.name as person FROM events LEFT OUTER JOIN attendees ON events.id = attendees.e_id LEFT OUTER JOIN people ON attendees.p_id = people.id
-
And parse the results into an array:
//$queryResult is the result returned by your query while($row = $queryResult->fetch(PDO::FETCH_ASSOC)) { $results[$row['event']][] = $row['person']; }
After all, you have an associative array that stores events and people.
print_r($results['Company Christmas party'])
will bring you back
Array
(
[0] => Array
(
[0] => Bob
[1] => Anne
[2] => John
)
)
In terms of efficiency, I don't see anything inefficient, linear complexity here.
source to share