CODEWITHSUNDEEP
phpmysqlnull
hopeless bob
hopeless bob

Reputation: 15

mysql wont take null, instead sets 0

I am trying to insert a NULL value into my DB, I tried different examples but the result kept turning up as 0 so I forced it, note the line below //********** $gefid = null; yet is still shows up as 0, have tried with and without quotes, capitals, you name it. The field definitely allows Nulls and nulls are default code:

<?php

DEFINE ('DBUSER', 'xxxxxxxxx');
DEFINE ('DBPW', 'xxxxxxxxxxxxxxxxxxxxxxx');
DEFINE ('DBHOST', 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxx.com');
DEFINE ('DBNAME', 'Mydb');


$dbc = mysqli_connect(DBHOST,DBUSER,DBPW);
if (!$dbc) {
die("Database connection failed really badly: " . mysqli_error($dbc));
exit();
}

$dbs = mysqli_select_db($dbc, DBNAME);
if (!$dbs) {
die("now the dam Database selection bit failed: " . mysqli_error($dbc));
exit();
}
$lat = mysqli_real_escape_string($dbc, $_GET['lat']);
$lng = mysqli_real_escape_string($dbc,$_GET['lng']);

$prox = mysqli_real_escape_string($dbc,$_GET['prox']);
$description = mysqli_real_escape_string($dbc,$_GET['description']);
$id = mysqli_real_escape_string($dbc,$_GET['id']);
$direction = mysqli_real_escape_string($dbc,$_GET['direction']);
$avoiddays = mysqli_real_escape_string($dbc,$_GET['avoiddays']);
$validfrom = mysqli_real_escape_string($dbc,$_GET['validfrom']);
$validto = mysqli_real_escape_string($dbc,$_GET['validto']);



if (isset($_GET['gefid'])) {
  $gefid = mysqli_real_escape_string($dbc,$_GET['gefid']);
} else {
  $gefid = null;
}
//***********************************
$gefid = null;
$expiry = mysqli_real_escape_string($dbc,$_GET['expiry']);

$query = "INSERT INTO realtime (rt_lat,rt_lng,rt_prox,rt_description,rt_direction,rt_avoiddays,rt_validto,rt_validfrom,rt_gefid,rt_expiry) VALUES ('$lat', '$lng', '$prox', '$description','$direction','$avoiddays','$validto','$validfrom','$gefid','$expiry')"; 

$result = mysqli_query($dbc, $query) or trigger_error("Query MySQL Error: " . mysqli_error($dbc));

mysqli_close($dbc);
?>

Upvotes: 0

Views: 128

Answers (3)

James McClelland
James McClelland

Reputation: 555

Someone commented about the table schema but I will elaborate. You need to check if your table definition has default values automatically assigned and also see if the column is not null.

If you use:

   SHOW CREATE TABLE realtime;

And check the fields for 

   DEFAULT 0
   NOT NULL

If either of those column definitions are present then you wont be able to insert a null value into those columns.

Upvotes: 0

Ayub
Ayub

Reputation: 510

You should:

  1. $gefid = "NULL"; instead of: $gefid = null;
  2. remove quotes from $gefid in INSERT INTO statement ('$gefid' to $gefid)

Because NULL is a keyword that should be passed to MySQL instead of "NULL" which is a string, you are trying to pass a string to integer field in database.

EDIT: But if that is the case you should get MySQL error, double check your Schema for a Default 0.

Upvotes: 1

Bart Friederichs
Bart Friederichs

Reputation: 33573

The problem lies in your INSERT statement, not the variable assignment. To insert a NULL value, your statement should look like this (note, no quotes around NULL):

INSERT INTO table (column) VALUES (NULL);

You could create some extra code to add or leave out these quotes, but I strongly advice to use prepared statements, where you don't have to worry about escaping and quotes anymore.

Upvotes: 2

Related Questions