I am trying to use PHP and MySQL to automatically generate a list like this.
Subject #1
- Resource #3
- Resource #5
- Resource #12
Subject #2
- Resource #7
- Resource #4
- Resource #1
etc.
Here are the tables:
eresources
- erid
- etitle
subjects
- sid
- stitle
subjectmap
- sid
- erid
Here is the code that successfully gives me all the subjects in alphabetical order:
$subjectQuery = "SELECT * FROM subjects WHERE sid != 17 ORDER BY stitle ASC";
$subjectResult = $mysqli->query($subjectQuery);
while ($subjectArray = $subjectResult->fetch_assoc()) {
print "<h5 class='subcategory'>" . $subjectArray['stitle'] . "</h5>";
print "<div class='inner'>
// Need a list of resources that match each subject ID (sid) here!
</div>";
}
Now, here is the code that successfully gives me all the e-resources with a fixed subject ID:
$getERBySubjectQuery = " SELECT erid FROM subjectmap WHERE sid=11 ";
$getERBySubjectResult = $mysqli->query($getERBySubjectQuery);
if($getERBySubjectResult && $getERBySubjectResult->num_rows >= 1){
while($getERBySubjectArray = $getERBySubjectResult->fetch_assoc() ){
$query = " SELECT * FROM eresources WHERE erid = " .$getERBySubjectArray['erid']. " ORDER BY ertitle ASC ";
$result = $mysqli->query($query);
if($result && $result->num_rows >= 1){
while($array = $result->fetch_assoc() ){
print("<a href=\"" . $array['link'] . "\">" . "<h5 class='subcategory'>" . $array['ertitle'] . "<div class='accessnote'>" . $array['access'] . "</div></h5></a>");
}}}}
Naturally, I am not inclined to duplicate the same stanza for each subject ID (sid). I want the list of subjects to be generated automatically and the list of resources that have that subject to be generated automatically as well.
Basically I need to feed the sid from the first stanza into the second stanza for each subject.