Date calculations &lt;,&gt;

Date calculations <,>

Anon

Need some help:

Im using php with mysql database

I have two tables, a customer_table and a purchase_table. Customer table has customer info (firstname,lastname,etc..), and purchase table has purchase info for each customer(product_name,purchase_date,customer_id).

I would like to be able to see the customers who have not purchased any products since a date that i specify.
EX: show me all customers who have not purchased a product since 01-01-2002. Any customers who have purchase dates greater than 01-01-2002 will not be included.

$date_from = 2002-01-01;

Here is the sql statement (its not working):
$sql = "SELECT * FROM customer_table,purchase_table WHERE (customer_table.customer_id = purchase_table.customer_id)
&& ((purchase_table.purchase_date) < ($date_from)) GROUP BY customer_table.customer_id ORDER BY customer_table.customer_id";

echo $sql;

SELECT * FROM customer_table,purchase_table WHERE (customer_table.customer_id = purchase_table.customer_id) && ((purchase_table.purchase_date) < (2002-01-31)) GROUP BY customer_table.customer_id ORDER BY customer_table.last_name

I know this is long, thanks for any help

steve

Anon

I found my problem, i just needed to put ' ' around $date_from, but i have another problem. My table might have this data

Customer_table:

Customer_id = 1
name = john smith
Customer_id = 2
name = mary smith

Purchase_table
purchase_id = 1
name = microwave
date = 2002-01-01
customer_id = 1

purchase_id = 2
name = washer
date = 2002-01-15
customer_id = 1

john smith purchased two products, one on the 1st and one on the 15th
if i run this selecting anybody who has not purchased a product since 2002-01-10, John smith should not come up since he purchased a different product on the 15th, but he does cause it grabed it because the first product purchased (the microwave) is before the 2002-01-10 date. How can i write the sql statement to acheive this.

[deleted]

tricky, because MySQL does not support subqueries.

You'll have to do a trick with CASE(), SUM() and GROUP BY.

Use the CASE to return 1 or 0 depending on wether the purchase is before or after the set date.
Then use SUM() to add the CASE output for all the purchases, and use GROUP BY to get result 'grouped by' customer.

a bit like this (not good, but to get the idea):

SELECT customerid, SUM(CASE WHEN purchasedate > setdate THEN 1 ELSE 0 END) AS badpurchases
FROM table
GROUP BY customerid;

now a new field is created, called 'badpurchases' and it holds the number of purchases done after the given date.

You can add another SUM(CASE thing to count the good purchases.
Then you'll get a result for all customers, counting their good and bad purchases.
Then you can use the HAVING clause to return only those who do have >1 good purchases and 0 bad purchases.

Like I said, tricky with MySQL shitty SQL support.

A forum, a FAQ, what else do you need?