I have a query that lists organisations based on the choice of activities/areas of expertise a user makes in a form. Each organisation can do one or many areas of expertise/activities.
My query echoes the list of organisations relating to the choices the user makes fine but it fails when echoing the list of areas of expertise/activities under each organisation. My output should be something like
organisation name a
activity x
activity y
expertise a
expertise b
instead the query lists the organisations but doesn't echo the activities/expertise' under each one. here is my query so far
<?php
//display list of organisations that doe support activities chosen in form
$sql1 = "SELECT supportactivity.supportactivity_id AS supportactivity_id, supportactivity.supportactivity AS supportactivity,
supportactivityuser.username AS username, qualityadmin.quality_name AS quality_name,
qualityadmin.qualityadmin_id AS qualityadmin_id
FROM supportactivity
LEFT JOIN supportactivityrelations ON supportactivity.supportactivity_id = supportactivityrelations.supportactivity_id
LEFT JOIN supportactivityuser ON supportactivity.supportactivity_id = supportactivityuser.supportactivity_id
LEFT JOIN qualityadmin ON supportactivityrelations.quality_name = qualityadmin.quality_name
WHERE supportactivityuser.username = '$username' ORDER BY quality_name";
$results1 = mysql_query($sql1);
while ($row = mysql_fetch_array ($results1)){
$name1 = $row["quality_name"];
echo '<a href="info_details.php?quality_name='. urlencode($row[3]) .'">'. $row[3] .'</a><br><br>';
}
//display list of support activities relating to each organisation
$sql2 = "SELECT supportactivity.supportactivity_id AS supportactivity_id, supportactivity.supportactivity AS supportactivity,
qualityadmin.quality_name AS quality_name, qualityadmin.qualityadmin_id AS qualityadmin_id
FROM supportactivity
LEFT JOIN supportactivityrelations ON supportactivity.supportactivity_id = supportactivityrelations.supportactivity_id
LEFT JOIN supportactivityuser ON supportactivity.supportactivity_id = supportactivityuser.supportactivity_id
LEFT JOIN qualityadmin ON supportactivityrelations.quality_name = qualityadmin.quality_name
WHERE supportactivityrelations.quality_name = '$name1'";
$results2 = mysql_query($sql2);
while ($row = mysql_fetch_array ($results2)){
$supportactivity = $row["supportactivity"];
echo "<br>$supportactivity";
}
//display list of organisations that do areas of expertise chosen in form
$sql2 = "SELECT expert.expert_id AS expert_id, expert.expert AS expert,
expertuser.username AS username, qualityadmin.quality_name AS quality_name,
qualityadmin.qualityadmin_id AS qualiyadmin_id
FROM expert
LEFT JOIN expertrelations ON expert.expert_id = expertrelations.expert_id
LEFT JOIN expertuser ON expert.expert_id = expertuser.expert_id
LEFT JOIN qualityadmin ON expertrelations.quality_name = qualityadmin.quality_name
WHERE expertuser.username = '$username'";
$results2 = mysql_query($sql2);
while ($row2 = mysql_fetch_array ($results2)){
$name2 = $row2["quality_name"];
echo '<a href="info_details.php?quality_name='. urlencode($row[3]) .'">'. $row[3] .'</a><br><br>';
}
//display areas of expertise relating to each organisation
$sql_expert = "SELECT expert.expert_id AS expert_id, expert.expert AS expert,
qualityadmin.quality_name AS quality_name, qualityadmin.qualityadmin_id AS qualiyadmin_id
FROM expert
LEFT JOIN expertrelations ON expert.expert_id = expertrelations.expert_id
LEFT JOIN expertuser ON expert.expert_id = expertuser.expert_id
LEFT JOIN qualityadmin ON expertrelations.quality_name = qualityadmin.quality_name
WHERE expertrelations.quality_name = '$name1'";
$set3 = mysql_query($sql_expert);
while ($row = mysql_fetch_array ($set3)){
$name4 = $row["expert"];
echo "<br>$name4";
}
?>
Ive tried fixing this by having a while loop x inside another while loop y where while loop x is the list of organisations outputted and while list y is the lis tof activities/areas of expertise but when I ran this I get zero results.
any help would be greatly received
here is the form for reference purposes
http://www.mindseyemidlands.co.uk/notts_quality/info_resource/sourcesofhelp_page1.php
excuse the large gap when you click submit- scroll down and you should see the results of the query. At the moment the organisations are repeated as I have not included an update procedure when the choices are inserted into the database.
