I'm building a PHP/MySQL application and I'm running into a problem with my create and update query. I have 5 columns that are set to type FLOAT that are also set as NULL columns. I don't plan to fill them in until much later in the workflow.
However, I need to create new records for this database, and I need to edit existing records, without touching these 5 float fields at all. I'm using OOP PHP that uses a standard save()
method that checks to see if an ID exists in the object. If not, it calls create()
, and if so, it calls update()
. It works very well, usually.
The update()
and create()
methods are designed to pull from a protected static $db_fields
attribute array declared at the top of each Class, that contains all of the fields used in that table. update()
and create()
run through that array and either INSERT INTO
or UPDATE
in SQL, accordingly.
My understanding is that if you use ''
(two single quotes, empty), SQL will skip those INSERT INTO
or UPDATE
requests and leave them as NULL. There aren't even form fields for those 5 float values anywhere on the page, so of course when the methods run, the values are going to be ''
.
Is that why I'm getting the "Data truncated" error? It seems different -- I haven't seen the truncated error before and that's why I'm coming to you geniuses. Thanks.
That error means the data is too large for the data type of the MySQL table column.
An exception thrown as a DataTruncation exception (on writes) or reported as a DataTruncation warning (on reads) when a data values is unexpectedly truncated for reasons other than its having execeeded MaxFieldSize .
TRUNCATE TABLE empties a table completely. It requires the DROP privilege. Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all rows, or a sequence of DROP TABLE and CREATE TABLE statements. To achieve high performance, TRUNCATE TABLE bypasses the DML method of deleting data.
''
and null
are not the same. if your mysql server is in strict mode, then it will refuse to do the insert since you have passed invalid data for the column. without strict mode, it returns a warning.
mysql> create table a (a float not null); Query OK, 0 rows affected (0.11 sec) mysql> insert a values (''); Query OK, 1 row affected, 1 warning (0.05 sec) mysql> show warnings; +---------+------+----------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------+ | Warning | 1265 | Data truncated for column 'a' at row 1 | +---------+------+----------------------------------------+ 1 row in set (0.00 sec) mysql> set sql_mode = 'STRICT_ALL_TABLES'; Query OK, 0 rows affected (0.02 sec) mysql> insert a values (''); ERROR 1265 (01000): Data truncated for column 'a' at row 1
either insert explicit null
s, or don't even specify the column in the insert.
when you're updating you can send all of the values you have because mysql will automatically ignore the unchanged ones.
This isn't meant as a solution, but only to perhaps shed some more light on the problem.
If you add the keyword IGNORE to the statement, and try to insert '' (or any string not beginning with a number) into a float column, MySQL will insert the value 0, instead of the default value.
INSERT IGNORE INTO a_table (float_column) VALUE ('') INSERT IGNORE INTO a_table (float_column) VALUE ('abc')
Both will insert 0, even if the default is null. Without IGNORE, of course, both statements will raise the error you're seeing.
In short, you get the message because the data you're supplying is neither the expected type nor the default value.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With