What I have run into is select from my first two tables I didn't have any issues displaying in an html table. When I added the third is where I started having problems with my table layout.
With only two tables I had a good layout it would list the base incident with the Items, value and Status. Base record would list once and the items associated would list all until it reached the end and then go back to the base and display the next one.
Now, with the additional code added for the Drugs, It may display as this:
Occurrence Number | Occurrence Date/Time |Offense | Location | etc
12345 1/1/2018 - 12:00 Took 123 Main St
Items | Item Value | Item Status
Toaster $20 Missing
Crayon $1 Missing
Poodle $100 Found
After Adding my third table (some of the joins are configs incase you were wondering) I get the records but the Items and Drugs are mixing together before looping back to the Base record and displays:
Occurrence Number | Occurrence Date/Time |Offense | Location | etc
12345 1/1/2018 - 12:00 Took 123 Main St
Items | Item Value | Item Status
Toaster $20 Missing
Drug Description | Quantity | Measurement | Estimated Value
Opiate 1 Gm $100
Crayon $1 Missing
Drug Description | Quantity | Measurement | Estimated Value
Poodle $100 Found
I'm sure it has something to do with the "if" needing to be some type of "while" for the Items and Drugs but I've tried while, do while, etc. While testing each one of these most times it will list a base record and then list ALL of the property without ever going to the Drugs or back to the base record.
<?php
{
$isql = "Select I.Number, (CONVERT(VARCHAR(10), I.IncidentDate, 101) + ' - ' + CONVERT(VARCHAR(5),I.IncidentTime,108)) as IncDateTime,
(CONVERT(VARCHAR(10), I.AtFoundDate, 101) + ' - ' + CONVERT(VARCHAR(5),I.AtFoundTime,108)) as FoundDateTime, O.Code,
(I.Location + ', ' + I.LocationCity) as IncidentLocation, (V.FirstName + ' ' + V.LastName) as Name, (V.Age + '/' + V.Race + '/' + V.Sex) as AgeRaceSex,
(V.HomeAddress + ', ' + V.HomeCity) as VictimAddress, CS.CDescription, IP.ItemDescription, IPL.Description as ItemStatus, CAST(IP.ItemValue as numeric(10,2)) as ItemValue,
ID.DrugDescription, ID.Quantity, ID.Measurement, ID.EstimatedValue
From tblIncident I
left join tblCrime O on O.IncidentNumber=I.IncidentNumber
left join tblVictim V on V.IncidentNumber=I.IncidentNumber
left join tblLkpCaseStatus CS on CS.Code=I.CaseStatus
left join tblProperty IP on IP.Number=I.Number
left join tblDrug ID on ID.Number=I.IncidentNumber
Where Date BETWEEN '01/01/2018' AND '03/30/2018' and I.SecurityLevel is null
and (V.SequenceNumber = 1 or V.SequenceNumber Is Null) And (O.SequenceNumber = 1 Or O.SequenceNumber Is Null) and
IP.ReportAsProperty = 1
order by I.Number"
;
$stmt = sqlsrv_query( $conn, $isql);
if( $stmt === false ) {
{
if( ($errors = sqlsrv_errors() ) != null) {
foreach( $errors as $error ) {
echo "SQLSTATE: ".$error[ 'SQLSTATE']."<br />";
echo "code: ".$error[ 'code']."<br />";
echo "message: ".$error[ 'message']."<br />";
}
}}}
/**
* Put resules in a table
*/
$stmt = sqlsrv_query($conn, $isql, array(), array("Scrollable" => 'static'));
if( $stmt === false ) {
echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
exit;
}
$row_count = sqlsrv_num_rows($stmt);
if ($row_count === false) {
echo "Error (sqlsrv_num_rows)";
exit;
}
// Set the number of rows to be returned on a page.
$rowsPerPage = 10;
// Get the total number of rows returned by the query.
$rowsReturned = sqlsrv_num_rows($stmt);
if($rowsReturned === false)
die( print_r( sqlsrv_errors(), true));
elseif($rowsReturned == 0)
{
echo "No rows returned.";
}
echo "<table id='table_id'' border='5' column width='100%'>";
$previousincident_number = null;
while( $row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC) )
{
// echo "<tr class='blank_row'><td></td></tr>";
echo "<tr>";
if ($row['IncidentNumber'] != $previousincident_number) {echo "<tr class='blank_row'><td colspan='10'></td></tr>";
echo "<th bgcolor='#d6d7d8'><center>Occurrence Number</center></th>
<th bgcolor='#d6d7d8'><center>Occurrence Date/Time</center></th><th bgcolor='#d6d7d8'><center>At Found Date/Time</center></th>
<th bgcolor='#d6d7d8'><center>Offense</center></th>
<th bgcolor='#d6d7d8'><center>Location</center></th><th bgcolor='#d6d7d8'><center>How Committed</center></th><th bgcolor='#d6d7d8'><center> Name</center></th>
<th bgcolor='#d6d7d8'><center>Age/RaceSex</center></th><th bgcolor='#d6d7d8'><center> Address</center></th><th bgcolor='#d6d7d8'><center>Case Status</center></th><tr>";
echo "<td><center>" . $row['Number']."</center></td>";
echo "<td><center>" . $row['IncDateTime']."</center></td>";
echo "<td><center>" . $row['FoundDateTime']."</center></td>";
echo "<td><center>" . $row['Code']."</center></td>";
echo "<td><center>" . $row['Location']."</center></td>";
echo "<td><center>" . $row['HowCommitted']."</center></td>";
echo "<td><center>" . $row['Name']."</center></td>";
echo "<td><center>" . $row['AgeRaceSex']."</center></td>";
echo "<td><center>" . $row['Address']."</center></td>";
echo "<td><center>" . $row['CDescription']."</center></td>";
echo "<tr>";
if ($row['ItemDescription'] !== null){
echo "<tr><th bgcolor='#d6d7d8'><center>Items</center></th><th bgcolor='#d6d7d8'><center>Item Value</center></th><th bgcolor='#d6d7d8'><center>Item Status</center></th></th></></tr>" ;
}
$previousincident_number = $row['IncidentNumber'];
}
{
if ($row['ItemDescription'] !== null){
echo "<td><center>" . $row['ItemDescription']."</center></td>";
echo "<td><center>$" . $row['ItemValue']."</center></td>";
echo "<td><center>" . $row['ItemStatus']."</center></td>";
echo "</tr>";
}
{
if ($row['DrugDescription'] !== null){
echo "<tr><th bgcolor='#d6d7d8'><center>Drug Description</center></th><th bgcolor='#d6d7d8'><center>Quantity</center></th>
<th bgcolor='#d6d7d8'><center>Measurement</center></th><th bgcolor='#d6d7d8'><center>Estimated Value</center></th></th></></tr>" ;
}
$previousincident_number = $row['IncidentNumber'];
{
if ($row['DrugDescription'] !== null){
echo "<td><center>" . $row['DrugDescription']."</center></td>";
echo "<td><center>" . $row['Quantity']."</center></td>";
echo "<td><center>" . $row['Measurement']."</center></td>";
echo "<td><center>$" . $row['EstimatedValue']."</center></td>";
echo "</tr>";
}
else { echo "</tr>";
}
}}
}
}
}
echo "<tr>";
echo "</table>";
?>
Any guidance and/or help would be MUCH appreciated.