SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2002
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can I Use UNIONS For This?

    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.
    Last edited by thebigtymer; Jun 9, 2003 at 12:36.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Nov 2002
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've cleaned up the code, and you can see it below:
    PHP Code:
    // 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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •