Link tables

[adzie]

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

[HuggieBear]

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.

[adzie]

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.

[cunoodle2]

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?

[HuggieBear]

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.

[adzie]

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

[HuggieBear]

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?

[adzie]

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;
}

[adzie]

Thoughts anyone?

[HuggieBear]

You can still use the same principle, just a variable to keep count.

[adzie]

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

[adzie]

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