MySQL query fails if php variable is empty

[MockY]

I know the following simple demonstration fails and what to do to rectify it (though with a lot of additional code is necessary), but I don't know why this is the behaviour. It would be great if someone could explain to me why, and what the neatest way of fixing it would be.

 

+----------+--------------+------+-----+---------+
| Field    | Type         | Null | Key | Default |
+----------+--------------+------+-----+---------+
| Id       | int(11)      | NO   | PRI | NULL    | 
| Name     | varchar(45)  | YES  |     | NULL    |     
| Number   | int(9)       | YES  |     | NULL    |         

 

$name = "Bradley Cooper";
$number = "";

$query = "INSERT INTO table (Id, Name, Number) VALUES (1, '$name', $number)";
mysql_query($query);

 

Because the $number variable is empty, this simple query fails. Logically, one would think that nothing or NULL gets inserted to that field since the MySQL structure rule states that default is NULL, and since nothing is fed to it in the query, I think that it would automatically be NULL since if the column is omitted, that is exactly the value that will be inserted.

 

Enclosing the variable with quotes is not preferable since this field accepts integers. Also, converting the variable to an integer by (int)$number results in a 0, which is not what I want either. Checking if the variable is empty of not, and if it is, assign $number = "''" so that the query would succeed by enclosing the empty string with single quotes. But this creates a lot of unnecessary code (some of my tables are 80 columns wide and the query therefore have equal amount of variables).

 

So to summarise, why is this happening and how do I neatly avoid assigning quotes to the empty strings? In other words, how do I insert nothing when variables that usually contain an integer is on occation empty?

[Nodral]

Put a clause in like the following

 

if(strlen($number)<0){
$number="NULL";
}

 

This will put a value of NULL into the number rather than a blank.

 

If you look at the SQL statement, you are creating an error when you do it the original way

 

$query = "INSERT INTO table (Id, Name, Number) VALUES (1, 'Bradley Cooper, )";

 

And the comma at the end and difference between the columns and values given will cause the script to fall over.

 

Another way, would be to quote the number you are putting in with backticks.

 

$query = "INSERT INTO table (Id, Name, Number) VALUES (1, '$name',`$number` )";

 

This way you actually insert the value of $number as a literal and if it is blank, it will write a blank value into the column.

 

 

 

[voip03]

You can try this way  also

$query = "INSERT INTO table (Id, Name, Number) VALUES ('" . 1 ."','" . $name ."','" . $number ."')";