Varchar(32) won't accept zero MySQL

Question

I have a field in a MySQL database table called mileage and its type is varchar(32)

On my webpage I have an input field where user can type in mileage and send the form

Here is some PHP validation for the mileage input:

if (empty($_POST["mileage"])) {
        $mileageErr = "Mileage required";
    }   
    else{
    $mileage = htmlspecialchars($_POST["mileage"]);
    }

When I insert the mileage into a db table:

// validation omitted...

$query = $db->prepare("INSERT INTO cars VALUES ($mileage)");
$result = $query->execute();

When I enter the number 0 into the input field and submit it, I get the error "Mileage required".

Why am I getting this error? I have set the type to be varchar(32), it should accept any string input up to length 32 right?

Or is there something I don't know about leading zero's?

http://stackoverflow.com/questions/2220519/in-php-is-0-treated-as-empty — xQbert, May 23 '14 at 21:38
Not related, but when you insert strings (varchar(32)...), you need to quote them although you really should use a bound variable instead of injecting it directly to avoid sql injection. That's why you prepare the query in the first place. — jeroen, May 23 '14 at 21:50

score 4 · Accepted Answer · answered May 23 '14 at 21:38

4

The error you're getting has nothing to do with how your database is set up. empty goes true if you have a zero value.

The following things are considered to be empty:

"" (an empty string)
0 (0 as an integer)
0.0 (0 as a float)
"0" (0 as a string) NULL
FALSE array() (an empty array)
$var; (a variable declared, but without a value)
http://us3.php.net/empty

If you would like to do a correct check, try isset and simply checking if your post == "" so for example

if(isset($_POST['mileage']) && $_POST['mileage'] != "")

answered May 23 '14 at 21:38

ashin999

375
1
12

Right, so can a quick fix like `if (empty($_POST["mileage"]) && if($_POST["mileage"]) != 0)` do the job? – user3574492 May 23 '14 at 21:42
No, empty is going to return false because your value is zero. Use the code that I posted, and if mileage is going to be only numerical, you can take it one step further and check is_numeric as well. – ashin999 May 23 '14 at 21:43
If you don't want values like zero, then empty will work for you. But if you do, then use the code I posted in the answer. – ashin999 May 23 '14 at 21:45

score 0 · Answer 2 · answered May 23 '14 at 21:38

0

$_POST["mileage"] will evaluate as FALSE because it is zero (see http://uk3.php.net/empty for details). A variable is considered empty if it does not exist or evaluates to FALSE.

answered May 23 '14 at 21:38

Matthew Daly

9,212
2
42
83

score 0 · Answer 3 · answered May 23 '14 at 21:41

0

You want to check if the mileage is there with isset (http://us2.php.net/manual/en/function.isset.php), instead of empty (http://us1.php.net/empty).

answered May 23 '14 at 21:41

mukunda

2,908
15
21

Varchar(32) won't accept zero MySQL

3 Answers3