How to perform a while loop on the query result I created a trigger AFTER  an UPDATE of a column in a MySQL database. When the trigger detects a certain value, it should perform some actions. This is what I have at the moment and is working fine.

CREATE TRIGGER `order_gereed` AFTER UPDATE ON `oc_order`
FOR EACH ROW IF new.order_status_id = "15"
THEN
UPDATE push_voorraad SET actie = '1';
END IF


But now I need to do some more SQL when new.order_status_id matches 15. I don't know how to do this in pure SQL, but I do now how to in php. So I hope with posting my php code, someone can tell me how to do this in SQL.

$query = "SELECT
            oc_order_product.quantity,
            oc_order_option.option_value_id,
            product.id
          FROM oc_order_product
            JOIN oc_order_option
              ON oc_order_option.order_id = oc_order_product.order_id
            JOIN product
              ON product.oc_product_id = oc_order_product.model
          WHERE oc_order_product.order_id = '25' AND oc_order_product.store_url = 'http://www.someurl.com/'";

$result = $mysqli-&gt;query($query);
while($row = $result-&gt;fetch_object()){
  $query = "UPDATE product_option_value SET quantity = quantity - {$row-&gt;quantity} WHERE product_id='{$row-&gt;id}' AND option_value_id='{$row-&gt;option_value_id}'";
  $mysqli-&gt;query($query);
}


25 in the query above should actually be new.order_id.

http://www.someurl.com/ in the query above should actually be new.store_url
Oke, I found my own answer after reading the internet en doing some trail and error.

I created a stored procedure which I will execute on the update trigger.

The trigger

CREATE TRIGGER `order_gereed` AFTER UPDATE ON `oc_order`
 FOR EACH ROW IF new.order_status_id = "15"
THEN
CALL VooraadUpdate(new.order_id, new.store_url);
UPDATE push_voorraad SET actie = '1';
END IF


The procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `VooraadUpdate`(IN `orderID` INT(11), IN `storeURL` VARCHAR(255))
    NO SQL
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE a, b, c INT;
    DECLARE cur1 CURSOR FOR SELECT
                              oc_order_product.quantity,
                              oc_order_option.option_value_id,
                              product.id
                            FROM oc_order_product
                              JOIN oc_order_option
                                ON oc_order_option.order_id = oc_order_product.order_id
                              JOIN product
                                ON product.oc_product_id = oc_order_product.model
                            WHERE oc_order_product.order_id = orderID AND oc_order_product.store_url = storeURL;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur1;
    read_loop: LOOP
      FETCH cur1 INTO a, b, c;
        IF done THEN
          LEAVE read_loop;
        END IF;

      UPDATE product_option_value SET quantity = quantity - a WHERE product_id=c AND option_value_id=b;

    END LOOP;

    CLOSE cur1;
END