So i have this code that gets the Invoice ID and total for each month, year, and customer selected. But .. even though I use the 'DISTINCT' keyword, I get double outputs if there are 2 entires in the Order_Details table (which contains the Product ID for Catagory referencing later)
The distinct does not seem to be rooting out the duplicates..
Code:
Code: Select all
<?php
session_start();
session_regenerate_id();
include('connect.php');
if (!$_SESSION['is_admin']){
// Hacking Attempt
header("Location: admin_login.php");
die();
}
//
//$comp=mysql_query("SELECT DISTINCT * FROM `Own Company Details`") OR DIE (mysql_error());
//
$month=$_SESSION['month'];// Old Way of Doing it
$custid=$_SESSION['custid'];
$year=$_SESSION['year'];
// Get Cust info
$cust=mysql_query("SELECT Name AS CustName, Address AS CustAddr, citytown AS CustCity, Postcode AS CustZIP,email AS CustEmail, Telephone AS CustTele, fax AS CustFax FROM Customers WHERE customerid='$custid'") OR DIE (mysql_error());
$cust_info=mysql_fetch_array($cust);
//
$sql_orders=mysql_query("SELECT DISTINCT Orders.Subtotal AS sub, Orders.Invoice_Number AS InvoiceID, categories.catname AS CatName FROM Invoice_Record AS ir, Orders, Order_Details, Customers,Products, categories WHERE Orders.Customer_ID='$custid' AND Orders.month='$month' AND ir.month='$month' AND Orders.year='$year' AND Orders.Customer_ID=Customers.customerid AND categories.catid=Products.Product_Cat_ID AND Order_Details.Product_ID=Products.Product_ID AND Orders.Invoice_Number=Order_Details.Invoice_Number") OR DIE (mysql_error());
//
$sql_info=mysql_query("SELECT * FROM `Own Company Details`") OR DIE ('Could not query:' . mysql_error());
$info=mysql_fetch_array($sql_info);
// Start the layout
//$cust_info=mysql_fetch_array($sql_orders);
echo "<div align='center'><img src='Petal Foods Logo.jpg'><br />
<b>Address:</b>{$info['Address']}<br />
<b>City:</b>{$info['CityTown']}<br />
<b>Postal Code:</b>{$info['PostCode']}<br />
<b>Telephone:</b>{$info['Telephone']}<br />
<b>Mobile Phone:</b>{$info['Mobile']}<br />
<b>Email:</b>{$info['Email']}<br />
<b>VAT #:</b>{$info['VATno']}</div>
<br />
<b>Customer:</b> {$cust_info['CustName']}<br />
<b>Address:</b> {$cust_info['CustAddr']}<br />
<b>City/Town:</b> {$cust_info['CustCity']}<br />
<b>Postal Code:</b> {$cust_info['CustZIP']}<br />
<b>Telephone:</b> {$cust_info['CustTele']}<br />
<b>Email:</b> {$cust_info['CustEmail']}<br />
<b>Fax:</b> {$cust_info['CustFax']}<br /><br />
<div align='center'><table border='1' width='80%'>
<tr><td><b>Invoice #</b></td><td><b>Total</b></td></tr>";
while ($order=mysql_fetch_array($sql_orders)){
//$total=$order['price'] * $order['quan'];
//$subtotal+=$total;
//$invoice=$order['InvoiceID'];
echo "<tr><td><a href=\"invoice3.php?custid=$custid&orderid=$invoice\">{$order['InvoiceID']}</a><td>£{$order['sub']}</td></tr>";
$subtotal+=$order['sub'];
}
$order=mysql_fetch_array($sql_orders);
if ($order['CatName'] == "Buffets/Business Lunches"){
$VAT=(.175 * $subtotal);
$VAT=number_format($VAT, 2, '.', "");
}else{
$VAT='0.00';
}
$subtotal=number_format( $subtotal, 2, '.', "");
echo "<tr><td></td><td></td><td></td><td></td></tr>
<tr><td></td><td><b>VAT: £$VAT</b></td></tr>
<tr><td></td><td><b>Total: £$subtotal</b></td></tr></table></div>";
unset($_SESSION['custid']);
unset($_SESSION['month']);
unset($_SESSION['year']);
?>Code: Select all
-- Server version: 4.1.11
-- PHP Version: 4.3.10-16
-- --------------------------------------------------------
--
-- Table structure for table `Customers`
--
CREATE TABLE `Customers` (
`customerid` int(11) NOT NULL auto_increment,
`Name` varchar(200) default NULL,
`ContactName` varchar(200) NOT NULL default '',
`Address` varchar(200) NOT NULL default '',
`citytown` varchar(100) NOT NULL default '',
`Postcode` varchar(10) NOT NULL default '',
`email` varchar(100) default NULL,
`telephone` varchar(200) default '0',
`fax` varchar(20) default NULL,
`statementcustomer` smallint(6) NOT NULL default '0',
`discountid` int(11) default NULL,
PRIMARY KEY (`customerid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=34 ;
-- --------------------------------------------------------
--
-- Table structure for table `Invoice_Record`
--
CREATE TABLE `Invoice_Record` (
`Invoice_Number` int(4) unsigned NOT NULL default '0',
`Customer_ID` int(4) NOT NULL default '0',
`SubTotal` float(7,2) unsigned NOT NULL default '0.00',
`VAT` float(7,2) NOT NULL default '0.00',
`Date_Ordered` varchar(25) NOT NULL default '',
`Delivery_Date` varchar(25) NOT NULL default '',
`month` smallint(2) unsigned NOT NULL default '3',
KEY `Customer_ID` (`Customer_ID`,`SubTotal`,`VAT`,`Date_Ordered`,`Delivery_Date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `Order_Details`
--
CREATE TABLE `Order_Details` (
`Invoice_Number` int(11) NOT NULL default '0',
`Product_ID` int(11) NOT NULL default '0',
`Price` float(10,2) NOT NULL default '0.00',
`Quantity` int(11) NOT NULL default '0',
KEY `Product_ID` (`Product_ID`,`Price`,`Quantity`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `Orders`
--
CREATE TABLE `Orders` (
`Invoice_Number` int(11) NOT NULL auto_increment,
`Customer_ID` int(11) NOT NULL default '0',
`Date_Ordered` varchar(15) NOT NULL default '0000-00-00',
`Delivery_Date` varchar(15) NOT NULL default '0000-00-00',
`VAT` decimal(10,0) NOT NULL default '0',
`Subtotal` decimal(10,2) NOT NULL default '0.00',
`month` enum('01','02','03','04','05','06','07','08','09','10','11','12') NOT NULL default '01',
`year` int(4) unsigned NOT NULL default '0',
`Dispatched` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`Invoice_Number`),
KEY `Customer ID` (`Customer_ID`),
KEY `month` (`month`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
-- --------------------------------------------------------
--
-- Table structure for table `Products`
--
CREATE TABLE `Products` (
`Product_ID` int(11) NOT NULL auto_increment,
`Product_Cat_ID` int(11) NOT NULL default '0',
`Name` varchar(50) NOT NULL default '',
`Description` text NOT NULL,
`Picture` varchar(20) NOT NULL default '',
`Bake & Method` text NOT NULL,
`Weight` float(30,2) NOT NULL default '0.00',
`Standard_Price` float(30,4) NOT NULL default '0.0000',
PRIMARY KEY (`Product_ID`),
KEY `Product Cat ID` (`Product_Cat_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=47 ;
-- --------------------------------------------------------
--
-- Table structure for table `categories`
--
CREATE TABLE `categories` (
`catid` int(11) NOT NULL auto_increment,
`catname` varchar(60) NOT NULL default '',
PRIMARY KEY (`catid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;