PHP and SQL Results

[cmlowe3714]

First of all I would like to say before hand thank you for any information regarding this question.

I have two things that I am trying to do. I am querying actually an MS Sql server to return data the data returns perfectly fine into an array and the results display correctly however within this data I am trying to Group 1 of the fields a customer name which is secondary on the order by clause.

What I need to do is anytime there is a change in Customer Name I need to insert a line seperating the results.

the second thing im trying to do is when I print the report i am creating I would still like to have the page header and column names displaying at the top of every printed page.

This is an intranet based site so I can not show an actual example but I will provide a screen shot of the report as well as the function that is being used in order to create the report.

function CoilsToInvoice()
{

echo '<table width="100%" border="0"><tr><td>';
	MaterialToInvoice('Material');
echo '</td></tr><tr><td>';
   MaterialToInvoice('Services');
echo '</td></tr><tr><td>';
	MaterialToInvoice('Remnant');
echo '</td>
  </tr>
</table>';

}

function MaterialToInvoice($OrderType)
{
include ('includes/db2.php');

$currentdate = date("m/d/Y");
echo '<table width="98%" border="0">
  <tr> 
    <td rowspan="2">&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp<img src="../images/ParagonBWLogo.GIF" width="263" height="60"></td>
    <td><div align="right"><strong>COILS TO INVOICE - NEW</strong></div></td>
  </tr>
  <tr> 
    <td><div align="right"><strong>'.$currentdate.'</strong></div></td>
  </tr>
</table>';

echo '<table width="100%" border="0" align="left" bgcolor="#FFFFFF"><tr><td><div align="center"><h6><strong>Order Type</strong></h6></div></td><td><div align="center"><h6><strong>Customer Name</strong></h6></div></td><td><div align="center"><h6><strong>Order #</strong></h6></div></td><td><div align="center"> <h6><strong>Order Date</strong></h6></div></td><td><div align="center"><h6><strong>Mill Tag #</strong></h6></div></td><td><div align="center"><h6><strong>Customer Po</strong></h6></div></td><td><div align="center"><h6><strong>Selling Price</strong></h6></div></td><td><div align="center"><h6><strong>Delivery Terms</strong></h6></div></td><td><div align="center"><h6><strong>BOL Number</strong></h6></div></td><td><div align="center"><h6><strong>Scrap Wgt</strong></h6></div></td><td><div align="center"><h6><strong>Invoice Date</strong></h6></div></td><td><div align="center"> <h6><strong>Production Date</strong></h6></div></td></tr>';

$query = "SELECT     	
	tblSalesOrderHeader.OrderNumber, tblSalesOrderHeader.OrderType, tblSalesOrderHeader.DeliveryTerms, tblSalesOrderDetail.OKToInvoice, 
    tblInventory.MillTagNumber, tblInventory.GaugeMeasurement, tblInventory.CoilWidth, tblBusiness.BusinessName, tblBOLHeader.BOLNumber, tblBOLDetail.CoilWeight,
	tblSlitDetail.ScrapWeight As SlitScrapWgt,tblSheetDetail.ScrapWeight As SheetScrapWgt,
	tblSheetHeader.ProductionDate As SheetProdDate, tblSlitHeader.ProductionDate As SlitProdDate,
		Case 
			When tblSalesOrderHeader.OrderType = 'Material' Then
				tblSalesOrderHeader.CustomerPO
			Else
				''
		End As CustomerPO,
		Case
			When tblSalesOrderHEader.OrderType = 'Material' Then
				tblSalesOrderDetail.SellPrice + tblSalesOrderDetail.DlvdFreightAmount
			Else
				tblSalesOrderDetail.ServicesAmount
		End As SellPrice, tblProcessOrder.OrderDate As OrderDate
FROM      

	 tblSalesOrderDetail 
Left Outer Join
	tblSalesOrderHeader On tblSalesOrderDetail.SalesOrderHeaderID = tblSalesOrderHeader.SalesOrderHeaderID
LEFT OUTER JOIN
	tblBOLDetail On tblSalesOrderDetail.InventoryID = tblBOLDetail.InventoryID
Left Outer Join
             tblBOLHeader ON tblBOLDetail.BOLHeaderId = tblBOLHeader.BOLHeaderId 
LEFT OUTER JOIN
            tblBusiness ON tblSalesOrderHeader.SoldToId = tblBusiness.BusinessNumberId 
LEFT OUTER JOIN
            tblInventory ON tblSalesOrderDetail.InventoryId = tblInventory.InventoryId
Left Outer Join
	tblAdjustments On tblInventory.ParentID = tblAdjustments.InventoryID And
		tblAdjustments.DeleteFlag = 0 And tblAdjustments.AdjustmentType Like 'Create%'
Left Outer Join
	tblProcessOrder On tblAdjustments.ProcessOrderID = tblProcessOrder.ProcessOrderID
Left Outer Join
	tblSlitDetail On tblInventory.InventoryID = tblSlitDetail.InventoryID and
		(tblSlitDetail.DeleteFlag = 0 Or tblSlitDetail.DeleteFlag Is Null) 
Left Outer Join
	tblSheetDetail On tblInventory.InventoryID = tblSheetDetail.InventoryID and
		(tblSheetDetail.DeleteFlag = 0 Or tblSheetDetail.DeleteFlag Is Null)
Left Outer Join
	tblSheetHeader On tblSheetDetail.SheetHeaderID = tblSheetHeader.SheetHeaderID and
		(tblSheetHeader.DeleteFlag = 0 Or tblSheetHeader.DeleteFlag Is Null)
Left Outer Join
	tblSlitHeader On tblSlitDetail.SlitHeaderID = tblSlitHeader.SlitHeaderID and
		(tblSlitHeader.DeleteFlag = 0 Or tblSlitHeader.DeleteFlag Is Null) 
WHERE     

	Not tblSalesOrderDetail.OkToInvoice Is Null And
	tblSalesOrderDetail.DeleteFlag = 0 And
	(tblInventory.InventoryStatus = 'Available' Or tblInventory.InventoryStatus = 'Committed')  and
	(tblBOLDetail.DeleteFlag = 0 Or tblBOLDetail.DeleteFlag Is Null) and
	(tblBOLHeader.DeleteFlag = 0 Or tblBOLHeader.DeleteFlag Is Null) and
	(tblSalesOrderHeader.OrderType = '".$OrderType."')
Order By
	tblSalesOrderHeader.OrderType, tblBusiness.BusinessName, tblBOLHeader.BOLNumber, tblSalesOrderHeader.OrderNumber,tblInventory.MillTagNumber";

$result = mssql_query($query);
$numRows = mssql_num_rows($result); 
echo "<BR<BR><div align='left'><h4>".$OrderType."</div><div align='right'> Total Lines: ".$numRows."</h4></div>"; 
while($row = mssql_fetch_array($result))
{
$formatted = number_format($row[15],2);
//$orderdate = date("Y/m/d g:i a",$row[16]);
if ($row[16] == null){
$orderdate = "";
}
else {
$orderdate = substr($row[16], 0, -8);
$orderdate = $row[16];
$convert_date = date('U', strtotime($orderdate));
$orderdate = date('m/d/Y', $convert_date);
}

if ($row[3] == null){
$invoicedate = "";
}
else {
$invoicedate = substr($row[3], 0, -8);
$invoicedate = $row[3];
$convert_date = date('U', strtotime($invoicedate));
$invoicedate = date('m/d/Y', $convert_date);
}

if ($row[12] == null){
$prod1date = "";
}
else {
$prod1date = substr($row[12], 0, -8);
$prod1date = $row[12];
$convert_date = date('U', strtotime($prod1date));
$prod1date = date('m/d/Y', $convert_date);
}
if ($row[13] == null){
$prod2date = "";
}
else {
$prod2date = substr($row[13], 0, -8);
$prod2date = $row[13];
$convert_date = date('U', strtotime($prod2date));
$prod2date = date('m/d/Y', $convert_date);
}
echo "<tr><td><font size='-2'><div align = 'center'>". $row['OrderType'] ."</td><td><font size='-2'><div align = 'center'>". $row['BusinessName'] ."</td><td><font size='-2'><div align = 'center'>". $row[0] ."</td><td><font size='-2'><div align = 'center'>".$orderdate."</td><td><font size='-2'><div align = 'center'>". $row[4] ."</td><td><font size='-2'><div align = 'center'>". $row[14] ."</td><td><font size='-2'><div align = 'center'>".$formatted."</td><td><font size='-2'><div align = 'center'>". $row[2] ."</td><td><font size='-2'><div align = 'center'>". $row[8] ."</td><td><font size='-2'><div align = 'center'>". $row[10] ."". $row[11] ."</td><td><font size='-2'><div align = 'center'>". $invoicedate ."</td><td><font size='-2'><div align = 'center'>". $prod1date."". $prod2date ."</td></tr>";

}

echo "</table>";
}

I know the names wont make much sense but once I am done with the formatting I will correct the $row names.

I have corrupted the names to protect the innocent LOL. But as you will see in the screen shot there are 4 different business names all I need to do is insert a black line between each business.

The column names at the top are what i need replicated on the top of every printed page.

Even though its sql the formatting should be the same as if it were mysql.

All help is greatly appreciated.

[cmlowe3714]

Got most of the issue taken care of I'll share the results.

   $row_count = ""; 

while($row = mssql_fetch_array($result))
{



$formatted = number_format($row[15],2);
//$orderdate = date("Y/m/d g:i a",$row[16]);
if ($row[16] == null){
$orderdate = "";
}
else {
$orderdate = substr($row[16], 0, -8);
$orderdate = $row[16];
$convert_date = date('U', strtotime($orderdate));
$orderdate = date('m/d/Y', $convert_date);
}

if ($row[3] == null){
$invoicedate = "";
}
else {
$invoicedate = substr($row[3], 0, -8);
$invoicedate = $row[3];
$convert_date = date('U', strtotime($invoicedate));
$invoicedate = date('m/d/Y', $convert_date);
}

if ($row[12] == null){
$prod1date = "";
}
else {
$prod1date = substr($row[12], 0, -8);
$prod1date = $row[12];
$convert_date = date('U', strtotime($prod1date));
$prod1date = date('m/d/Y', $convert_date);
}
if ($row[13] == null){
$prod2date = "";
}
else {
$prod2date = substr($row[13], 0, -8);
$prod2date = $row[13];
$convert_date = date('U', strtotime($prod2date));
$prod2date = date('m/d/Y', $convert_date);
}

if ($row['BusinessName'] != $row_count)
{
echo "<tr><td height='2' colspan = '11'><hr></td></tr><tr><td><div align='center'><strong><font size='-2' face='Verdana, Arial, Helvetica, sans-serif'>Customer Name</font></strong></div></td><td><div align='center'><strong><font size='-2' face='Verdana, Arial, Helvetica, sans-serif'>Order #</font></strong></div></td><td><div align='center'> <strong><font size='-2' face='Verdana, Arial, Helvetica, sans-serif'>Order Date</font></strong></div></td><td><div align='center'><strong><font size='-2' face='Verdana, Arial, Helvetica, sans-serif'>Mill Tag #</font></strong></div></td><td><div align='center'><strong><font size='-2' face='Verdana, Arial, Helvetica, sans-serif'>Customer PO</font></strong></div></td><td><div align='center'><strong><font size='-2' face='Verdana, Arial, Helvetica, sans-serif'>Selling Price</font></strong></div></td><td><div align='center'><strong><font size='-2' face='Verdana, Arial, Helvetica, sans-serif'>Delivery Terms</font></strong></div></td><td><div align='center'><strong><font size='-2' face='Verdana, Arial, Helvetica, sans-serif'>BOL Number</font></strong></div></td><td><div align='center'><strong><font size='-2' face='Verdana, Arial, Helvetica, sans-serif'>Scrap Wgt</font></strong></div></td><td><div align='center'><strong><font size='-2' face='Verdana, Arial, Helvetica, sans-serif'>Invoice Date</font></strong></div></td><td><div align='center'> <strong><font size='-2' face='Verdana, Arial, Helvetica, sans-serif'>Production Date</font></strong></div></td></tr>"; 

echo "<tr><td height='2' colspan = '11'><hr></td></tr><tr><td><font size='-6' face='Verdana, Arial, Helvetica, sans-serif'><div align = 'center'>". $row['BusinessName'] ."</td><td><font size='-6' face='Verdana, Arial, Helvetica, sans-serif'><div align = 'center'>". $row[0] ."</td><td><font size='-6' face='Verdana, Arial, Helvetica, sans-serif'><div align = 'center'>".$orderdate."</td><td><font size='-6' face='Verdana, Arial, Helvetica, sans-serif'><div align = 'center'>". $row[4] ."</td><td><font size='-6' face='Verdana, Arial, Helvetica, sans-serif'><div align = 'center'>". $row[14] ."</td><td><font size='-6' face='Verdana, Arial, Helvetica, sans-serif'><div align = 'center'>".$formatted."</td><td><font size='-6' face='Verdana, Arial, Helvetica, sans-serif'><div align = 'center'>". $row[2] ."</td><td><font size='-6' face='Verdana, Arial, Helvetica, sans-serif'><div align = 'center'>". $row[8] ."</td><td><font size='-6' face='Verdana, Arial, Helvetica, sans-serif'><div align = 'center'>". $row[10] ."". $row[11] ."</td><td><font size='-6' face='Verdana, Arial, Helvetica, sans-serif'><div align = 'center'>". $invoicedate ."</td><td><font size='-6' face='Verdana, Arial, Helvetica, sans-serif'><div align = 'center'>". $prod1date."". $prod2date ."</td></tr>";
}
else
{
echo "<tr><td nowrap><font size='-6' face='Verdana, Arial, Helvetica, sans-serif'><div align = 'center'>". $row['BusinessName'] ."</td><td><font size='-6' face='Verdana, Arial, Helvetica, sans-serif'><div align = 'center'>". $row[0] ."</td><td><font size='-6' face='Verdana, Arial, Helvetica, sans-serif'><div align = 'center'>".$orderdate."</td><td><font size='-6' face='Verdana, Arial, Helvetica, sans-serif'><div align = 'center'>". $row[4] ."</td><td><font size='-6' face='Verdana, Arial, Helvetica, sans-serif'><div align = 'center'>". $row[14] ."</td><td><font size='-6' face='Verdana, Arial, Helvetica, sans-serif'><div align = 'center'>".$formatted."</td><td><font size='-6' face='Verdana, Arial, Helvetica, sans-serif'><div align = 'center'>". $row[2] ."</td><td><font size='-6' face='Verdana, Arial, Helvetica, sans-serif'><div align = 'center'>". $row[8] ."</td><td><font size='-6' face='Verdana, Arial, Helvetica, sans-serif'><div align = 'center'>". $row[10] ."". $row[11] ."</td><td><font size='-6' face='Verdana, Arial, Helvetica, sans-serif'><div align = 'center'>". $invoicedate ."</td><td><font size='-6' face='Verdana, Arial, Helvetica, sans-serif'><div align = 'center'>". $prod1date."". $prod2date ."</td></tr>";
}
$row_count= $row['BusinessName']; 
}

echo "</table>";
}

As you can see all i did was setup a variable and created an if else statement to perform the grouping. At the end of the while statement I update that variable with the current business name in order to check against the next record.

Although I used horizontal rules this would work for bgcolors as well.

As for the print problem we decided that displaying the column headers at each customer name change would suffice.