2

I need some assistance writing the sql select statement for a many to many database relationship.

I have the table1, table2, and table3 in mysql and want to output the bottom table in html. However, the relational structure is confusing me. Can you help me output the correct interests in the correct table cell? Currently, my sql select and fetch statement produce a table with multiple rows for each user, one with each interest.

enter image description here

I have tried every kind of join and array that I know to get this to output correctly. Can you explain what am I doing wrong? My current code is shown below:

$query = "SELECT *, table2.interest FROM table1
JOIN table3 ON table3.table1id = table1.id
JOIN table2 ON table3.table2id = table2.id
WHERE year=2017";

//Execute query
$qry_result = mysql_query($query) or die(mysql_error());

//Build Result String
$display_string = "<table border='1px'><tr><th>id</th><th>name</th>         <th>string</th><th>year</th><th>interest</th></tr>";
$display_string .= "<tr><td>$row[id]</td><td>$row[name]</td> <td>$row[string]</td><td>$row[year]</td><td>$row[interests] </td></tr>  </table>";

echo $display_string;
Ryanf
  • 187
  • 3
  • 9
  • If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. Note that `id` in table3 appears to be redundant. – Strawberry Mar 21 '15 at 19:07
  • 1
    See here: http://stackoverflow.com/questions/149772/how-to-use-group-by-to-concatenate-strings-in-mysql – Paul Griffin Mar 21 '15 at 19:13
  • It seems like you are looking for `GROUP_CONCAT` – Alvaro Montoro Mar 21 '15 at 19:15
  • I wouldn't use GROUP_CONCAT for this. A loop will suffice. – Strawberry Mar 21 '15 at 19:17
  • 1
    I'd probably do the presentation in php; just get all rows, order them by user and then group them while you loop over them. That would allow easy changes in the presentation as well (adding links to interests for example). – jeroen Mar 21 '15 at 19:22

1 Answers1

3

You sample output is not for just 2017, so remove the where. Then do a group by:

SELECT t1.*, GROUP_CONCAT(t2.interest SEPARATOR ', ') as interests
FROM table1 t1 JOIN
     table3 t3
     ON t3.table1id = t1.id JOIN
     table2 t2
     ON t3.table2id = t2.id
GROUP BY t1.id;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786