CODEWITHSUNDEEP
phpmysqlsqlcodeigniter
GeorgeGeorgitsis
GeorgeGeorgitsis

Reputation: 1262

How to get MySQL error if i try to UPDATE to MySQL negative values?

I have some columns in my MySQL DB declared as "smallint(6) UNSIGNED". Those columns must not have negative values. My problem is that, whenever i'm trying to set manually from phpmyadmin (browse tab) a negative number, phpmyadmin displays an error.

If i try to set a negative value from my PHP Codeigniter application, the negative number is stored as 0 and there is no error.

My SQL update from Codeigniter is this:

UPDATE `table_example` SET `d1` = -2 WHERE `field_example` = '11' AND `field_example` = '0' AND `field_example` = 1

I want the above SQL command to return me an error to my PHP application, but currently it doesn't. It is excecuted (tried the above SQL UPDATE in phpmyadmin and it is successful, even if from phpmyadmin-browse manually changing it fails!) and the value is 0. I don't want it to be 0, i want to get an error from mysql like "Hey fool, you can't update those fields as negative numbers. Here, grab this error".

In fact, i am using transaction in Codeigniter, but it doesn't rollback. That's logical because the above SQL UPDATE is executed successfully! But how? Thanks in advance.

Upvotes: 1

Views: 397

Answers (1)

Yordan Boikov
Yordan Boikov

Reputation: 51

try this sql before your query:

mysql> SET sql_mode = '';

more info about modes can be found here: https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sql-mode-setting

Upvotes: 2

Related Questions