Set the value as null and not as string null when updating the database

[maryjane9110024]

I am trying to update the database with isset to set the value of the variable to either null or the value entered by the user. Currently, if no value has been entered null is being set as a string causing the date field to populate with zeros. How do I code it so that it populates or sets the value as null and not as string null?

 

Here's my code: (yes I know sql injections it's still in development  )

<?php

require_once('../config.php');

 

$parcel_id = isset($_GET['parcel_id']) ? $_GET['parcel_id'] : null;

//$appeal_address_match = isset($_POST['AppealAddressMatch']) ? 1 : 0; (add to sql statement -- appeal_address_match='". $appeal_address_match ."',)

$date_appeal_received = isset($_POST['DateAppealReceived']) ? $_POST['DateAppealReceived'] : null;

$bos_meeting_date = isset($_POST['BosMeetingDate']) ? $_POST['BosMeetingDate'] : null;

$late_returns_date = isset($_POST['LateReturnsDate']) ? $_POST['LateReturnsDate'] : null;

$determination_notice = isset($_POST['DeterminationNoticeSet']) ? $_POST['DeterminationNoticeSet'] : null;

$final_determination = isset($_POST['FinalDetermination']) ? $_POST['FinalDetermination'] : null;

$analysis_recommendation = isset($_POST['AnalysisRecommendation']) ? $_POST['AnalysisRecommendation'] : null;

$email_address = isset($_POST['EmailAddress']) ? $_POST['EmailAddress'] : null;

$phone_number = isset($_POST['PhoneNumber']) ? $_POST['PhoneNumber'] : null;

 

if(isset($_GET['parcel_id'])) {

$db = new ezSQL_mysql(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);

$result = $db->query("UPDATE parcels SET date_appeal_received='" . $date_appeal_received . "', 

bos_meeting_date='". $date_appeal_received ."', 

late_returns_date='". $late_returns_date ."', 

determination_notice_sent_date='". $determination_notice ."', 

final_determination='". $final_determination ."', 

analysis_recommendation='". $analysis_recommendation ."', 

email_address='". $email_address ."', 

phone_number='". $phone_number ."'

WHERE parcel_id='" . $parcel_id ."'");

/*if ($result == TRUE) {

echo '{"success":true}';

} else {

echo '{"success":false}';

} */

 

if($result) echo 'true';

else echo 'false';

 

}

 

?>

 

[ginerjm]

I believe you have to set your local vars to 'null' but in the query you DON"T wrap that var in quotes.

[Ch0cu3r]

It is because you have wrapped the date values within single quotes in your query here

bos_meeting_date='". $date_appeal_received ."',    <-- wrapped in single quotes
late_returns_date='". $late_returns_date ."',      <-- and here

When your query is executed and the date values are null it will result in the query becoming

bos_meeting_date='',   <-- if the date value is null it will result in an empty value
late_returns_date='',  <-- same here

If you do not want to add a value to a field if it is null (or empty) then do not include that field in your insert query. You should dynamically build the query. In your table schema make sure to apply DEFAULT NULL to those fields which do not require a value.

[maryjane9110024]

I tried removing the quotes and having it as bos_meeting_date=". $date_appeal_received .", but it bombs when there is a value. 

 

How do I dynamically build a query? I get a different answer every link I looked at in my Google results. I just checked and the default is null for those fields. 

Edited January 13, 2015 by maryjane9110024

[Ch0cu3r]

A query is defined within a string.All you do is concatenate the field/value pairs to the string to define the query. Once the query has been built you pass it to $db->query to execute it.

// start to define the query
$query = 'UPDATE parcels SET ';

// now add the date_appeal_received field value if it is not null/empty
if(!empty($date_appeal_received))
   $query .= "date_appeal_received='$date_appeal_received', ";

// now add the bos_meeting_datefield value if it is not null/empty
if(!empty($bos_meeting_date))
   $query .= "bos_meeting_date='$bos_meeting_date', ";

// and so on

// remove the comma after the last field/value pair
$query = rtrim(', ', $query);

// now add the where clause
$query .= " WHERE parcel_id='$parcel_id'";

// Once the query has been built, execute it
$result = $db->query($query);

Edited January 13, 2015 by Ch0cu3r

[QuickOldCar]

default pattern in the database is zeros for date

 

What you should do instead is not let anything be added to the database if a POST field is empty

 

First off are you using GET for parcel id and also POST through a form?

If the form is used the parcel id would be lost unless you passed it in a hidden value in the form, and then you check it as POST

If need to do something odd like this might as well use REQUEST for the parcel id and can pass it back to the form in a hidden value so is always there.

 

This is how I would handle it.

You can pass the parcel id back into form as a hidden value.

Can also pass the other variables back into the form.

 

EDIT : wrote this before Ch0cu3r's advice, I missed this was an update

<?php
$errors = array();

if (isset($_REQUEST['parcel_id']) && trim($_REQUEST['parcel_id']) != '') { //can check the expected type as well
    $parcel_id = trim($_REQUEST['parcel_id']);
} else {
    $parcel_id = '';
}


if (isset($_POST['submit'])) {
   
    if ($parcel_id == '') {
        $errors[] = "No parcel id";
    }
   
    if (isset($_POST['DateAppealReceived']) && trim($_POST['DateAppealReceived']) != '') {
        $date_appeal_received = trim($_POST['DateAppealReceived']);
    } else {
        $errors[] = "No date appeal received";
  $date_appeal_received = '';
    }
   
    if (isset($_POST['BosMeetingDate']) && trim($_POST['BosMeetingDate']) != '') {
        $bos_meeting_date = trim($_POST['BosMeetingDate']);
    } else {
        $errors[] = "No bos meeting date";
  $bos_meeting_date = '';
    }
   
    if (isset($_POST['LateReturnsDate']) && trim($_POST['LateReturnsDate']) != '') {
        $late_returns_date = trim($_POST['LateReturnsDate']);
    } else {
        $errors[] = "No late returns date";
  $late_returns_date = '';
    }
   
    if (isset($_POST['DeterminationNoticeSet']) && trim($_POST['DeterminationNoticeSet']) != '') {
        $determination_notice = trim($_POST['DeterminationNoticeSet']);
    } else {
        $errors[] = "No determination notice";
  $determination_notice = '';
    }
   
    if (isset($_POST['FinalDetermination']) && trim($_POST['FinalDetermination']) != '') {
        $final_determination = trim($_POST['FinalDetermination']);
    } else {
        $errors[] = "No final determination";
  $final_determination = '';
    }
   
    if (isset($_POST['AnalysisRecommendation']) && trim($_POST['AnalysisRecommendation']) != '') {
        $analysis_recommendation = trim($_POST['AnalysisRecommendation']);
    } else {
        $errors[] = "No analysis recommendation";
  $analysis_recommendation = '';
    }
   
    if (isset($_POST['EmailAddress']) && trim($_POST['EmailAddress']) != '') {
        $email_address = trim($_POST['EmailAddress']);
    } else {
        $errors[] = "No email";
  $email_address = '';
    }
   
    if (isset($_POST['PhoneNumber']) && trim($_POST['PhoneNumber']) != '') {
        $phone_number = trim($_POST['PhoneNumber']);
    } else {
        $errors[] = "No email";
  $phone_number = '';
    }
   
   
} //end post submit


if (empty($errors)) {
   
    $db     = new ezSQL_mysql(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);
    $result = $db->query("UPDATE parcels SET date_appeal_received='" . $date_appeal_received . "',
bos_meeting_date='" . $date_appeal_received . "',
late_returns_date='" . $late_returns_date . "',
determination_notice_sent_date='" . $determination_notice . "',
final_determination='" . $final_determination . "',
analysis_recommendation='" . $analysis_recommendation . "',
email_address='" . $email_address . "',
phone_number='" . $phone_number . "'
WHERE parcel_id='" . $parcel_id . "'");
   
    if ($result) {
        echo 'submitted';
    } else {
        echo 'error submitting';
    }
   
} else {
   
    foreach ($errors as $error) {
        echo "$error <br />";
    }
   
}
?>

Edited January 13, 2015 by QuickOldCar

[Barand]

I do not know if it works with PDO but prepared statements with MySQLi will write NULL to a field if the bound parameter has a null value

[maryjane9110024]

Thank you all for the information!! What if someone was deleting an entry that was entered? How would that update the value to null or empty?

[Barand]

Example:

mysql> SELECT * FROM city;
+----+------+------+------------+
| id | xpos | ypos | cityname   |
+----+------+------+------------+
|  1 |   40 |   60 | Manchester |
|  2 |   80 |   60 | Leeds      |
|  3 |   90 |  160 | Leicester  |
+----+------+------+------------+

mysql> UPDATE city SET cityname=NULL WHERE id = 2;

mysql> SELECT * FROM city;
+----+------+------+------------+
| id | xpos | ypos | cityname   |
+----+------+------+------------+
|  1 |   40 |   60 | Manchester |
|  2 |   80 |   60 | NULL       |
|  3 |   90 |  160 | Leicester  |
+----+------+------+------------+

[maryjane9110024]

yea that I know but I'm talking about with what Ch0cu3r said in his post above.

Edited January 13, 2015 by maryjane9110024

[maryjane9110024]

Quickoldcar, I wish I could prevent them but with the form if they remove information and want to update then it will display and insert zeros into the database which is what i don't want to happen. so if the user removes the date from the field i want it to be null instead of zeros if that makes any sense?

[Barand]

start values again

mysql> SELECT * FROM city;
+----+------+------+------------+
| id | xpos | ypos | cityname   |
+----+------+------+------------+
|  1 |   40 |   60 | Manchester |
|  2 |   80 |   60 | Leeds      |
|  3 |   90 |  160 | Leicester  |
+----+------+------+------------+

Now this code, where id=2 and ypos left blank

$db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE);

if (isset($_POST['id'])) {
    $id = $_POST['id'];                                           // 2
    $ypos = empty($_POST['ypos']) ? null : $_POST['ypos'];        // empty
    
    $sql = "UPDATE city SET ypos = ? WHERE id = ?";
    $stmt = $db->prepare($sql);
    $stmt->bind_param('ii', $ypos, $id);
    $stmt->execute();
}

Tables values after running code

mysql> SELECT * FROM city;
+----+------+------+------------+
| id | xpos | ypos | cityname   |
+----+------+------+------------+
|  1 |   40 |   60 | Manchester |
|  2 |   80 | NULL | Leeds      |
|  3 |   90 |  160 | Leicester  |
+----+------+------+------------+