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;
}

Answer 1

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.

Answer 2

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.