Post by Richardorl » Sun Jun 12, 2016 12:03 am

Hello!

I'm trying to make a trigger to automate an issue about categories of a product.
I'm experienced in MSSQL but not in MySQL sintaxis, so I'm having some problems in writing it... I someone could please help me with this trigger on products table, I would really apreciate it ;)

I'm checking after update a product quantity if this product exists or not in my category "In stock" (id=91 in my case)
If qty<=0 the check if the product was in id=91, and delete it from that category in that case.

On the other hand, if qty>0 check if that product exists in category id=91 and if not, insert into that category.

Here you have the code:

Code: Select all

use ocar699;

CREATE TRIGGER auto_stock_category AFTER UPDATE ON ocar699.oc_product
for each row
begin
  if EXISTS (SELECT * FROM oc_product_to_category WHERE product_id = OLD.product_id) THEN
    DELETE FROM `oc_product_to_category` WHERE product_id=OLD.product_id and category_id=91 ;
  
  else

  if NOT EXISTS (SELECT * FROM oc_product_to_category WHERE product_id = OLD.product_id) THEN
  INSERT INTO `oc_product_to_category` (`product_id`, `category_id`) VALUES (OLD.product_id, '91');
  end if
end

I have errors in line 7,9 and 13...

Thank you!!




Newbie

Posts

Joined
Mon Jun 06, 2016 4:08 pm

Post by straightlight » Sun Jun 12, 2016 7:44 am

Coded in Opencart-oriented object, this can also be achieved:

Code: Select all

$query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "product` `p` INNER JOIN `" . DB_PREFIX . "product_to_category` `p2c` ON (`p2c`.`product_id` = `p`.`product_id`) WHERE `p`.`quantity` > '0' AND EXISTS (SELECT * FROM `" . DB_PREFIX . "category` `c` WHERE `c`.`category_id` = `p2c`.`category_id` AND `c`.`category_id` = '91')");

if ($query->num_rows) {
    // Set it the way you expect to delete here by changing the OLD.product_id to your actual product ID..
    // DELETE FROM `oc_product_to_category` WHERE product_id = OLD.product_id and category_id = '91';
}

$query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "product` `p` INNER JOIN `" . DB_PREFIX . "product_to_category` `p2c` ON (`p2c`.`product_id` = `p`.`product_id`) WHERE `p`.`quantity` <= '0' AND NOT EXISTS (SELECT * FROM `" . DB_PREFIX . "category` `c` WHERE `c`.`category_id` = `p2c`.`category_id` AND `c`.`category_id` = '91')");

if ($query->num_rows) {
    $this->db->query("INSERT INTO `oc_product_to_category` (`product_id`, `category_id`) VALUES (OLD.product_id, '91')");
}
Feel free to customize it.

The most generated errors being found on Opencart forum originates from contributed programming. The increased post counters are caused by redundancies of the same solutions that were already provided prior.


Regards,
Straightlight
Opencart.com Administrator / Quality Assurance Analyst / Programmer


Legendary Member
Online

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by Richardorl » Sun Jun 12, 2016 3:44 pm

Thanks a lot for the code!


I finally got it in MySQL and I just applied the first part (just remove from a category when stock <=0)
If anybody also is interested, here you have it:

Code: Select all

delimiter $$

CREATE TRIGGER auto_stock_category AFTER UPDATE ON oc_product
for each row
begin

if new.quantity<=0 then
  if EXISTS (SELECT * FROM oc_product_to_category WHERE product_id = OLD.product_id) THEN
  DELETE FROM oc_product_to_category WHERE product_id=OLD.product_id and category_id=91 ;
  end if;
     
end if;

end;$$
Just put the id you need (in my case it was "91")

Thanks!

Newbie

Posts

Joined
Mon Jun 06, 2016 4:08 pm

Post by artcore » Sun Jun 12, 2016 4:22 pm

Hi Richard,
I'm just wondering why not use a simple 'get products if quantity >0' addition to the product model?

Attn: I no longer provide OpenCart extensions, nor future support - this includes forum posts.
Reason: OpenCart version 3+ ;D

Thanks!


User avatar
Active Member

Posts

Joined
Tue Jul 09, 2013 4:13 am
Location - The Netherlands

Post by Richardorl » Sun Jun 12, 2016 8:34 pm

artcore wrote:Hi Richard,
I'm just wondering why not use a simple 'get products if quantity >0' addition to the product model?
Hi!
I guess experienced programmers have many solutions. ;)
This is the way I found with my short knowledge in programming.

Thanks!

Newbie

Posts

Joined
Mon Jun 06, 2016 4:08 pm

Post by artcore » Sun Jun 12, 2016 9:20 pm

I'm sure my knowledge is shorter as I learned today about triggers ;D
Take a look at the getProducts method in the model/catalog folder. To get your desired effect you could just add AND p . quantity > 0 in the where clause right after where p . status = 1.
This would remove all products with quantity < 1 from the entire shop regardless of category

https://github.com/opencart/opencart/bl ... roduct.php
line 78

Attn: I no longer provide OpenCart extensions, nor future support - this includes forum posts.
Reason: OpenCart version 3+ ;D

Thanks!


User avatar
Active Member

Posts

Joined
Tue Jul 09, 2013 4:13 am
Location - The Netherlands

Post by Richardorl » Mon Jun 13, 2016 2:00 am

Hi!

Php today for me is almost like Chinese ::)
I'll start on it after summer vacation with php and I'll take a look at that code once I'm able to understand it... But yes I understand what you mean and I many things are familiar to me when I read the code.
The problem is that I would have never been able to find were to place the modification you say... I have to get on php and understand how it works.

Thanks a lot for your help! I really apreciate it! ;)

Newbie

Posts

Joined
Mon Jun 06, 2016 4:08 pm
Who is online

Users browsing this forum: No registered users and 13 guests