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] );

Check conditions in php before insert.

u_mulder
– u_mulder

2014-10-04 18:51:44 +00:00
Commented Oct 4, 2014 at 18:51 — u_mulder
– u_mulder, Commented Oct 4, 2014 at 18:51

Gordon Linoff · Accepted Answer · 2014-10-04 19:05:52Z

1

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.

answered Oct 4, 2014 at 19:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

aphextwix Over a year ago

Thanks - unfortunately this is throwing the ERROR: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens - Any idea why? the number of parameters seems to match on both parts?

aphextwix Over a year ago

Could this be because in cases where dtmDiscontinued = '' nothing is being entered?

Gordon Linoff Over a year ago

@aphextwix . . . I don't know why the number of parameters would not match.

aphextwix Over a year ago

Please see updates above? Are you able to see the cause of the error now? Many thanks

Gordon Linoff Over a year ago

As I tried to point out in my answer, I don't understand what the condition on dtmDiscontinued is. I made it a variable. If so, you need to include it in the list.

|

Gervs · Accepted Answer · 2014-10-04 19:16:58Z

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

Collectives™ on Stack Overflow

MYSQL Conditional Insert with Multiple Conditions

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related