Inserting NULL values

Databases
1

When I try to INSERT like

INSERT INTO utilities (name,type,capacity,serving, circuit, meter_load, voltage,frequency,created_by,created_date,notes,enabled ) VALUES ( 'Addidas','Electrolite Supplier','','',,,,'lurtnowski@domain.com',CURRENT_TIMESTAMP,'',1)

I get an error, but when I add ‘’, it worls but I get errors

image
image1472×666 45 KB

Heres my table structure
image
image1548×747 115 KB

shouldnt it work?

2

If you want to insert a null, simply dont insert that field.

INSERT INTO utilities (name,type,capacity,serving, circuit, meter_load, voltage,frequency,created_by,created_date,notes,enabled ) VALUES ( 'Addidas','Electrolite Supplier','','',,,,'lurtnowski@industechnology.com',CURRENT_TIMESTAMP,'',1)
=>
INSERT INTO utilities (name,type,capacity,serving,created_by,created_date,notes,enabled ) VALUES ( 'Addidas','Electrolite Supplier','','','lurtnowski@industechnology.com',CURRENT_TIMESTAMP,'',1)

3

NULL is not the same as an empty string. An empty string has a type.

4

Or if the field sometime has a value, and other times should be null. When it should be null, insert NULL without quotes.

5

you can’t do that

commas are used to separate values

you need either a value or the keyword NULL – you can’t just put nothing

6

ok, trying to set the value to NULL if the variable has nothing in it


   echo '<pre>';print_r($_POST);echo '</pre>';
	       
      $Name = $_POST['Name'];
      $Type = $_POST['Type'];
      $Serving = $_POST['Serving'];
   if(empty($_POST['Frequency'])) { $Frequency = Null; } else { $Frequency = $_POST['Frequency']; }
      $Voltage = $_POST['Voltage'];
      $Frequency = $_POST['Frequency'];
      $Capacity = $_POST['Capacity'];
      $Circuit = $_POST['Circuit'];
      $Enabled = $_POST['enabled'];
      $meter_load = $_POST['meter_load'];
      $Notes = $_POST['notes'];
     
      $sql = "INSERT INTO utilities (name,type,capacity,serving, circuit, meter_load, voltage,frequency,created_by,created_date,notes,enabled
	          ) VALUES (
			  '".$Name."','".$Type."','".$Capacity."','".$Serving."',".$Circuit.",".$meter_load.",".$Voltage.",".$Frequency.",'".$_SESSION['email']."',CURRENT_TIMESTAMP,'".$Notes ."',".$Enabled.")";
	  echo $sql;

produces

Array
(
    [Name] => Blockbuster
    [Type] => Computer Supplier
    [Serving] => 
    [Capacity] => 
    [Circuit] => 
    [meter_load] => 
    [Voltage] => 
    [Frequency] => 
    [enabled] => 1
    [notes] => 
)
INSERT INTO utilities (name,type,capacity,serving, circuit, meter_load, voltage,frequency,created_by,created_date,notes,enabled ) VALUES ( 'Blockbuster','Computer Supplier','','',,,,,'lurtnowski@industechnology.com',CURRENT_TIMESTAMP,'',1)

why doesn’t my iff statement work to make the value NULL?

I can use
https://www.php.net/manual/en/function.empty.php
to check for ‘’, but how about and emplt number?

7

You still can’t do it that way.

You either need to have code like this:

INSERT INTO Table1 (field1, field2, field3) VALUES ('1', NULL, '3')

or you need to have something like this:

INSERT INTO Table1 (field1, field3) VALUES ('1', '3')

Your code would give option 3, which doesn’t work…

INSERT INTO Table1 (field1, field2, field3) VALUES ('1', , '3')

This is an ULTRA dumbed down version (no sql injection protection, should be using parameterized queries, etc) but essentially will give option #2 above.

echo '<pre>';print_r($_POST);echo '</pre>';

$insertPortion = "INSERT INTO utilities(";
$valuesPortion = ") VALUES (";

if(!empty($_POST['Name'])) {
	$insertPortion .= "name, ";
	$valuesPortion .= "'" . $_POST['Name'] . "', ";
}
if(!empty($_POST['Type'])) {
	$insertPortion .= "type, ";
	$valuesPortion .= "'" . $_POST['Type'] . "', ";
}
if(!empty($_POST['Capacity'])) {
	$insertPortion .= "capacity, ";
	$valuesPortion .= "'" . $_POST['Capacity'] . "', ";
}
if(!empty($_POST['Serving'])) {
	$insertPortion .= "serving, ";
	$valuesPortion .= "'" . $_POST['Serving'] . "', ";
}
if(!empty($_POST['Circuit'])) {
	$insertPortion .= "circuit, ";
	$valuesPortion .= $_POST['Circuit'] . ", ";
}
if(!empty($_POST['meter_load'])) {
	$insertPortion .= "meter_load, ";
	$valuesPortion .= $_POST['meter_load'] . ", ";
}
if(!empty($_POST['Voltage'])) {
	$insertPortion .= "voltage, ";
	$valuesPortion .= $_POST['Voltage'] . ", ";
}
if(!empty($_POST['Frequency'])) {
	$insertPortion .= "frequency, ";
	$valuesPortion .= $_POST['Frequency'] . ", ";
}
if(!empty($_SESSION['email'])) {
	$insertPortion .= "created_by, ";
	$valuesPortion .= "'" . $_SESSION['email'] . "', ";
}
	$insertPortion .= "created_date, ";
	$valuesPortion .= "'CURRENT_TIMESTAMP, ";
if(!empty($_POST['Notes'])) {
	$insertPortion .= "notes, ";
	$valuesPortion .= "'" . $_POST['Notes'] . "', ";
}
if(!empty($_POST['enabled'])) {
	$insertPortion .= "enabled, ";
	$valuesPortion .= $_POST['enabled'] . ", ";
}
$sql = substring($insertPortion, 0, -3) . substring($valuesPortion, 0, -3) . ")"
8

You guys are way overthinking this. A simple function will take care of this problem.

Stripped down example…

<?php
$hostdb = 'localhost';
$dbname = 'test';
$username = 'root';
$password = '';
$pdo = new PDO("mysql:host=localhost;dbname=$dbname", $username, $password);

function empty2null($value)
{
    return $value === '' ? null : $value;
}

$_POST['name'] = 'Addidas';
$_POST['circuit'] = '';

$sql = "INSERT INTO utilities (name, circuit) VALUES (?,?)";
$stmt = $pdo->prepare($sql);
$stmt->execute([$_POST['name'], empty2null($_POST['circuit'])]);
10

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.