MYSQL Conditional Insert with Multiple Conditions

Question

I'm trying to create an INSERT statement with multiple conditions as part of a PHP function - here's my code so far :

public function insertData($data)
    {
        try
        {
            $stmt = $this->con->prepare('
            INSERT IGNORE INTO tblProductData(
            strProductCode, 
            strProductName, 
            strProductDesc,  
            smintStockLevel, 
            dblPrice, 
            dtmDiscontinued, 
            dtmAdded)
           SELECT :strProductCode, 
                  :strProductName, 
                  :strProductDesc, 
                  :smintStockLevel, 
                  :dblPrice,
           (CASE WHEN :dtmDiscontinued  = "yes" then NOW();
           ELSE SET dtmlDiscontinued = "NULL";
           END CASE),
            NOW()
            FROM dual
            WHERE not (:dblPrice < 5.0 and :smintStockLevel < 10) 
            and not (:dblPrice > 1000.0) ');

            $length = count($data);
            for ($x=0; $x < $length; $x++) {
            $params = array(':strProductCode' => $data[$x][0], 
                            ':strProductName' => $data[$x][1], 
                            ':strProductDesc' => $data[$x][2],
                            ':smintStockLevel' => $data[$x][3], 
                            ':dblPrice' => $data[$x][4] );

            $stmt->execute($params);
            }

            return $stmt;
            } catch(PDOException $e) {
                echo 'ERROR: ' . $e->getMessage();
            }
        }
    }

The conditions are as follows

• If the value of dblPrice is less than 5 and the value of smintStockLevel is less than 10 the row will not be inserted.
• If the value of dblPrice is greater than 1000 - do not insert that row
• Where dtmDiscontinued = 'yes' the NOW() function will be called inserting the current date/time in that cell. Otherwise enter NULL

This query is being executed as part of a PHP prepared statement, iterating over a multi-dimensional array which contains all of the values.

Will I need to create multiple CASE(s) ? Can somebody please provide advice on the best way to approach this problem.

Any further info required, let me know.

UPDATE

The code above has been entered to show the full context and included Gordon's suggestion. However, I'm currently receiving the error ERROR: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on execution.

Can anybody see the cause? Thanks

SOLUTION

:dtmlDiscontinued needed to be added to the $params to solve the error :

Example

 $params = array(':strProductCode' => $data[$x][0], ':strProductName' => $data[$x][1], ':strProductDesc' => $data[$x][2],
                            ':smintStockLevel' => $data[$x][3], ':dblPrice' => $data[$x][4], ':dtmDiscontinued' => $data[$x][5] );  

Answer 1

Mysql doesn't have check constraint so you'll have to implement them in a trigger. To give ypu a headstart:

DELIMITER $$
CREATE TRIGGER product_cond BEFORE INSERT ON tblProductData
FOR EACH ROW
BEGIN
    declare message VARCHAR(255);
    declare hasError BIT;
    IF new.dblPrice < 5 AND new.smintStockLevel < 10 THEN
        SET hasError := 1;
        SET message := 'If price is less then 5 stock has to be more then 10';
    END IF;
    IF new.dblPrice > 1000 THEN
        SET hasError := 1;
        SET message := 'Price can not be over 1000';
    END IF;
    /* create the other ckecks in the same way
    If check failed signal an error */
    IF hasError = 1 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = message;
    END IF;
END$$

DELIMITER ;

Note that MYSQL 5.5+ is required to use SIGNAL

Of course you can do these checks also in the application layer

Answer 2

You cannot use where clause with values. You can implement your logic in SQL, with something like this:

INSERT INTO tblProductData(strProductCode, strProductName, strProductDesc,  smintStockLevel,
                           dblPrice, dtmDiscontinued, dtmAdded
                          )
    SELECT :strProductCode, :strProductName, :strProductDesc, :smintStockLevel, 
           :dblPrice,
           (case when :dtmDiscontinued  = 'Yes' then NOW() end),
           NOW()
    FROM dual
    WHERE not (:dblPrice < 5.0 and :smintStockLevel < 10) and
          not (:dblPrice > 1000.0);

(Note the from dual doesn't actually do anything but some databases require a from clause when using where.)

I don't know what dtmDiscontinued = 'yes' means. But something like the above should work.

That said, you might find it easier to put the logic in the application layer rather than in the database. If you always want to check these conditions, then consider a trigger.