adzie Posted April 7, 2012 Share Posted April 7, 2012 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 Link to comment https://forums.phpfreaks.com/topic/260514-limit-results-in-linked-queries/ Share on other sites More sharing options...
AyKay47 Posted April 7, 2012 Share Posted April 7, 2012 I don't quite understand your logic. Link to comment https://forums.phpfreaks.com/topic/260514-limit-results-in-linked-queries/#findComment-1335233 Share on other sites More sharing options...
adzie Posted April 7, 2012 Author Share Posted April 7, 2012 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 Link to comment https://forums.phpfreaks.com/topic/260514-limit-results-in-linked-queries/#findComment-1335240 Share on other sites More sharing options...
cpd Posted April 8, 2012 Share Posted April 8, 2012 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... Link to comment https://forums.phpfreaks.com/topic/260514-limit-results-in-linked-queries/#findComment-1335436 Share on other sites More sharing options...
fenway Posted April 9, 2012 Share Posted April 9, 2012 Wait -- you just want to show the first 5 awards a given member has? Simple JOIN with LIMIT. Link to comment https://forums.phpfreaks.com/topic/260514-limit-results-in-linked-queries/#findComment-1335522 Share on other sites More sharing options...
adzie Posted April 11, 2012 Author Share Posted April 11, 2012 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 Link to comment https://forums.phpfreaks.com/topic/260514-limit-results-in-linked-queries/#findComment-1336500 Share on other sites More sharing options...
Muddy_Funster Posted April 12, 2012 Share Posted April 12, 2012 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? Link to comment https://forums.phpfreaks.com/topic/260514-limit-results-in-linked-queries/#findComment-1336646 Share on other sites More sharing options...
adzie Posted May 31, 2012 Author Share Posted May 31, 2012 Sorry for dragging this up, never saw the response Muddy_Funster Yes that in essence is it. Link to comment https://forums.phpfreaks.com/topic/260514-limit-results-in-linked-queries/#findComment-1350059 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.