adzie Posted March 13, 2011 Share Posted March 13, 2011 Hi guys Long time no post. Firstly is this possible, and can anyone point me in the right direction. I have two tables as below and I want to display only the results that are active against the master table. Table1 - Awards awardid award code active 1 Apple AFC 1 2 Orange OOC 1 3 Lemon LSO 1 Table 2 - Log userid AFC OOC LSO 1 1 0 0 2 1 1 1 The problem i'm facing is I can get the results from table 1 where active = 1 which is great and then return each row. however I only want to return the row where the user has that award for example user 1 only has the AFC. Any guidance as always appreciated. Many thanks Quote Link to comment Share on other sites More sharing options...
HuggieBear Posted March 13, 2011 Share Posted March 13, 2011 Can you change the database structure? If so, that would be better. awards awardid award code active userawards userid awardid You have a row in the userawards table for each award that each user has. Then a simple join could retrieve the results you're after. If you can't change the structure then it can still be done, but changing the structure would be better. Quote Link to comment Share on other sites More sharing options...
adzie Posted March 13, 2011 Author Share Posted March 13, 2011 Thats similar to how I had the original. but I really wanted to keep the user ids awards on one row rather than multiple rows. Quote Link to comment Share on other sites More sharing options...
cunoodle2 Posted March 13, 2011 Share Posted March 13, 2011 I'm a little unclear on exactly what it is that you are looking for. Can you post the non working query you have thus far and/or what your desired output would be? Quote Link to comment Share on other sites More sharing options...
HuggieBear Posted March 13, 2011 Share Posted March 13, 2011 It's not very good database design. What's the reason for not wanting multiple rows for each user? Like I said, it can be done the way you want but I wouldn't advise it: // Query the database for active awards $res = mysql_query("SELECT code FROM awards WHERE active = '1'"); // Populate the codes array $codes = array(); while ($row = mysql_fetch_row($res)){ $codes[] = $row[0]; } // Hardcoded userid, yours probably comes from a form or something $userid = '1'; // Query the database for user awards $res = mysql_query("SELECT * FROM log WHERE userid = '$userid'"); // Print the award details (I'm assuming this will only be a single row) $row = mysql_fetch_assoc($res); foreach ($codes as $award){ if ($row[$award] == 1){ echo $award . "<br />\n"; } } This is untested, but I expect it to be pretty close to the mark. Quote Link to comment Share on other sites More sharing options...
adzie Posted March 14, 2011 Author Share Posted March 14, 2011 Brilliant!! Definetly moving towards the right Direction, Thank you HuggieBear. Rather simple compared to the 100 + lines I had for a similar result. What I had done previously was to search for each award where it was active and the for each row check against the table which is rubbish and very messy. Would it be possible though using the example supplied above to give each result an ID or number, not the id in the database, but an id so result 1,2,3 etc so if I have two awards only id 1 and id 2 but if I have 4 id1, id2, id3, id4. The reason is I want to place images next to each other but after image5 I want to place it on another row, so need the id number to work with the spacing code I have. Then the next problem is rather than printing the title I would need to print the award from the top table ie orange to select the correct image. Once again thanks for your help Quote Link to comment Share on other sites More sharing options...
HuggieBear Posted March 14, 2011 Share Posted March 14, 2011 Try this for returning the award // Query the database for active awards $res = mysql_query("SELECT code, award FROM awards WHERE active = '1'"); // Populate the codes array $codes = array(); while ($row = mysql_fetch_assoc($res)){ $codes[$row['code']] = $row['award']; } // Hardcoded userid, yours probably comes from a form or something $userid = '1'; // Query the database for user awards $res = mysql_query("SELECT * FROM log WHERE userid = '$userid'"); // Print the award details (I'm assuming this will only be a single row) $row = mysql_fetch_assoc($res); foreach ($codes as $code => $award){ if ($row[$code] == 1){ echo $award . "<br />\n"; } } As for the id, you can just use a simple count variable. Do you need the id to output as an HTML id, or do you just need it for layout of the awards? Quote Link to comment Share on other sites More sharing options...
adzie Posted March 14, 2011 Author Share Posted March 14, 2011 Excellent, thats certainly giving me the result I want, and I assume I can put more fields into the result should I need to? foreach ($codes as $code => $award) I've now got the award code showing which is what I need for the image to display. As I said before ideally each result will have an id, not a html one but a result if you like, my previous code was something like below. $a being horizontal position and $b being vertical position. Like I said before I want to display the first 4 results on one row and the next 5 on the second row and then next 5 on the third row etc. Potentially upto 20+ awards. Hope that makes sense. So the code below achieved that but with the new code i'm not sure how to implement a potential count as suggested. Before even if the award wasn't in the log table it still created an empty space the new code you have given me stops that but obviously I need a way of counting each result so that I can then space it correctly. Thanks // now to the results if ($number > 0) { // for each award result for ($i=0; $i<$number; $i++) { // Set the layout of the awards if ($i <=3 ) { $as = $i + 1; $as2 = $as * 50; $a = $as2 + 20; $b = 40; } elseif ($i >= 4 and $i <= { $as = $i -4; $as2 = $as * 50; $a = $as2 + 20; $b = 55; } Quote Link to comment Share on other sites More sharing options...
adzie Posted March 14, 2011 Author Share Posted March 14, 2011 Thoughts anyone? Quote Link to comment Share on other sites More sharing options...
HuggieBear Posted March 15, 2011 Share Posted March 15, 2011 You can still use the same principle, just a variable to keep count. Quote Link to comment Share on other sites More sharing options...
adzie Posted March 15, 2011 Author Share Posted March 15, 2011 Thanks Don't have a link to an example that would work with the above, keen to learn this and not just take your work although the examples are a bonus Quote Link to comment Share on other sites More sharing options...
adzie Posted March 23, 2011 Author Share Posted March 23, 2011 Sorry for bringing this back to top again, does anyone have an example or suitable tutorial that would work with the examples HuggieBear gave a few posts back? Many thanks Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.