Thanks for asking.
My Array returns a row for each band (in table4) that is included in an event (in table2) that is a scheduled event( in table1) - Here is the simplified table structure (There are others tables involved, but those relationships are simple and not a problem:
Table1 (Schedule Dates):
ID | TABLE2_ID | DATE
Table2 (Events):
ID | EVENT NAME |
Table3 (Event/Bands Lookup table):
TABLE2_ID | Y/N FLAG | ORDER | TABLE4_ID
Table4 (Bands):
ID | BAND NAME
Array (
[0] => 1
[ID] => 1 (the id of a schedule entry in table 1 - Primary Key)
[1] => 45
[TABLE2_ID] => 45 (the id of the event that is scheduled)
[2] => 2007-02-15
[DATE] => 2007-02-15
[3] => 45
[TABLE2.ID] => 45 (the id of an event in table 2 - Primary Key)
[4] => Event Name
[EVENT NAME] => Event Name
[5] => 45
[TABLE2_ID] => 45 (the id of an event in the lookup table)
[6] => Y
[Y/N FLAG] => Y
[7] => 1
[ORDER] => 1
[8] => 186
[TABLE4_ID] => 186 (The id of the band in the lookup table)
[9] => 186
[TABLE4.ID] => 186 (The id of the band in table4)
[10] => Rolling Stones
[BAND NAME] => Rolling Stones
)
So a loop looks like this (with id's omitted):
DATE | EVENT | BAND
2007-04-15 | Party on the Patio | ZZ Top (Flagged Y, Order 1)
2007-04-15 | Party on the Patio | Rolling Stones (Flagged Y, Order 2)
2007-04-15 | Party on the Patio | Led Zeppelin (Flagged N, Order 1)
2007-04-15 | Party on the Patio | The Police (Flagged N, Order 2)
I want to be able to do this:
2007-04-15 Party on the Patio
featuring ZZ Top and the Rolling Stones with Led Zeppelin and The Police
WHICH, I can do by placing them in variables:
$DATE . $EVENT_NAME
featuring $BANDY1 and $BANDY2 with $BANDN1 and $BANDN2
The Problem comes when there is no $BANDN2 for an event.
$BANDN2 was already given a value in the previous loop so it will always be The Police until it is told to be something else.
Can't use
if(isset($sql['band name']) { $BANDN2 = $sql['band name']; } else { $BANDN2 = 0; }
or if(!empty) because it is always set and always has a value.
The loop I was trying to use to assign the vars looks like this:
$num_bands = 0;
foreach($array as $array_band) {
$Band = $array['Band Name'];
if (($array['FLAG'] == 'Y') && ($array['Order'] == '1')) :
$BANDY1 = $Band ;
elseif (($array['FLAG'] == 'Y') && ($array['Order'] == '2')) :
$BANDY2 = $Band ;
elseif (($array['FLAG'] == 'Y') && ($array['Order'] == '3')) :
$BANDY3 = $Band ;
elseif (($array['FLAG'] == 'Y') && ($array['Order'] == '4')) :
$BANDY4 = $Band ;
elseif (($array['FLAG'] == 'Y') && ($array['Order'] == '5')) :
$BANDY5 = $Band ;
elseif (($array['FLAG'] == 'N') && ($array['Order'] == '1')) :
$BANDN1 = $Band ;
elseif (($array['FLAG'] == 'N') && ($array['Order'] == '2')) :
$BANDN2 = $Band ;
elseif (($array['FLAG'] == 'N') && ($array['Order'] == '3')) :
$BANDN3 = $Band ;
elseif (($array['FLAG'] == 'N') && ($array['Order'] == '4')) :
$BANDN4 = $Band ;
elseif (($array['FLAG'] == 'N') && ($array['Order'] == '5')) :
$BANDN5 = $Band ;
endif;
$num_bands = $num_bands + 1;
}
I'm not experienced enough to know whether to change the structure of my tables, my query, or the code I use to extract the variables - or all three!
Here's the simplified query again:
$schedule = @mysql_query("SELECT id, date FROM table1 ORDER BY date");
while ($row = @mysql_fetch_array($schedule)) {
$id = $row['id'];
$array = @mysql_query(
"SELECT
table1.id,
table1.table2_id,
table1.Date,
table2.id AS table2id,
table2.EventName,
table3.table2_id AS table_2_id,
table3.Flag
table3.Order,
table3.table4_id,
table4.id AS table4id,
table4.band_name
FROM table1
LEFT JOIN table2 ON table1.table2_id = table2.id
LEFT JOIN table3 ON table2.id = table3.table2_id
LEFT JOIN table4 ON table3.table4_id = table4.id
WHERE table1.id ='$id'
ORDER BY Flag, Order"
)
Thanks for checking it out.
