Can I Use UNIONS For This?

[thebigtymer]

I have MYSQL 4.0 now, so I want to see if I can cut down this script and reduce two queries to one. I haven't been able to find a good UNION tutorial, much less a way to display the results in PHP. Here is the script:

*EDIT* SCRIPT CLEANED UP, CHECK BELOW *EDIT*

Can UNIONS be used for this one? If so, how? Here's how the data is set up:

DJID name info
1 name info

ID StartTime EndTime Mon Tue Wed Thu Fri Sat Sun DJID
1 00:00:00 06:00:00 1 1 1 1 1 1 1 1

Thanks in advance.

[r937]

i see three queries -- the first selects a specific dj, the second selects all djs, and the third selects some stuff for a specific dj

you can definitely "combine" the first two by throwing the first query away, and simply processing the desired dj in the loop that processes all djs (assuming you actually do process all djs -- i didn't read your php code -- so if all you want is one dj, then throw the second query away)

as for combining the first with the third, that's not a union, that's a join, and yes, you could/should do it

[thebigtymer]

I've cleaned up the code, and you can see it below:

// global definitions
$day_name=array('Mon'=>'Monday','Tue'=>'Tuesday','Wed'=>'Wednesday','Thu'=>'Thursday','Fri'=>'Friday','Sat'=>'Saturday','Sun'=>'Sunday');
$day_value=array('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday');
$link_id=mysql_connect("$db_host","$db_login","$db_password" );
$link_id2=mysql_connect("$db_host","$db_login","$db_password" );
mysql_select_db($db_name,$link_id2);
$query2="select * from $db_dj order by priority ASC;";
$result2=mysql_query($query2,$link_id2);
while ($res2=mysql_fetch_array($result2)) {
// display DJ info
echo "<br>${res2['name']}<br>${res2['info']}<br>";
// display airtimes for that DJ
mysql_select_db($db_name,$link_id);
$query="select * from $db_onair where dj_id=${res2['dj_id']} AND (start_time != '00:00:00' OR start_display = '12:00 AM') ORDER BY Mon DESC,Tue DESC,Wed DESC,Thu DESC,Fri DESC,Sat DESC,Sun DESC,start_time ASC;"; 
$result=mysql_query($query,$link_id);
while ($res=mysql_fetch_array($result)) {
// initialize shift info
$weekday=array();
// get data
$start_time=$res['start_display'];
$end_time=$res['end_display'];
$weekday[]=$res['Mon'];
$weekday[]=$res['Tue'];
$weekday[]=$res['Wed'];
$weekday[]=$res['Thu'];
$weekday[]=$res['Fri'];
$weekday[]=$res['Sat'];
$weekday[]=$res['Sun'];
// initialize shift info
$j=-1;
$answer="";
// loop through days, build answer
while ($j<7) {
$i=$j+1;
while (($i<7) and ($weekday[$i]==0)) $i++; // found first day it's 1, or found end-of-week
if ($i<7) { // else done
$j=$i; // memo
while (($i<7) and ($weekday[$i]==1)) $i++; // found last day it's 1 ($i-1) or Sunday passed ($i=7)
if ($i<7) { // range finished before Sunday
$i--;
$answer.=", ".$day_value[$j].(($i>$j)?" - ".$day_value[$i]:''); // we will strip first comma afterwards
if (!isset($stripfirst)) $stripfirst=TRUE;
$j=$i;
} else { // range finished positively on Sunday
$answer.=', Sunday';
$j=$i;
} // range analysis
} else { // range finished negatively on Sunday
$j=$i;
} // range analysis
} // while week not fully analysed for that shift found
// eventually fix
if (isset($stripfirst))
if ($stripfirst) $answer=substr($answer,2); // else NOP
// finally display
echo "$answer : $start_time - $end_time<BR>";
} // while Shifts found
} // while DJs 

I'm pretty sure of the join I need. However, I'm not sure how to print them without doing the second query and the loop for it. That's where I'm stuck. You can see what I'm getting at with http://www.thebigtymer.com/djtest.php