SQL query question
I have a table of lodges and table of scores:
lodges
LodgeID (PK)
Lodge
etc
scores
ScoreID
Score
Question
LodgeID
For any given lodge, I have a page where the results can be viewed, with a list of all the categories.
I would like it only to display the categories where there are any scores (votes) in that category.
So I have a query WADAlodges on a page which refers to the lodges and scores tables for each category:
SELECT * FROM lodges INNER JOIN countries ON lodges.CountryID = countries.CountryID INNER JOIN scores ON lodges.LodgeID = scores.LodgeID WHERE lodges.LodgeID = %s OR ( -1= %s AND lodges.LodgeID= %s)
So it might return, for example,
LodgeID, Question
123, 1
123, 2
123, 3
etc
And I have some code to only display the text for each category, e.g.
<?php if ($row_WADAlodges['value']=="Yes" AND $row_WADAlodges['2015_Awards_Participant']=="Yes" AND $row_WADAlodges['Question']=="1"){ ?>
<?php if ($row_WADAlodges['value']=="Yes" AND $row_WADAlodges['2015_Awards_Participant']=="Yes" AND $row_WADAlodges['Question']=="2"){ ?>
<?php if ($row_WADAlodges['value']=="Yes" AND $row_WADAlodges['2015_Awards_Participant']=="Yes" AND $row_WADAlodges['Question']=="3"){ ?>
etc
But that only works for the first record in the query - how can I get it to work for other records for other scores?
Hope that makes sense, as I'm not sure I've explained it very well.
I have attached a copy of the page.
Thanks