I have a several tables that make up a schedule. I am using PHP and MySQL with Linux. The code is as follows:
<?php
session_start();
if(isset($_SESSION['username'])){
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<link href="styles/style.css" rel="stylesheet" type="text/css">
<title>View All Employees</title>
</head>
<body>
<div id="wrapper">
<div id="header"><br><br><br>
<p>xxxxxxxxxx</p></div>
<div id="side"><?php
include ('includes/side.php');
?>
</div>
<div id="main">
<center><h3><b>Past Schedules</b></h3></center>
<?php
$db = new mysqli('localhost', 'xxxx', 'xxxx', 'xxxx');
if($db->connect_errno > 0){
die('Unable to connect to database [' . $db->connect_error . ']');
}
echo '<table>';
echo '<tr>';
echo '<th>Job Date</th><th>Report</th><th>Start</th><th>Customer</th><th>Crew</th> <th>Equipment</th><th>Description</th><th>Job Notes</th>';
echo '</tr>';
$result = $db->query("SELECT
`ScheduledJob`.`ScheduledJobID`,
`ScheduledJob`.`JobDate`,
`ScheduledJob`.`ReportTime`,
`ScheduledJob`.`StartTime`,
`ScheduledJob`.`CustomerID`,
`Customer`.`Name` AS `CustomerName`,
`ScheduledEmployee`.`EmployeeID`,
CAST(NULL AS SIGNED INTEGER) AS `EquipmentID`,
`ScheduledJob`.`JobDescription`,
CONCAT(`Employee`.`FirstName`, ' ', `Employee`.`LastName`) AS `EmployeeName`,
'' AS `EquipmentNumber`
FROM
(`ScheduledJob` INNER JOIN
`Customer` ON `ScheduledJob`.`CustomerID`=`Customer`.`CustomerID`) LEFT JOIN
(`ScheduledEmployee` INNER JOIN
`Employee` ON `ScheduledEmployee`.`EmployeeID`=`Employee`.`EmployeeID`) ON `ScheduledJob`.`ScheduledJobID`=`ScheduledEmployee`.`ScheduledJobID`
UNION
SELECT
`ScheduledJob`.`ScheduledJobID`,
`ScheduledJob`.`JobDate`,
`ScheduledJob`.`ReportTime`,
`ScheduledJob`.`StartTime`,
`ScheduledJob`.`CustomerID`,
`Customer`.`Name` AS `CustomerName`,
CAST(NULL AS SIGNED INTEGER) AS `EmployeeID`,
`ScheduledEquipment`.`EquipmentID`,
`ScheduledJob`.`JobDescription`,
'' AS `EmployeeName`,
`Equipment`.`Number` AS `EquipmentNumber`
FROM
(`ScheduledJob` INNER JOIN
`Customer` ON `ScheduledJob`.`CustomerID`=`Customer`.`CustomerID`) LEFT JOIN
(`ScheduledEquipment` INNER JOIN
`Equipment` ON `ScheduledEquipment`.`EquipmentID`=`Equipment`.`EquipmentID`) ON `ScheduledJob`.`ScheduledJobID`=`ScheduledEquipment`.`ScheduledJobID`;");
while($job = $result->fetch_object()){
echo '<tr>';
echo '<td>',$job->JobDate,'</td>';
echo '<td>',$job->ReportTime,'</td>';
echo '<td>',$job->StartTime,'</td>';
echo '<td>',$job->CustomerName,'</td>';
echo '<td>',$job->EmployeeName,'</td>';
echo '<td>',$job->EquipmentNumber,'</td>';
echo '<td>',$job->JobDescription,'</td>';
echo '<td>',$job->JobNotes,'</td>';
echo '</tr>';
}
echo '</table>';
?>
<?php
} else {
header("location:index.php");
}
?></div>
<div id="footer"></div>
</div>
</body>
</html>
The output is currently like this:
-------------------------------------------------------------------------------------------------------------------
Job Date Report Start Customer Crew Equipment Description Job Notes
2014-02-13 07:00:00 08:00:00 Spruence Genco Steven Gray This is just a sample
2014-02-13 07:00:00 08:00:00 Spruence Genco Phil Dunfy This is just a sample
2014-02-13 07:00:00 08:00:00 Spruence Genco Donald Duck This is just a sample
2014-02-13 07:00:00 08:00:00 Spruence Genco 5234 This is just a sample
2014-02-13 07:00:00 08:00:00 Spruence Genco 3758 This is just a sample
---------------------------------------------------------------------------------------- ----------------------------
But I need it to be like this:
---------------------------------------------------------------------------------------- -----------------------------
Job Date Report Start Customer Crew Equipment Description Job Notes
2014-02-13 07:00:00 08:00:00 Spruence Genco Steven Gray 5234 This is just a sample
Phil Dunfy 3758
Donald Duck
----------------------------------------------------------------------------------------------------------------------
How can i do this. I only want the date, report, start, customer, description, and notes to only show up once...and list all the crew and equipment.
Please help me...
Thanks a lot.....
