MySQL and PHP - insert NULL rather than empty string

Viewed 457 times

I have a MySQL statement that inserts some variables into the database. I recently added 2 fields which are optional ($intLat, $intLng). Right now, if these values are not entered I pass along an empty string as a value. How do I pass an explicit NULL value to MySQL (if empty)?

$query = "INSERT INTO data (notes, id, filesUploaded, lat, lng, intLat, intLng)
          VALUES ('$notes', '$id', TRIM('$imageUploaded'), '$lat', '$long', 
                  '$intLat', '$intLng')";
mysql_query($query);

Answers

To pass a NULL to MySQL, you do just that.

INSERT INTO table (field,field2) VALUES (NULL,3)

So, in your code, check if $intLat, $intLng are empty, if they are, use NULL instead of '$intLat' or '$intLng'.

$intLat = !empty($intLat) ? "'$intLat'" : "NULL";
$intLng = !empty($intLng) ? "'$intLng'" : "NULL";

$query = "INSERT INTO data (notes, id, filesUploaded, lat, lng, intLat, intLng)
          VALUES ('$notes', '$id', TRIM('$imageUploaded'), '$lat', '$long', 
                  $intLat, $intLng)";

Tuesday, September 13, 2022

notem25971-01dfa93f8bfc

You have at least these two options:

$rows = [(1,2,3), (4,5,6), (7,8,9) ... ];

$sql = "insert into `table_name` (col1, col2, col3) values (?,?,?)";

$stmt = $db->prepare($sql);

foreach($rows as $row)
{
    $stmt->execute($row);
}

OR:

$rows = [(1,2,3), (4,5,6), (7,8,9) ... ];

$sql = "insert into `table_name` (col1, col2, col3) values ";

$paramArray = array();

$sqlArray = array();

foreach($rows as $row)
{
    $sqlArray[] = '(' . implode(',', array_fill(0, count($row), '?')) . ')';

    foreach($row as $element)
    {
        $paramArray[] = $element;
    }
}

// $sqlArray will look like: ["(?,?,?)", "(?,?,?)", ... ]

// Your $paramArray will basically be a flattened version of $rows.

$sql .= implode(',', $sqlArray);

$stmt = $db->prepare($sql);

$stmt->execute($paramArray);

As you can see the first version features a lot simpler code; however the second version does execute a batch insert. The batch insert should be faster, but I agree with @BillKarwin that the performance difference will not be noticed in the vast majority of implementations.

Thursday, September 22, 2022

angelo_parente

pass like this

date('Y-m-d H:i:s','1299762201428')

Friday, September 30, 2022

digilive

I would rather suggest to check $_POST paramenters before definied them so if a variable is not empty set values otherwise set as NULL

if(!empty($_POST['dateRequest'])) { $dateRequest = $_POST['dateRequest']; } else { $dateRequest = NULL; }
if(!empty($_POST['firstName'])) { $firstName = $_POST['firstName']; } else { $firstName  = NULL; }
if(!empty($_POST['lastName'])) { $lastName = $_POST['lastName']; } else { $lastName = NULL; }
if(!empty($_POST['lastName'])) { $note = $_POST['note']; } else { $note = NULL; }

This will prevent you to pass empty parameters in your query.

Sunday, December 11, 2022

jonathan_kehayias

Then you should just loop through array elements, check each value for null and replace it with empty string. Something like that:

foreach ($array as $key => $value) {
    if (is_null($value)) {
         $array[$key] = "";
    }
}

Also, you can implement checking function and use array_map() function.

Thursday, December 15, 2022

xml

Only authorized users can answer the search term. Please sign in first, or register a free account.

Not the answer you're looking for? Browse other questions tagged :

php

mysql

null

sql-insert

MySQL and PHP - insert NULL rather than empty string

Answers

php,mysql,null,sql-insert