0

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:

TBL1:
id    time    timeframe   Mon     Tues    Wed     Thur    Fri     Sat     Sun
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

TBL2:
id    Name    Color
1     Name1   #hexcolor
2     Name2   #hexcolor2
3     Name3   #hexcolor3
ect


    $query = array('select' => "*", 
               'tbl' => "Schedule");
$query2 = array('select' => "name, color", 
               'tbl' => "dj_colors");

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

EDIT* This is what I've come up with and trying, but I think I am still missing something as its still not working like I want it to. I went a head and gave the names of the actual tables that I'm using in proper format. Still not pulling colors, but I'm not getting any errors. Please bear with me, I am still learning. I'm not asking for the answer, just where to look and some helpful tips.

Chad
  • 1
  • 2

1 Answers1

0

I think you need to learn about joins. You can return the colors in the same query as the names, by joining them in:

select t1.*, mon.color, tue.color, . . . sun.color
from table1 t1 join
     table2 mon
     on t1.name = mon.name join
     tale2 tue
     on t1.tue = tue.name
     . . .
     table2 sun
     on t1.sun = sun.name

The expression:

SELECT color FROM tbl2 WHERE name

Will probably return no rows. With no comparison, name will be converted to an int, based on the initial digits at the front. If it starts with an alpha character, it will be transformed to 0, which is false, so no rows are returned.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I told you it was probably something basic. I'm teaching myself php at the same time of writing code. Off to learn the join functions. Thank you very much. – Chad Mar 31 '13 at 14:37
  • @Chad . . . I don't see a from clause in your revised question. – Gordon Linoff Mar 31 '13 at 16:09
  • I fixed the format to show all the code, I wasnt paying attention yesterday when editing my post. – Chad Apr 01 '13 at 13:26
  • @Chad . . . I think you want to give the different colors different aliases: `Monday.color as MondayColor, Tuesday.color as TuesdayColor, . . . `. MySQL likes for columns to have different names. – Gordon Linoff Apr 01 '13 at 13:30
  • The days are not supposed to be in colors, only the names for the staff member. Like, I have a staffer that works Monday 1a-3a and Thursday 4p-7p. I want his name to show up in a specific color, regardless of where he actually is in the schedule. – Chad Apr 01 '13 at 13:35