T&S Web Design

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

Tasks

Taskassignments

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>';
}