SitePoint Sponsor

User Tag List

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

    Printing Results From a JOIN If Multiple Matches For A Group

    I have the code as follows:

    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

    // NB $link_id disposed and connection closed automatically
    ?> 
    You can see how it works here: http://www.thebigtymer.com/djtest.php. It basically runs a query of all DJ's, and it runs a query for each iteration of the loop to get airshifts. This could mean that there could be quite a lot of queries if there are a lot of DJ's. I want to use a join and reduce the queries to one.

    How do I echo the results if it's not a 1:1 ratio? Say 1 DJ has 2-3 airshifts. How do I print that? Thanks.

  2. #2
    SitePoint Zealot
    Join Date
    Nov 2002
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    bump

  3. #3
    SitePoint Zealot
    Join Date
    Nov 2002
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    bump

  4. #4
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the table layout, a small sample of data and a small example of what you envison for output would help.
    Jason Sweat ZCE - jsweat_php@yahoo.com
    Book: PHP Patterns
    Good Stuff: SimpleTest PHPUnit FireFox ADOdb YUI
    Detestable (adjective): software that isn't testable.

  5. #5
    SitePoint Zealot
    Join Date
    Nov 2002
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    CREATE TABLE DJs (
    dj_id int(11) NOT NULL auto_increment,
    name varchar(40) default NULL,
    info text,
    priority int(11) NOT NULL default '0',
    UNIQUE KEY DJID (dj_id)
    ) TYPE=MyISAM;

    CREATE TABLE OnAir (
    id int(11) NOT NULL auto_increment,
    start_time time default NULL,
    start_display char(8) default NULL,
    end_display char(8) default NULL,
    Mon smallint(6) default '0',
    Tue smallint(6) default '0',
    Wed smallint(6) default '0',
    Thu smallint(6) default '0',
    Fri smallint(6) default '0',
    Sat smallint(6) default '0',
    Sun smallint(6) default '0',
    dj_id int(11) NOT NULL default '0',
    UNIQUE KEY id (id)
    ) TYPE=MyISAM;

    INSERT INTO DJs VALUES (1, 'The BigTymer\'s B Morning Buzz', 'Droppin\' Dem Bombs All Morning', 1);
    INSERT INTO DJs VALUES (2, 'Michael Bryan', 'Doin\' Da Damn Thang During Your Lunch Hour', 2);
    INSERT INTO DJs VALUES (3, 'Danny Cruz', 'Bob Yo\' Head On Da Ride Home', 3);
    INSERT INTO DJs VALUES (4, 'Carson', 'Keepin\' It Glacier!', 4);
    INSERT INTO DJs VALUES (5, 'Charlie Scott', 'Smokin\' Pot All Night', 5);

    INSERT INTO OnAir VALUES (1, '05:30:00', '5:00 AM', '10:00 AM', 1, 1, 1, 1, 1, 0, 0, 1);
    INSERT INTO OnAir VALUES (2, '10:00:00', '10:00 AM', '3:00 PM', 1, 1, 1, 1, 1, 0, 0, 2);
    INSERT INTO OnAir VALUES (3, '15:00:00', '3:00 PM', '7:00 PM', 1, 1, 1, 1, 1, 0, 0, 3);
    INSERT INTO OnAir VALUES (4, '19:00:00', '7:00 PM', '12:00 AM', 1, 1, 1, 1, 1, 0, 0, 4);
    INSERT INTO OnAir VALUES (5, '00:00:00', '12:00 AM', '5:00 AM', 0, 1, 1, 1, 1, 0, 0, 5);
    INSERT INTO OnAir VALUES (12, '10:00:00', '10:00 AM', '2:00 PM', 0, 0, 0, 0, 0, 1, 0, 3);
    INSERT INTO OnAir VALUES (13, '14:00:00', '2:00 PM', '6:00 PM', 0, 0, 0, 0, 0, 1, 0, 4);
    INSERT INTO OnAir VALUES (14, '23:00:00', '11:00 PM', '1:00 AM', 0, 0, 0, 0, 0, 1, 0, 2);
    INSERT INTO OnAir VALUES (15, '00:00:00', '11:00 PM', '1:00 AM', 0, 0, 0, 0, 0, 0, 1, 2);
    INSERT INTO OnAir VALUES (16, '18:00:00', '6:00 PM', '11:00 PM', 0, 0, 0, 0, 0, 1, 0, 5);
    INSERT INTO OnAir VALUES (17, '17:00:00', '5:00 PM', '11:00 PM', 0, 0, 0, 0, 0, 0, 1, 5);

    I want it to look like http://www.thebigtymer.com/djtest.php, but with only 1 query instead of the code in the first post above. I just have no clue how to print the results if you get a result from the DJs table, and it has 2 or 3 matches in the OnAir table if you want to group them all together

  6. #6
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    select dj.*, oa.*
    from djs dj, onair oa
    where oa.dj_id = dj.id
    order by dj.priority asc
    should give you the data all at once, then loop over the result set, save the dj.name in a variable and output the dj info if it changes (then store the new value in the comparison var). Then use your date logic from the first post to print times.

    HTH
    Jason Sweat ZCE - jsweat_php@yahoo.com
    Book: PHP Patterns
    Good Stuff: SimpleTest PHPUnit FireFox ADOdb YUI
    Detestable (adjective): software that isn't testable.

  7. #7
    SitePoint Zealot
    Join Date
    Nov 2002
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you much, I got it now.


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
  •