Page 1 of 1
[SOLVED] Mysql query for the lowest special price
Posted: Tue Jun 07, 2022 12:28 am
by bardossi
Hello everyone,
I am trying to put together a mysql query for my old 1.5.6.4 Opencart store, the query should get the lowest value on oc_product_special, between certain date interval. I tested my query in phpmyadmin, it works but I cannot insert it into the model. This is my query: SELECT MIN(price) FROM oc_product_special WHERE product_id = xxxx AND date_end > DATE(NOW() - INTERVAL 30 DAY)
And this is my code in model, first part is working (before AND):
Code: Select all
$this->db->query("SELECT MIN(price) AS min_price FROM " . DB_PREFIX . "product_special WHERE product_id = '" . (int)$product_id . "'") . "' AND date_end > date_end > DATE(NOW() - INTERVAL 30 DAY);
Could you please help me what am I doing wrong? (I am newbie in sql...
Thanks!
Re: Mysql query for the lowest special price
Posted: Tue Jun 07, 2022 3:27 am
by straightlight
bardossi wrote: ↑Tue Jun 07, 2022 12:28 am
Hello everyone,
I am trying to put together a mysql query for my old 1.5.6.4 Opencart store, the query should get the lowest value on oc_product_special, between certain date interval. I tested my query in phpmyadmin, it works but I cannot insert it into the model. This is my query: SELECT MIN(price) FROM oc_product_special WHERE product_id = xxxx AND date_end > DATE(NOW() - INTERVAL 30 DAY)
And this is my code in model, first part is working (before AND):
Code: Select all
$this->db->query("SELECT MIN(price) AS min_price FROM " . DB_PREFIX . "product_special WHERE product_id = '" . (int)$product_id . "'") . "' AND date_end > date_end > DATE(NOW() - INTERVAL 30 DAY);
Could you please help me what am I doing wrong? (I am newbie in sql...
Thanks!
By referring to a free extension:
https://www.opencart.com/index.php?rout ... n_id=36530 , you could pull the query on how to achieve this.
Re: Mysql query for the lowest special price
Posted: Tue Jun 07, 2022 6:30 am
by bardossi
Hello, thanks for the reply. I will give it a try.
In this case I have to use the min() function of php instead of MIN() of SQL. But if I want to have an interval I would need the second part of the query after the AND. And this is where I am in trouble with syntax.
Re: Mysql query for the lowest special price
Posted: Tue Jun 07, 2022 7:55 am
by straightlight
Code: Select all
$this->db->query("SELECT MIN(`price`) AS min_price FROM `" . DB_PREFIX . "product_special` WHERE `product_id` = '" . (int)$product_id . "' AND `date_end` > DATE(NOW() - INTERVAL 30 DAY)");
You'd, then, need to call the
min_price alias field from the query.
Re: Mysql query for the lowest special price
Posted: Tue Jun 07, 2022 3:24 pm
by bardossi
Hello, thank you. I tried but it throws a syntax error: Parse error: syntax error, unexpected '`', expecting ',' or ')'
Just a question: in this case don't I need the AND operator? (I tried but I got the same error)
Re: Mysql query for the lowest special price
Posted: Tue Jun 07, 2022 9:16 pm
by straightlight
bardossi wrote: ↑Tue Jun 07, 2022 3:24 pm
Hello, thank you. I tried but it throws a syntax error: Parse error: syntax error, unexpected '`', expecting ',' or ')'
Just a question: in this case don't I need the AND operator? (I tried but I got the same error)
Fixed. Please try again.
Re: Mysql query for the lowest special price
Posted: Wed Jun 08, 2022 12:19 am
by bardossi
Thank you for your efforts. This query will not be my favourite...still does not work. I get back to my station and copy my whole function here.
Re: Mysql query for the lowest special price
Posted: Wed Jun 08, 2022 2:10 am
by bardossi
Ok, here is my model (without the interval query):
Code: Select all
public function getSpecialPriceMin($product_id) {
$query = $this->db->query("SELECT MIN(price) AS min_price FROM " . DB_PREFIX . "product_special WHERE product_id = '" . (int)$product_id . "'");
return $query->row;
}
here is my controller, passing the query result to myVariable:
Code: Select all
$query = $this->model_catalog_product->getSpecialPriceMin($product_id);
$this->data['myVariable'] = $query['min_price'];
and finally this myVariable is echoed to the product page:
This is working properly without the additional condition of interval.
Re: Mysql query for the lowest special price
Posted: Wed Jun 08, 2022 7:20 pm
by straightlight
Forgot the AND clause. Should work now.
Re: Mysql query for the lowest special price
Posted: Wed Jun 08, 2022 8:55 pm
by bardossi
Almost...: Parse error: syntax error, unexpected '30' (T_LNUMBER), expecting ',' or ')'
Re: Mysql query for the lowest special price
Posted: Thu Jun 09, 2022 12:43 am
by straightlight
bardossi wrote: ↑Wed Jun 08, 2022 8:55 pm
Almost...: Parse error: syntax error, unexpected '30' (T_LNUMBER), expecting ',' or ')'
Wasn't my day yesterday, it seem. Code updated. Try again.
Re: Mysql query for the lowest special price
Posted: Thu Jun 09, 2022 2:38 am
by bardossi
Neither was mine... and Yes! It is working. I did not find that double quote...it seems I have to try harder next time.
Thanks for your efforts.
Re: Mysql query for the lowest special price
Posted: Thu Jun 09, 2022 2:55 am
by straightlight
bardossi wrote: ↑Thu Jun 09, 2022 2:38 am
Neither was mine... and Yes! It is working. I did not find that double quote...it seems I have to try harder next time.
Thanks for your efforts.
Now that the issue has been solved, please add: [SOLVED] at the beginning of the subject line on your first post.