Limit results in linked queries

[adzie]

Hello all,

 

I've inherited a rather awkward table that cannot be changed and so I need some help to limit the results.

 

Table one - member_awards

member_id

then a column for each award code

 

Table two - awards

award_id

award_desc

 

$awardCodes = mysql_query("SELECT * FROM `member_awards` WHERE `member_id` = 1");	
$awardsres = mysql_fetch_assoc($awardCodes);

$awards = mysql_query("SELECT * FROM `awards` ORDER BY 'award_id' ASC");	

while ($ac2 = mysql_fetch_assoc($awards)) {	
$ac22 = db_entry_check($awardsres[$ac2[award_id]])? 1 : 0;	
if ($ac22 != $actual && $ac22 == 1) {

// DO something

}

}

 

 

Table 1 has 20 columns, 0 if they don't have the award 1 if they do.  I need to pick out 5 where they do have the award ie the column result is 1.  This is where i'm getting stuck.  Any points would be appreciated

[AyKay47]

I don't quite understand your logic.

[adzie]

Ok ignore my code then.

 

Those two tables, how would I get the result to show which ones the member has and limit it to a maximum of 5

[cpd]

You could group by and then only show the first 5 for each. This however splits your logic between the MySQL Server and PHP; not the end of the world. If you wanted all the logic in the MySQL server you've got a painful query ahead of you...

[fenway]

Wait -- you just want to show the first 5 awards a given member has? Simple JOIN with LIMIT.

[adzie]

I don't think I was very clear before hand with the table structures

 

Table one - member_awards

member_id

then a column for each award_id(in table 2) - this will be 1 if they have the award 0 if they don't

 

Table two - awards

award_id

award_desc

 

 

[Muddy_Funster]

so

 

member_awards =

member_id | ac1 | ac2 | ac3 | ac4 | ac5 | ac6 | ac7 | ... | ac20 |

    1    |  1  |  0  |  1  |  0  |  1  |  1  |  1  | ... |  1  |

and awards =

award_id | award_description |

  ac1  | Biggest Muppet    |

  ac2  | Worlds Best God  |

  ac3  | Zealot of the Day |

...

 

 

and you want to display 5 awards per member relating the column name from one table to the field value in another to display "Jim has recieved : award1, award20, award5, award6, award7".  is that the basics of it?

[adzie]

Sorry for dragging this up, never saw the response

 

Muddy_Funster

 

 

Yes that in essence is it.