MySQL query: need suggestions

chef · #1 19-Jan-2009, 20:32

Hi:
3 table below:
1.schedules

scheduleid
scheduletitle

2.resources

machid
scheduleid
name

3.reservations

resid
machid
scheduleid
start_date
end_date
summary

each "scheduletitle" has a one "scheduleid"
each "scheduleid" may have more then 10 "machid"
each "machid" may has booked, record in "reservations"

now i want to print one of scheduletitle, find the match in the "schedules.scheduleid", and after that need to match "schedules.scheduleid" and "resources.scheduleid", get all the "resources.machid" belong to the "schedules.scheduleid" and print out.

and i need to look up on the booking table "reservations", did any booking on the each "resouces.machid", if has booking, need to print out the "start_date" and "end_date" and "summary" with the "machid".

PHP Code:


<?php
$query = "SELECT * FROM  schedules, resources, reservations WHERE   schedules.scheduletitle = 'core-r-005'AND schedules.scheduleid=resources.scheduleid AND schedules.scheduleid = reservations.scheduleid AND reservations.machid = resources.machid";
                $result = mysql_query($query);
                $num_results = mysql_num_rows($result);
                
                for ($i=0; $i <$num_results; $i++)
                  {
                     $row = mysql_fetch_array($result);
                     extract ($row);
                     
                    $class = "tbl_white";
                    if ($i % 2 == 0) $class = "tbl_gray";
                    echo "<tr align='center' class= $class >";
                     
                    echo "<td width='10%'>" . $name . "</td>";
                    echo "<td width='20%'>" . date("m/d/y G.i:s", $start_date) . "</td>";
                    echo "<td width='20%'>" . date("m/d/y G.i:s", $end_date) . "</td>";
                    echo "<td width='150%'>" . $summary . "</td>";
                    echo "</tr>";
                    
     
     
     
                  }

i write code, this did print all the "resources.machid", only print the "reservations.machid".
but i need print all of them, even no booking.
can any please help me!!

admin · #2 19-Jan-2009, 21:34

I don't quite understand what you are looking for but I will suggest the following query:

Code:

SELECT
	*	
FROM
	`schedules` AS `S`
	LEFT JOIN `resources` AS `M`
		ON `M`.`scheduleid`=`S`.`scheduleid`
	LEFT JOIN `reservations` AS `R`
		ON `R`.`machid`=`M`.`machid`
WHERE
	`S`.`scheduletitle`='core-r-005'

chef · #3 19-Jan-2009, 23:26

thanks for help!!
i got the result work like that:

PHP Code:


$query = "SELECT
  schedules.scheduletitle, resources.name, reservations.summary, reservations.end_date, reservations.start_date
FROM
  cwlab.schedules LEFT OUTER JOIN cwlab.resources ON schedules.scheduleid = resources.scheduleid LEFT OUTER JOIN cwlab.reservations ON resources.machid = reservations.machid
WHERE
  schedules.scheduletitle = 'core-r-003'
ORDER BY
  resources.name
";

And one more thing:

PHP Code:


for ($i=0; $i <$num_results; $i++)
                  {
                     $row = mysql_fetch_array($result);
                     extract ($row);
                     
                    $class = "tbl_white";
                    if ($i % 2 == 0) $class = "tbl_gray";
                    if($end_date <= '1194786000')
                    {
                        $endDate = '';
                        $end_date = $endDate;
                    }
                    
                    echo "<tr align='center' class= $class >";
                     
                    echo "<td width='10%'>" . $name . "</td>";
                    echo "<td width='20%'>" . date("m/d/y G.i:s", $start_date) . "</td>";
                    echo "<td width='20%'>" . date("m/d/y G.i:s", $end_date) . "</td>";
                    echo "<td width='150%'>" . $summary . "</td>";
                    echo "</tr>";
                    
     
     
     
                  }

that code has error on it.
end_date if < 1194786000, i don't want echo on the table.
seen work, but also display error message as well.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

MySQL query: need suggestions

Re: need help!!

Re: MySQL query: need suggestions