CODEWITHSUNDEEP
mysqlsqloscommerce
Fredrik Johansson
Fredrik Johansson

Reputation: 95

Query with date range for unsold products in oscommerce

I have the Query below to check for unsold Products in my oscommerce store.

$products_query_raw = " select p.products_id, p.products_quantity, p.products_model, pd.products_name from (" . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd) LEFT JOIN " . TABLE_ORDERS_PRODUCTS . " op ON p.products_id = op.products_id where op.products_id IS NULL and p.products_id = pd.products_id order by p.products_model";

This seems to work fine, but I want to limit it to search for unsold products within a date range. The dates for the orders are located in the table ORDERS and the ordered products in the table ORDER_PRODUCTS.

I tried a few things but I cant seem to get it right, if there is anyone that can help me out I would be grateful.

Upvotes: 0

Views: 231

Answers (1)

user4019473
user4019473

Reputation:

I literally just asked almost this same question this morning but in more general terms MySQL Querying Multiple Tables.

@evanv did a great job explaining the trick of it... Also, @aphextwix pointed out a great tutorial for using JOINs http://www.sitepoint.com/understanding-sql-joins-mysql-database/

The part that was screwing me up was the LEFT JOIN and the sub-query.

Here is the query I think you want in oscommerce terms...

$number_of_days = "7";
$unsold_products_query = tep_db_query("SELECT a.* FROM " . TABLE_PRODUCTS . " AS a LEFT JOIN (SELECT products_id FROM " . TABLE_ORDERS_PRODUCTS . " as b INNER JOIN " . TABLE_ORDERS . " AS c ON b.orders_id = c.orders_id WHERE c.date_purchased >= date_sub(c.date_purchased, INTERVAL " . $number_of_days . " day) GROUP BY products_id) AS d ON a.products_id = d.products_id WHERE d.products_id IS NULL");
$unsold_products_array = tep_db_fetch_array($unsold_products_query);

You will of course need to replace c.date_purchased in the date_sub() function if you want to use a date other than the date of the last purchase of that item.

Hope it helps.

Upvotes: 1

Related Questions