CODEWITHSUNDEEP
phpmysqlzend-db-table
Andy Baird
Andy Baird

Reputation: 6208

VARCHAR(4) storing more characters than four

I have a VARCHAR(4) column that receives data from an input that may be above 4 characters. This is okay, though, and I let MySQL naturally (or so I thought) cut off the end of the characters.

Strangely enough, when I'm looking at the database row results later in PHP (Using the PDO driver), the entire string is displaying, not just the 4 characters.

Weird thing is, if I do a SELECT query on the MySQL CLI, it only returns the 4 characters. Even when I do a mysqldump, only the 4 characters show.

Any idea what could cause this odd inconsistency?

Note that these characters are all numbers.

Edit: By request, here's some psuedocode that represents the save/fetch methods:

Storing:

$data = array(
     'name_first4'       => $fields['num'],   
     // name_first4 is the column name with VARCHAR(4)
);
$where = $this->getTable()->getAdapter()->quoteInto('id = ?', $id);
$this->getTable()->update($data,$where);

Fetching, using Zend_Db_Table:

$row = $this->getTable()->fetchRow(
    $this->getTable()->select()->where('id=?',$data)
);

Upvotes: 3

Views: 3082

Answers (1)

mu is too short
mu is too short

Reputation: 434665

If you're doing this:

  1. Create or load an object $o.
  2. Assign '12345' to the property/column in question.
  3. Save $o and let MySQL truncate the value to '1234'.
  4. Access the property/column in $o and get '12345' back.

then you're seeing one of the problems of letting your database silently mangle your data.

The save succeeds, your object has no idea that MySQL has truncated the data so it keeps the '12345' around rather than reloading that column from the database, and you have inconsistent data on your hands.

If you're depending on MySQL silently truncating your data then you'll probably have to do this:

  1. Create/load your object.
  2. Updated the properties.
  3. Save the object.
  4. Throw away your local reference to the object.
  5. Load it fresh from the database to make sure you get the real values.

I'd recommend adding strict validations to your objects to avoid the silent truncation inside MySQL. Turning on strict mode would also avoid this problem but then you'd need to review and tighten up all your error handling and data validation (which wouldn't really be a bad thing).

Upvotes: 10

Related Questions