What would be the best way to print out records with sub-records attached to them? Suppose I have 2 tables, something like this:
User table:
[table=“width: 500, class: grid, align: center”]
[tr]
[td]User_id[/td]
[td]User_name[/td]
[/tr]
[tr]
[td]1[/td]
[td]John[/td]
[/tr]
[tr]
[td]2[/td]
[td]Mick[/td]
[/tr]
[/table]
Movie table:
[table=“width: 500, class: grid, align: center”]
[tr]
[td]User_id[/td]
[td]Movie_name[/td]
[td]Rating[/td]
[/tr]
[tr]
[td]1[/td]
[td]Die Hard[/td]
[td]5[/td]
[/tr]
[tr]
[td]1[/td]
[td]MIB[/td]
[td]8[/td]
[/tr]
[tr]
[td]2[/td]
[td]Iron Man[/td]
[td]6[/td]
[/tr]
[/table]
What I want to get, is this:
John:
- Die Hard (5)
- MIB (8)
Mick:
- Iron man (6)
Should I do an inner join and process the query result using, for example, PHP or is there a better way to organize the result with the help of mysql?