i have 3 tables, sketched below. showid , actorid shows-actors table comes id column in shows , actors table. combine form composite primary key - actor can in many shows, , shows can have many different actors.
_______ _______ _______________ |actors | |shows| |shows-actors | --------- --------- -------------- |id|name| |id|name| |actorid|showid| --------- --------- --------------- |1 |bob | |1 |got | | 1 |1 | --------- --------- ---------------- |2 |lou| |2 |twd | | 1 |2 | --------- --------- ----------------
on 1 site page want display list of actors names, shows appear in. e.g. data above, page display: 1. bob. shows: got, twd. 2. lou. shows: ...etc.
currently have list of actors displaying correctly, i'm stuck on how i'd use composite pk list of shows each actor has appeared in. here have far, in terms of code relevant question.
$query = "select * `shows-actors` inner join `actors` on `actors`.`id` = `shows-actors`.`acid` order `actors`.`name` asc"; $result = mysqli_query($link, $query) or die(mysqli_error($link)); while($actor = mysqli_fetch_assoc($result)) { print '<tr>'; print '<td>'.$actor['name'].'</td>'; //insert shows actor appears in here print '</tr>'; }
all appreciated.
you join shows
table this:
select actors.`name`, shows.`name`, `shows-actors` inner join `actors` on `actors`.`id` = `shows-actors`.`acid` inner join shows on shows.id = `shows-actors`.showid order `actors`.`name` asc
Comments
Post a Comment