Occasionally when working with database relationships one will come across an instance of a many-to-many relationship. This type of relationship can occur, for example, when multiple users can be assigned to multiple tasks with users being assigned multiple times. This type of relationship should be implemented as three separate tables as shown below.
Users
| userid |
username |
phone |
… |
| 1 |
me |
867-5309 |
… |
| 2 |
you |
555-5555 |
… |
Tasks
| taskid |
name |
description |
… |
| 1 |
Blog |
Write a blog entry and post it on the T&S Blog. |
… |
| 2 |
Create a new website. |
One person design the site. The other person write the html/css for the site. |
… |
Taskassignments
| assignmentid |
taskid |
userid |
| 1 |
1 |
1 |
| 2 |
2 |
1 |
| 3 |
2 |
2 |
Now, it is possible to join these tables in a single SQL statement. However, the resulting set would most likely contain many duplicate entries. There is a way in mysql to get the entries in two SQL statements without duplicating even a single entry. This is done using mysql’s GROUP_CONCAT function. This function is used to return many entries that relate to one entry on a single row. For example:
SELECT userid, GROUP_CONCAT(taskid) tasks FROM Taskassignments GROUP BY userid
would return
The next step from here would be to change the SQL statement so that it joins with the “Users” table.
SELECT userinfo.userid, tasks, username FROM Users userinfo INNER JOIN (SELECT userid, GROUP_CONCAT(taskid) tasks FROM Taskassignments GROUP BY userid) tasksassigned ON userinfo.userid = tasksassigned.userid
This retrieves the username instead of just the userid. (Now the user can be referred to as “Bob” rather than “User 12″.)
Now that the users have been retrieved from the database, all the associated tasks must be retrieved without duplicating entries. How can this be achieved? All the task ids must be parsed and stored so they can be retrieved from the database. In php this is simply a few lines of code:
$ids = array();
while ($row = mysql_fetch_assoc($result1)) {
foreach (explode(',', $row['tasks']) as $id) {
if (!in_array($id, $ids))
$ids[] = $id;
}
}
Lastly, all the tasks are retrieved from the “Tasks” table.
if (count($ids) > 0)
$result2 = mysql_query('SELECT * FROM Tasks WHERE taskid = '.implode(' OR taskid = ', $ids);
And the tasks are correlated to the proper users, so the results can be displayed.
$tasks = array();
if (isset($result2))
while ($row = mysql_fetch_assoc($result2))
$tasks[$row['taskid']] = $row['name'];
mysql_data_seek($result1, 0);
while ($row = mysql_fetch_assoc($result1)) {
echo htmlspecialchars($row['username']).' has the following tasks:
<ul>';
foreach (explode(',', $row['tasks']) as $taskid)
echo '<li>'.htmlspecialchars($tasks[$taskid]).'</li>';
echo '</ul>';
}
Recently I scoured the web to come up with some website resources that could be useful for small business owners. While I’ve personally written on a lot of these topics in the past, it’s good to get a third-party point of view. As typically happens, I found others had already come up with great lists.
