-1

I am working in OpenCart and am trying to change this query. I need to change it so that it returns records from that last year(1 year from now) here is my query

$query = $this->db->query("SELECT count(*) AS total, channel FROM `" . DB_PREFIX . "order` WHERE customer_id = '" . (int)$customer_id . "' && order_status_id IN(" . implode(",", $implode) . ") AND YEAR(date_added) = YEAR(CURDATE()) GROUP BY channel");

I tried changing it to this but it ddidnt work:

$query = $this->db->query("SELECT count(*) AS total, channel FROM `" . DB_PREFIX . "order` WHERE customer_id = '" . (int)$customer_id . "' && order_status_id IN(" . implode(",", $implode) . ") AND date_added BETWEEN CURDATE() - INTERVAL 1 year AND CURDATE()) GROUP BY channel");
marklar
  • 123
  • 9
  • 2
    "Didn't work." Please be more clear about that. What error did you get? – Difster Jan 31 '18 at 23:28
  • no error it just didnt change results – marklar Jan 31 '18 at 23:29
  • If the results did not change after adding the extra filter, then all records satisfy the additional criterion. – Shadow Jan 31 '18 at 23:54
  • 1
    @Strawberry there is no dot between the db prefix and the table name within the sql code. – Shadow Jan 31 '18 at 23:55
  • As an aside, though long-winded, something like `date_added BETWEEN CONCAT(YEAR(CURDATE()),'-01-01') AND CONCAT(YEAR(CURDATE()),'-12-31')` can use an index and so has the potential to be much more efficient. – Strawberry Feb 01 '18 at 00:07
  • Can't you bring in the `CURDATE() - INTERVAL 1 year` or any similar calculation already precalulated to the query? That value will be the same for every record, so why let it calculate with every record? – MarkusEgle Feb 01 '18 at 05:16

2 Answers2

0

Before, consider to sanitize your input data to protect your queries against SQL Injection. Here you can find some content about this topic.

Based on this answer, we can adapt your PHP code to the following:

$query = $this->db->query("SELECT count(*) AS total, channel 
               FROM `" . DB_PREFIX . "order` 
               WHERE customer_id = '" . (int)$customer_id . "' 
                 AND order_status_id IN(" . implode(",", $implode) . ")
                 AND date_added >= DATE_SUB(NOW(),INTERVAL 1 YEAR)
               GROUP BY channel");
Claudio Busatto
  • 721
  • 7
  • 17
  • Mysql does understand the && operator. – Shadow Jan 31 '18 at 23:53
  • Really? I did not know it! Thanks for the information, I am editing the answer! – Claudio Busatto Jan 31 '18 at 23:56
  • Plus the use of now() instead of curdate() can have nasty side effects, depending on the requirements. Moreover, if you believe that the current question has already been answered here on SO, you should not answer, but flag the question as duplicate. OK, you probably cannot do that yet because of your rep. – Shadow Feb 01 '18 at 00:00
  • Yes, I cannot mark it as duplicate, that is why I linked the original answer to the answer. Thanks for the reviews, I tried to help in a question and I am learning more than the OP hehe – Claudio Busatto Feb 01 '18 at 00:05
0

You got the wrong syntax when calculating the last year.

You can use DATE_SUB.

$query = $this->db->query("SELECT count(*) AS total, channel FROM `" . DB_PREFIX . "order` WHERE customer_id = '" . (int)$customer_id . "' && order_status_id IN(" . implode(",", $implode) . ") AND date_added BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 YEAR) AND CURDATE()) GROUP BY channel");
dhh
  • 4,289
  • 8
  • 42
  • 59
  • Nope, the syntax in the question is correct. It is an alternative syntax for date_sub(), see mysql documentation on date_add() – Shadow Jan 31 '18 at 23:52