Preserve leading zeros when saving numeric values in database

Question

When executing dynamic MySQL prepared statements to INSERT or UPDATE, leading zeros (such as 0 in 0213123) disappear. How can I prevent this?

public function update($tablo, array $column, array $form)
{
    global $db;
    $sutun = implode(' = ?, ', array_keys($column)) . ' = ?';
    $where = implode(' = ?, ', array_keys($form)) . ' = ?';
    $stmt = $this->db->prepare("UPDATE $tablo SET $sutun  WHERE $where") or die($this->db->error);
    $form = array_merge($column, $form);
    call_user_func_array(array($stmt,'bind_param'), $this->params($form));
    return $stmt->execute() or die($this->db->error);
}

public function params($params, $types = '', $input = array())
{
    foreach ($params as $key => $val) {
        ${$key} = $val;
        $input[] =& ${$key};

        if (is_numeric($val) AND fmod($val, 1) === 0.00) {
            $types .= 'i';
        } elseif (is_float($val)) {
            $types .= 'd';
        } elseif (is_string($val) OR fmod($val, 1) !== 0.00) {
            $types .= 's';
        } else {
            $types .= 'b';
        }
    }
    array_unshift($input, $types);
    return $input;
}

https://stackoverflow.com/questions/15625057/php-mysqli-prepared-statements-removing-leading-zeros — tyteen4a03, Nov 13 '17 at 19:01
: "It looks like you're writing your own ORM. Have you considered using one that's already written, tested, and widely supported like [Doctrine](http://www.doctrine-project.org/), [Propel](http://propelorm.org/) or [Eloquent](https://laravel.com/docs/master/eloquent)?" — tadman, Nov 13 '17 at 19:09

score 0 · Accepted Answer · answered Nov 13 '17 at 19:07

0

This appears to be the behavior of the database. The column is likely a datatype that doesn't allow leading zeros, such as INT. Try changing that column to VARCHAR.

answered Nov 13 '17 at 19:07

Goose

4,764
5
45
84

score 0 · Answer 2 · answered Nov 13 '17 at 19:11

Don't use is_numeric() to decide whether to use i as the type. It will be true for a string like "0213123". That will cause it to be parsed as a number, instead of keeping it as a string. Use is_int().

    if (is_int($val)) {
        $types .= 'i';
    } elseif (is_float($val)) {
        $types .= 'd';
    } elseif (is_string($val)) {
        $types .= 's';
    } else {
        $types .= 'b';
    }

However, this means you won't be able to use numeric strings for integer or float columns. The caller needs to provide the appropriate types.

But that shouldn't be much of a problem. MySQL will automatically convert types when storing into the database, so the types specified in the prepare() call don't have to match the database types exactly.

Preserve leading zeros when saving numeric values in database

2 Answers2