Page 1 of 1

MySQL Distinct Not working? wdf?

Posted: Fri Mar 17, 2006 3:57 pm
by LiveFree
Hey All,

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']);
?>
And the SQL strucutre:

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 ;
Any and all help is appriciated :)

Posted: Fri Mar 17, 2006 5:22 pm
by feyd
DISTINCT works just fine. It takes into consideration the entire record you are returning. Since this is more a database issue, moved.

Posted: Fri Mar 17, 2006 5:23 pm
by timvw
Afaik DISTINCT applies to *all* columns in the SELECT clause (and thus not only to the first on the right of the operator)

Posted: Fri Mar 17, 2006 6:24 pm
by LiveFree
Ok Guys thanks for clarifying that,

Do you know of a way to modify the query to eliminate the duplicates / use DISTINCT more effectiantly

Posted: Fri Mar 17, 2006 6:27 pm
by feyd
the more fields you remove (from the selection) that vary between each other the less records you will get.