Page 1 of 1
Count number of a product sold in x days
Posted: Mon Jul 27, 2015 7:45 pm
by lloydmedley
Trying to run a query to do as the title suggests. I need to know how many of a product has sold in the last 30 days. Not quite sure how to do it though as the data is in both tables 'order' and 'order_product'. Looking at some other code looks like I might need to use 'LEFT JOIN' perhaps? Any help appreciated.
Re: Count number of a product sold in x days
Posted: Mon Jul 27, 2015 9:13 pm
by lloydmedley
I've come up with the following which kind of works, but always seems to be a few out compared to other reports:
Code: Select all
SELECT sum(op.quantity) AS total FROM order_product op LEFT JOIN `order` o ON (op.order_id = o.order_id) WHERE product_id=257 AND DATE(date_added) >= CURDATE()-INTERVAL 1 MONTH;
Any suggestions? I've tried things like 30 DAY, 1 MONTH, 1 DAY, 1 YEAR, always close but not right?
Re: Count number of a product sold in x days
Posted: Mon Jul 27, 2015 9:35 pm
by artcore
Maybe: and status == complete?
Re: Count number of a product sold in x days
Posted: Thu Mar 03, 2016 7:16 pm
by lloydmedley
I am trying to display on the admin > catalogue > products page how many of each item has sold in the past 30 days.
I have tried the following but I'm a bit unsure what I'm doing so wondering if anyone could help where I'm going wrong.
In admin/model/catalog/product.php I have added
Code: Select all
public function totalsold30days(){
$query = "I CANT SEEM TO POST WITHOUT REMOVING the ACTUAL SQL QUERY HERE BUT IT'S ABOVE ANYWAY";
return $this->db->query($query);
}
In admin/controller/catalog/product.php I have added
Code: Select all
public function totalsold30days() {
$this -> data ['sold30'] = $this -> model_catalog_product -> totalsold30days();
}
But when I try to echo $sold30 in product_list.tpl it has no value. I don't know if I'm close and or way off with what I've done so far though to be honest
Re: Count number of a product sold in x days
Posted: Thu Mar 03, 2016 9:38 pm
by ocart777
give this a try
Code: Select all
public function totalsold30days(){
$query = $this->db->query("SELECT sum(op.quantity) AS total FROM order_product op LEFT JOIN `order` o ON (op.order_id = o.order_id) WHERE product_id=257 AND DATE(date_added) >= CURDATE()-INTERVAL 1 MONTH");
return $query->row['total'];
}
Re: Count number of a product sold in x days
Posted: Thu Mar 03, 2016 10:20 pm
by lloydmedley
Thanks for your reply. I feel like I'm close, but I'm still getting "Undefined variable: sold30 in /home/blades/public_html/vqmod/vqcache/vq2-dashboard_view_template_catalog_product_list.tpl".
Re: Count number of a product sold in x days
Posted: Thu Mar 03, 2016 11:50 pm
by ocart777
on your controller
Code: Select all
foreach ($results as $result) { //index 1
..etc
//public function totalsold30days() { //remove
$this -> data ['sold30'] = $this -> model_catalog_product -> totalsold30days($result['product_id']);
//} //remove
... etc
in your model file
Code: Select all
public function totalsold30days($product_id){
$query = $this->db->query("SELECT sum(op.quantity) AS total FROM order_product op LEFT JOIN `order` o ON (op.order_id = o.order_id) WHERE product_id='" . (int)$product_id . "' AND DATE(date_added) >= CURDATE()-INTERVAL 1 MONTH");
return $query->row['total'];
}