Table join - query help

Databases
1

Hi guys,

I am storing order data from our website into a mysql db;


CREATE TABLE IF NOT EXISTS `phpcart_items_ordered` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `order_id` varchar(50) NOT NULL default '',
  `product_id` varchar(50) NOT NULL default '',
  `product_name` varchar(100) NOT NULL,
  `date_ordered` datetime NOT NULL default '0000-00-00 00:00:00',



CREATE TABLE IF NOT EXISTS `Customers` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `order_id` varchar(50) NOT NULL default '',
  `order_total` decimal(10,2) NOT NULL default '0.00',
  `date_ordered` datetime NOT NULL default '0000-00-00 00:00:00',
----plus other non-relevant data  



CREATE TABLE IF NOT EXISTS `Products` (
  `product_id` mediumint(8) unsigned NOT NULL auto_increment,
  `product_code` varchar(15) default NULL,
  `product_name` varchar(80) default NULL,
  `active` mediumint(3) unsigned NOT NULL default '0',
----plus other non-relevant data

What I want to do is generate a list of all the products that we have not sold in a given month using the example query statement below:


WHERE date_ordered LIKE '$newdate%'
WHERE active='1'  (set in 'Products' table

The ‘newdate’ variable is generated like so:


// get current date for query
	$month = $_GET['month'];
	$year = $_GET['year'];
	
	if (empty($month)) {
   		$month = date("m"); 
		}
	if (empty($year)) {
   		$year = date("Y"); 
		}
	
	$newdate = "$year-$month";

Any help would be greatly appreciated :slight_smile:

2

Awesome!! You are a godsend, thank you so much

3

my pleasure :slight_smile:

4 
SELECT Products.product_id
     , Products.product_code
     , Products.product_name
  FROM Products
LEFT OUTER
  JOIN phpcart_items_ordered
    ON phpcart_items_ordered.product_id = Products.product_id
   AND phpcart_items_ordered.date_ordered >= '2010-06-01'
   AND phpcart_items_ordered.date_ordered  < '2010-07-01'
 WHERE Products.active = 1
   AND phpcart_items_ordered.product_id IS NULL
5

Umm ok, thanks for the heads up on my date format… Sounds like a better approach. So sorry, but back to my initial Q, any idea how I would do the join?

6

main thing you have to do is stop treating dates as character strings and start writing WHERE conditions in a manner that is efficient and will utilize indexes

so since you are extracting on a calendar month basis, then instead of this –

WHERE date_ordered LIKE '2010-06%'

you would do this instead –

WHERE date_ordered >= '2010-06-01'
  AND date_ordered  < '2010-07-01'

you deal with the months by using php to generate a range of dates, and you always use the 1st of the month with “less than” (so you don’t have to worry about february)

make sense?