A few NorthWind MySQL PHP scripts
Note that the dates on the NorthWind data range only across 1 full year, 1997: from July 1996 to May 1998.
Each line of data below comes from 3 files (tables): the orders file, the order-details file, and the customer file. There are also files of employees, products, suppliers, shippers, and more.
<p align="center">
Orders Shipped the 7 days before today in 1997
</p>
<?php
$thisyear = date('Y') ;
$diffyr = $thisyear - 1997 ;
$today = mktime(0,0,0,date("m"),date("d")-(365.25)*$diffyr,date("Y"));
$today = strftime("%Y-%m-%d",$today);
$less7 = mktime(0,0,0,date("m"),date("d")-(365.25*$diffyr+7),date("Y"));
$less7 = strftime("%Y-%m-%d",$less7);
$strdate = $less7 ;
$enddate = $today ;
$ttlsale = 0 ;
$query = "SELECT *,
sum(round(UnitPrice * Quantity -
UnitPrice * Quantity * Discount, 2)) as Sale,
count(D.OrderID) Products,
sum(Quantity) Items,
sum(IF(UnitPrice = 20.00 and
UnitPrice = 40.00, 1, 0) ) as gt40
FROM nwOrders as O, nwCustomers as C,
nwOrderDetails as D
WHERE O.CustomerID = C.CustomerID
AND ShippedDate >= '$strdate'
AND ShippedDate 'USA' ";
$result = mysql_query($query);
$numOrders = mysql_num_rows($result); // How many orders are there?
echo '
<h4 align=center> The '.$numOrders.' Orders Shipped from '
.$strdate.' up to '.$enddate.' </h4>
<table width=775 style="font-size:13px; font-family:Verdana;"
align=center cellspacing=3 cellpadding=3 border=1>
<tr align=center bgcolor="#ffcccc">
<th width=045> Order ID </th>
<th width=085> in order by <br> Ship Date </th>
<!-- th width=170> Contact Name </th -->
<th width=250> Ship Co. Name </th>
<!-- th width=190> Ship City, Country </th -->
<!-- th width=090> Sales-person </th -->
<th width=050> # of Prod\'s sold </th>
<th width=050> # of Items sold </th>
<th width=090> $Sale </th>
<!-- th width=070> $Freight </th -->
<th width=045> # of Prod\'s < $20 </th>
<th width=045> # of Prod\'s $20-40 </th>
<th width=045> # of Prod\'s >= $40 </th>
</tr>
';
while ($row = mysql_fetch_object($result) )
{
// ============================================================
$total = $row->Sale + $row->Freight ;
echo "
<tr align=right bgcolor=#ffeeee onClick='setcolor(this)';
onmouseover='ovr(this)'; onmouseout='off(this)'; >
<td>
<a href=NorthWind-database-1.php?order={$row->OrderID}>
{$row->OrderID}
</a> <!-- see next query below for details of 1 order -->
</td>
<td> {$row->ShippedDate} </td>
<td align=left> {$row->ShipName} </td>
<td> {$row->Products} </td>
<td> {$row->Items} </td>
<td> $ ".number_format($total,2)." </td>
<td> {$row->lt20} </td>
<td> {$row->bt2040} </td>
<td> {$row->gt40} </td>
</tr>\n";
$ttlsale += $total ;
} // End of while loop.
echo '
<tr align=right bgcolor=#ffdddd>
<td align=left colspan=3>
Click on an Order ID to see it\'s details
</td>
<td colspan=2> Total:
</td>
<td> $ ".number_format($ttlsale,2)."
</td>
<td colspan=4 >
</td>
</tr>
</table>
";
?>
to obtain and display the details of 1 order:
if (isset($_GET['order']))
$order = $_GET['order'] ;
else
$order = 10393 ;
$query = "SELECT *, round(D.UnitPrice * Quantity -
D.UnitPrice * Quantity * Discount, 2) Price,
round(D.UnitPrice * Quantity, 2) Total,
round(Discount, 2) Discount
FROM nwCustomers as C, nwOrders as O,
nwEmployees as E, nwOrderDetails as D,
nwProducts as P
WHERE O.EmployeeID = E.EmployeeID
AND O.CustomerID = C.CustomerID
AND O.OrderID = D.OrderID
AND D.ProductID = P.ProductID
AND O.OrderID = $order
ORDER BY D.UnitPrice ";
$query = "SELECT *, sum(round(UnitPrice * Quantity -
UnitPrice * Quantity * Discount, 2)) as Sale,
count(D.OrderID) Products,
sum(Quantity) Items,
sum(IF(UnitPrice = 20.00 and
UnitPrice = 40.00, 1, 0) ) as gt40
FROM nwOrders as O, nwCustomers as C,
nwEmployees as E, nwOrderDetails as D
WHERE O.EmployeeID = E.EmployeeID
AND O.CustomerID = C.CustomerID
AND O.CustomerID = '$CustomerID'
AND ShippedDate >= '1997-01-01'
AND ShippedDate
'.$numOrders.' orders were shipped to
<span style="color:darkgreen"> <b> '.$CoName.' </b>
</span> over the year
$thisyear = date('Y') ;
$diffyr = $thisyear - 1997 ;
$lastmo = mktime(0,0,0,date("m"),date("d")-(365.25*$diffyr+30),date("Y"));
$mo001 = strftime("%Y-%m-01",$lastmo);
$mo031 = strftime("%Y-%m-31",$lastmo);
$strdate = $mo001 ;
$enddate = $mo031 ;
$query = "SELECT *,
sum(round(UnitPrice * Quantity -
UnitPrice * Quantity * Discount, 2)) as Subttl,
sum(round(UnitPrice * Quantity -
UnitPrice * Quantity * Discount, 2)) + Freight as Sale,
count(D.OrderID) Products,
sum(Quantity) Items,
sum(IF(UnitPrice = 20.00 and
UnitPrice = 40.00, 1, 0) ) as gt40
FROM nwOrders as O, nwCustomers as C,
nwEmployees as E, nwOrderDetails as D
WHERE O.EmployeeID = E.EmployeeID
AND O.CustomerID = C.CustomerID
AND ShippedDate >= '$strdate'
AND ShippedDate = 20.00 and
UnitPrice = 40.00, 1, 0) ) as gt40
FROM nwOrders as O, nwCustomers as C,
nwEmployees as E, nwOrderDetails as D
WHERE O.EmployeeID = E.EmployeeID
AND O.CustomerID = C.CustomerID
AND ShippedDate >= '$strdate'
AND ShippedDate
the more complecated column calculations
in MS SQL Server and in MySQL :
An example of summing 3 subsets of a column in MS SQL Server and in MySQL
The example below gives the sum spent on products that cost less than $20
the sum spent on products that cost between $20 and $40
and the sum spent on products that cost more than $40
MySQL:
sum(IF(UnitPrice < 20.00, 1, 0) ) as lt20,
sum(IF(UnitPrice >= 20.00 and
UnitPrice < 40.00, 1, 0) ) as bt2040,
sum(IF(UnitPrice >= 40.00, 1, 0) ) as gt40,
MS SQL Server:
sum(case when UnitPrice < 20 then 1 else 0 end) as lt20,
sum(case when UnitPrice >= 20 and UnitPrice < 40
then 1 else 0 end) as bt2040,
sum(case when UnitPrice >= 40 then 1 else 0 end) as gt40,
similar:
SELECT bu, count(line_number) total,
sum(FIND_IN_SET(flag,'Y')) needed,
sum(FIND_IN_SET(flag,'N')) not_needed,
sum(FIND_IN_SET(flag,' ')) not_processed
FROM isf.isf_analog_line
GROUP BY bu
NorthWind Products
and their suppliers
$query = "SELECT * FROM nwProducts as P, nwSuppliers as S
WHERE P.SupplierID = S.SupplierID
ORDER BY CategoryID, ProductName ";
also see and download the Northwind database in MySql format ... in the MySql category.
