[SOLVED] Mysql query for the lowest special price

Quote

Post by bardossi » 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!
Last edited by bardossi on Thu Jun 09, 2022 5:24 am, edited 1 time in total.
bardossi
Newbie
Posts
16
Joined
Thu May 14, 2015 1:46 am
Top

Re: Mysql query for the lowest special price

Quote

Post by straightlight » Tue Jun 07, 2022 3:27 am

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.

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester

straightlight
Legendary Member
Posts
22525
Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON
Top

Re: Mysql query for the lowest special price

Quote

Post by bardossi » Tue Jun 07, 2022 6:30 am

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.
bardossi
Newbie
Posts
16
Joined
Thu May 14, 2015 1:46 am
Top

Re: Mysql query for the lowest special price

Quote

Post by straightlight » Tue Jun 07, 2022 7:55 am

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.
Last edited by straightlight on Thu Jun 09, 2022 12:43 am, edited 3 times in total.

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester

straightlight
Legendary Member
Posts
22525
Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON
Top

Re: Mysql query for the lowest special price

Quote

Post by bardossi » 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)
bardossi
Newbie
Posts
16
Joined
Thu May 14, 2015 1:46 am
Top

Re: Mysql query for the lowest special price

Quote

Post by straightlight » Tue Jun 07, 2022 9:16 pm

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.

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester

straightlight
Legendary Member
Posts
22525
Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON
Top

Re: Mysql query for the lowest special price

Quote

Post by bardossi » Wed Jun 08, 2022 12:19 am

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.
bardossi
Newbie
Posts
16
Joined
Thu May 14, 2015 1:46 am
Top

Re: Mysql query for the lowest special price

Quote

Post by bardossi » Wed Jun 08, 2022 2:10 am

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:

Code: Select all

<?php echo $myVariable; ?>
This is working properly without the additional condition of interval.
bardossi
Newbie
Posts
16
Joined
Thu May 14, 2015 1:46 am
Top

Re: Mysql query for the lowest special price

Quote

Post by straightlight » Wed Jun 08, 2022 7:20 pm

Forgot the AND clause. Should work now.

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester

straightlight
Legendary Member
Posts
22525
Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON
Top

Re: Mysql query for the lowest special price

Quote

Post by bardossi » Wed Jun 08, 2022 8:55 pm

Almost...: Parse error: syntax error, unexpected '30' (T_LNUMBER), expecting ',' or ')'
bardossi
Newbie
Posts
16
Joined
Thu May 14, 2015 1:46 am
Top

Re: Mysql query for the lowest special price

Quote

Post by straightlight » Thu Jun 09, 2022 12:43 am

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.

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester

straightlight
Legendary Member
Posts
22525
Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON
Top

Re: Mysql query for the lowest special price

Quote

Post by bardossi » 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.
bardossi
Newbie
Posts
16
Joined
Thu May 14, 2015 1:46 am
Top

Re: Mysql query for the lowest special price

Quote

Post by straightlight » Thu Jun 09, 2022 2:55 am

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.

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester

straightlight
Legendary Member
Posts
22525
Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON
Top
Post Reply
Return to “General Support”
Who is online

Users browsing this forum: No registered users and 119 guests