PHP + PDO/MySQL: how to automatically clip strings to the proper length on insert

Question

I have a table with various VARCHAR fields in MySQL. I would like to insert some user data from a form via PHP. Obviously if I know the field lengths in PHP, I can limit the data length there with substr(). But that sort of violates DRY (field length stored in MySQL and as a constant in my PHP script). Is there a way for me to configure an INSERT so it automatically chops off excessively-long strings, rather than fails?

edit: it's failing (or at least causing an exception) in PHP/PDO, when I have excessively long strings. Not sure what I have to do in PHP/PDO so it Does The Right Thing.

edit 2: Ugh. This is the wrong approach; even if I get it to work ok on INSERT, if I want to check for a duplicate string, it won't match properly.

Answer 1

Actually, MySQL truncates strings to the column width by default. It generates a warning, but allows the insert.

mysql> create table foo (str varchar(10));
mysql> insert into foo values ('abcdefghijklmnopqrstuvwxyz');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'str' at row 1 | 
+---------+------+------------------------------------------+
mysql> select * from foo;
+------------+
| str        |
+------------+
| abcdefghij | 
+------------+

If you set the strict SQL mode, it turns the warning into an error and rejects the insert.

---

Re your comment: SQL mode is a MySQL Server configuration. It probably isn't PDO that's causing it, but on the other hand it's possible, because any client can set SQL mode for its session.

You can retrieve the current global or session sql_mode value with the following statements:

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

The default should be an empty string (no modes set). You can set SQL mode in your my.cnf file, with the --sql-mode option for mysqld, or using a SET statement.

See http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html for full documentation on SQL modes.

Answer 2

you could query information_schema to get the lenght of the column and use that data to truncate before insert, but that's more overhead than necessary. just turn off strict SQL mode for this statement:

select @prev_mode = @@sql_mode;
set sql_mode = '';
insert blah....;
set sql_mode = @@sql_mode;

Answer 3

You can use column metadata to check string length. (PHP Manual on PDOStatement->getColumnMeta)

Get metadata for whole table this way

$query = $conn->query("SELECT * FROM places");
$numcols = $query->columnCount();

$tablemeta = array();
for ($i = 0; $i < $numcols; $i++) {
    $colmeta = $query->getColumnMeta($i);
    $tablemeta[$colmeta['name']] = $colmeta;
}