Jump to content

Link tables


adzie

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.