problem joining tables to get the specific output I'm trying to accomplish

[CLocke]

So I have been searching and doing trial and error for several days now, and Im turning to here for help.

I have created a page on my website that pulls a schedule information from a table on a database and displays it to the webpage. I am trying to pull a color scheme from a second table in the same DB, when the name on the first table matches the name on the second table, only that name will be displayed in the different color assigned to it. MY tables look as follows:

 

Schedule:

id   time   timeframe   Monday  Tuesday  Wednesday  Thursday   Friday    Saturday  Sunday
1   12a    00:00:00     Name1    Name2    Name3           Name2      Name5   Name6     Name7
2   1a      01:00:00     Name1    Name2    Name3           Name2      Name5   Name4     Name3
3   2a      02:00:00     Name1    Name2    Name4           Name2      Name2   Name6     Name2
4   3a      03:00:00     Name2    Name2    Name4           Name2      Name3   Name3     Name2

ect.

 

dj_colors:

TBL2:
id    name    color
1    Name1   #hexcolor
2    Name2   #hexcolor2
3    Name3   #hexcolor3
ect

 

  $query = array('select' => "*", 
               'tbl' => "Schedule");

$name = "select t1.*, Monday.color, Tuesday.color, Wednesday.color, Thursday.color, Friday.color, Saturday.color, Sunday.color
FROM Schedule t1
     JOIN dj_colors Monday
     ON t1.Monday = Monday.name,
     JOIN dj_colors Tuesday
     ON t1.Tuesday = Tuesday.name,
     JOIN dj_colors Wednesday
     ON t1.Wednesday = Wednesday.name,
     JOIN dj_colors Thursday
     ON t1.Thursday = Thursday.name,
     JOIN dj_colors Friday
     ON t1.Friday = Friday.name,
     JOIN dj_colors Saturday
     ON t1.Saturday = Saturday.name,
     JOIN dj_colors Sunday
     ON t1.Sunday = Sunday.name";

$DB = new DB();
$result = $DB->select_multi($query);

foreach ($result as $arrayLoop) {
    $printout .= "<tr>";
    foreach ($arrayLoop as $field => $data) {
        if ($field == 'id' || $field == 'TimeFrame') continue;
        if ($data === $name) $printout .= "<td color=".$color.">".$data."</td>"; 
        else $printout .= "<td class='schedule4'>".$data."</td>";   
    }
    $printout .= "</tr>";
}

 

I am pulling names from the Schedule db and posting them to a website. That part works fine. I am also trying to make it to where if the name in the field from db Schedule matches the name on db dj_colors, that name will be displayed in the color assigned to that name in dj_colors. I am at a loss here (I am still new to php coding and just learning the JOIN function). I am not wanting anyone to do it for me, but point me in the right direction anf give me some helpful pointers on where I am going wrong, please.

Edited April 1, 2013 by CLocke

[Psycho]

Can you show the format of your tables? Just looking at your query I think you have built a database design that is going to be difficult to work with and maintain.

 

EDIT: Also provide details on what the values are in "Monday", "Tuesday", etc. in the schedule table and the "name" field from the "colors" table.

Edited April 1, 2013 by Psycho

[CLocke]

The tables are formatted exactly as they are in the OP. there are 24 rows in table Schedule, 1 for each hour of the day.  the column headers are: id, Time, TimeFrame, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday. The number of rows on table dj_colors will vary with the number of staff, however the format on it is: id, name, color. I guess I'm not really understanding exactly what you want, sorry.

[Psycho]

A couple notes on your code:

 

 

        if ($field == 'id' || $field == 'TimeFrame') continue;

 

You should only query the fields you want to use. Also, I would write this code to explicitly work with the data instead of a generic loop. The reason why doing both of those (selecting all the records and making the processing code generic) is that it can lead to problems later on. Think about what would happen if someone needed to add a field to that table. It would be displayed in this output by default rather than a decision being made as to whether it should or not. From a security perspective, I think it is a bad idea to have code that would ever show any data by default if things were changed.

 

 

if ($data === $name) $printout .= "<td color=".$color.">".$data."</td>";
else $printout .= "<td class='schedule4'>".$data."</td>";

 

I don't see where $name is ever defined. So, I'm not sure what your expectation is on that line. But, I would expect that all records where $data does not equal $name would default to the else condition. Since $name is not changed in the loop, that would only ever occur with one set of records.

[CLocke]

So, I should change it to something like this:

	$query = array('select' => "Time, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday", 
				   'tbl' => "Schedule");

 

and then just loop for each id?

 

and then define:

$name = "select Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday FROM Schedule WHERE id='1'"

 

and then loop that for the different rows?

 

I really hope I'm not being too difficult. I asked someone for help, he wrote the code, didn't explain how or why he did it.

[Psycho]

I really hope I'm not being too difficult. I asked someone for help, he wrote the code, didn't explain how or why he did it.

 

Well, this forum is for people to get help with code they have written. Although, that doesn't mean we won't help people with code they were provided with, but the assumption is that the person requesting help has enough knowledge to communicate well on the subject at hand and can understand the code provided. If you didn't understand the code you were given you should have looked up the functions provided to understand what it was doing or ask the person who provided it.

 

Per your last response, I can't really say if that is what you "should" do. But, after rereading your original post again, I think the problem is in these two lines:

if ($data === $name) $printout .= "<td color=".$color.">".$data."</td>"; 
else $printout .= "<td class='schedule4'>".$data."</td>"; 

 

As I stated previously, you never defined $name (at least in the code you provided). But, what I *think* you want is IF the value is the color you want it to display as shown in the IF condition. But, look at your query and see what the field names will be for the color values - they will be Monday.color, Tuesday.color, etc. Looks like you were trying to have the IF condition be where the field was a "name" field - but again you never defined $name anyway. I really thing you should NOT try and make this too generic. Right now, your code relies upon the data being returned in a specific order (otherwise Tuesday would come before Monday). It is bad form, in my opinion, to create code that "assumes" data is returned in a specific order (there are exceptions). In this case you can write the code to specifically address the data.

 

Give this a try. Not tested, so there may be some syntax errors.

 

<?php

$query = "SELECT t1.time, ti.Monday, t1.Tuesday, t1.Wednesday, t1.Thursday, t1.Friday, t1.Saturday, t1.Sunday,
                Monday.color, Tuesday.color, Wednesday.color, Thursday.color, Friday.color, Saturday.color, Sunday.color
         FROM Schedule t1
         JOIN dj_colors Monday ON t1.Monday = Monday.name,
         JOIN dj_colors Tuesday ON t1.Tuesday = Tuesday.name,
         JOIN dj_colors Wednesday ON t1.Wednesday = Wednesday.name,
         JOIN dj_colors Thursday ON t1.Thursday = Thursday.name,
         JOIN dj_colors Friday ON t1.Friday = Friday.name,
         JOIN dj_colors Saturday ON t1.Saturday = Saturday.name,
         JOIN dj_colors Sunday ON t1.Sunday = Sunday.name";

$DB = new DB();
$result = $DB->select_multi($query);

$printout = '';
foreach ($result as $row)
{
    $printout .= "<tr>\n";
    $printout .= "<td class='schedule4'>{$row['time']}</td>\n";
    $printout .= "<td color=\"{$row['Monday.color']}\">{$row['Monday']}</td>\n";
    $printout .= "<td color=\"{$row['Tuesday.color']}\">{$row['Tuesday']}</td>\n";
    $printout .= "<td color=\"{$row['Wednesday.color']}\">{$row['Wednesday']}</td>\n";
    $printout .= "<td color=\"{$row['Thursday.color']}\">{$row['Thursday']}</td>\n";
    $printout .= "<td color=\"{$row['Friday.color']}\">{$row['Friday']}</td>\n";
    $printout .= "<td color=\"{$row['Saturday.color']}\">{$row['Saturday']}</td>\n";
    $printout .= "<td color=\"{$row['Sunday.color']}\">{$row['Sunday']}</td>\n";
    $printout .= "</tr>\n";
}

?>