Null values inserted in database table using php pdo

181
February 20, 2019, at 6:40 PM

I made a PHP interface for teacher table. All columns in table is set to NOT NULL. If I submit the form with empty inputs. Empty values will be submitted to my database table teacher. I can't understand if columns of the table is set to not null why database table accepts null values from my user interface.

   if(filter_has_var(INPUT_POST, "add_teacher")){
    function test_input($data){
        $data = stripslashes($data);
        $data = trim($data);
        $data = htmlspecialchars($data);
        return $data;
    }
    if(empty($_POST["firstname"])){
        $firstname_err = "* Firstname is required!";
    } else {
        if(!preg_match("/^[a-zA-Z ]*$/",$_POST["firstname"])){
            $firstname_err = "Invalid Firstname"; 
        } else if (!test_input($_POST["firstname"])){
            $firstname_err = "Invalid firstName, please enter a valid first name!";  
        } else {
            $firstname = $_POST["firstname"];
        }
    }
    if(empty($_POST["lastname"])){
        $lastname_err = "* Last name is required!";
    } else {
        if(!preg_match("/^[a-zA-Z ]*$/",$_POST["lastname"])){
            $lastname_err = "Invalid last name";
        } else if (!test_input($_POST["lastname"])){
            $lastname_err = "Invalid last name, please enter a valid last name!";
        } else {
            $lastname = $_POST["lastname"];
        }
    }
    if(empty($_POST["DOB"])){
        $DOB_err = "* Date of birth is a required field!";
    } else {
        $DOB = $_POST["DOB"];
    }

    if(empty($_POST["gender"])){
        $gender_err = "* Gender is a required field!";
    } else {
        $gender = $_POST["gender"];
    }
    if(empty($_POST["tazkira_number"])){
        $tazkira_number_err = "* This is a required field";
    } else {
        if(!filter_var($_POST["tazkira_number"], FILTER_VALIDATE_INT)){
            $tazkira_number_err = "* Only numbers are allowed";
        } else if(!test_input($_POST["tazkira_number"])){
            $tazkira_number_err = "* Invalid data entered";
        }
        else {
            $tazkira_number = $_POST["tazkira_number"];
        }
    }
    if(empty($_POST["phone_number"])){
        $phone_number_err = "* This is a required field";
    } else {
        $phone_number = $_POST["phone_number"];
    }

    if(empty($_POST["academic_field"])){
        $academic_field_err = "* Academic field is required!";
    } else {
        if(!preg_match("/^[a-zA-Z ]*$/",$_POST["academic_field"])){
            $academic_field_err = "Invalid academic field.";
        } else if (!test_input($_POST["academic_field"])){
            $academic_field_err = "Invalid academic field, please enter a valid academic field!";
        } else {
            $academic_field = $_POST["academic_field_err"];
        }
    }
    if(empty($_POST["email"])){
        $email_err = "* Email field is required!";
    } else {
        if(!filter_var($_POST["email"], FILTER_VALIDATE_EMAIL)){
            $email_err = "Invalid email entered";
        } else if (!test_input($_POST["email"])){
            $academic_femail_err = "Invalid data, please enter a valid email address!";
        } else {
            $email = $_POST["email"];
        }
    }
    if(empty($_POST["position"])){
        $position_err = "* Position field is required!";
    } else {
        if(!preg_match("/^[a-zA-Z ]*$/",$_POST["position"])){
            $position_err = "* Invalid data"; 
        } else if (!test_input($_POST["position"])){
            $position_err = "* Invalid data, please enter a valid position!";
        } else {
            $position = $_POST["position"];
        }
    }
    if(empty($_POST["hire_date"])){
        $hire_date_err = "* Hire date is a required field!";   
    } else {
        $hire_date = $_POST["hire_date"];
    }
    $resign_date = $_POST["resign_date"];
    $sql = "INSERT INTO teacher (firstname, lastname, DOB, gender, tazkira_number, phone_number, academic_field, email, position, hire_date, resign_date) VALUES (:firstname, :lastname, :DOB, :gender, :tazkira_number, :phone_number, :academic_field, :email, :position, :hire_date, :resign_date)";
    $stmt = $conn->prepare($sql);
    $res = $stmt->execute(["firstname"=> $firstname, "lastname" => $lastname, "DOB" => $DOB,  "gender" => $gender, "tazkira_number" => $tazkira_number, "phone_number" => $phone_number, "academic_field" => $academic_field,  "email" => $email, "position" => $position, "hire_date" => $hire_date,  "resign_date" => $resign_date]);
    $add_teacher_success_msg = "New teacher added successfully!";
}

As you can see above this code inserts into teacher some values. If I don't write anything in input of the form and click submit. Null or empty values will be submitted to table. Please help me solve this problem. Thank you

Answer 1

You're checking for invalid values, but not doing anything if you find them. I.e., you're always running the INSERT, no matter what errors you find. I'd recommend not using a separate variable for each error, but instead append errors to an array:

$errors = [];
if (empty($_POST["email"])) {
    $errors[] = 'Email is required.';
}
if (empty($_POST["academic_field"])) {
    $errors[] = "Academic field is required.";
}
// and so on...

Then, you can just check to see if $errors is empty to know if you have any errors:

if (empty($errors)) {
    // No errors, try the insert.
    $sql = "INSERT INTO teacher ...";
    $stmt = $conn->prepare($sql);
    $res = $stmt->execute(...);
} else {
    // Display the errors.
    echo "You have errors:";
    foreach ($errors as $error) {
        echo $error;
    }
}
Answer 2

Your "empty values" are actually not "null" values but "empty strings" (strings with zero characters).

To make PDO recognize them as NULL values, you have to convert empty strings into null before you inserting them into database.

For example, you could create function:

// e2n means "empty to null", and made shorter for more convinient usage:
function e2n($src)
{
   if (is_string($src) && trim($src) == "")
   {
       return null;
   }
   else
   {
       return $src;
   }
}

And use it like:


$sql = "INSERT INTO teacher (firstname, lastname, DOB, gender, tazkira_number, phone_number, academic_field, email, position, hire_date, resign_date) VALUES (:firstname, :lastname, :DOB, :gender, :tazkira_number, :phone_number, :academic_field, :email, :position, :hire_date, :resign_date)";
    $stmt = $conn->prepare($sql);
    $res = $stmt->execute(["firstname"=> e2n($firstname), "lastname" => e2n($lastname), "DOB" => e2n($DOB),  "gender" => e2n($gender), "tazkira_number" => e2n($tazkira_number), "phone_number" => e2n($phone_number), "academic_field" => e2n($academic_field),  "email" => e2n($email), "position" => e2n($position), "hire_date" => e2n($hire_date),  "resign_date" => e2n($resign_date)]);

Also, I recommend you to refactor your algorythm, so you have some array of fields, and names of validators, that are used for them, and walk through fields, running corresponding validators, and also make e2n conversion in place.

About error "SQLSTATE[23000]: Integrity constraint violation: 1048":

To skip insertion of data, you should add testing for your *_err variables:

$isOk = true;
//All your Error fields
$err_fields = ['firstname_err', 'lastname_err', 'DOB_err', 'gender_err', 'tazkira_number_err', 'phone_number_err', 'position_err', 'academic_field_err', 'email_err', 'hire_date_err'];
foreach ($err_fields as $field)
{
   if (isset($$field) && $$field)
   {
      echo "You have error!<br>";
      $isOk = false;
   }   
}
if ($isOk)
{
    // Running SQL if there were no errors:
    $sql = "INSERT INTO teacher (firstname, lastname, DOB, gender, tazkira_number, phone_number, academic_field, email, position, hire_date, resign_date) VALUES (:firstname, :lastname, :DOB, :gender, :tazkira_number, :phone_number, :academic_field, :email, :position, :hire_date, :resign_date)";
    $stmt = $conn->prepare($sql);
    $res = $stmt->execute(["firstname"=> e2n($firstname), "lastname" => e2n($lastname), "DOB" => e2n($DOB),  "gender" => e2n($gender), "tazkira_number" => e2n($tazkira_number), "phone_number" => e2n($phone_number), "academic_field" => e2n($academic_field),  "email" => e2n($email), "position" => e2n($position), "hire_date" => e2n($hire_date),  "resign_date" => e2n($resign_date)]);
}
Rent Charter Buses Company
READ ALSO
PHP SESSION handling - invalid values

PHP SESSION handling - invalid values

We need to make use of session_write_close() inside a lot of existing codesBut this looks dangerous to us, because PHP does the following - without giving any warning or error:

153
Google Tasks API: how to get the completed tasks in a tasklist?

Google Tasks API: how to get the completed tasks in a tasklist?

How to get the completed tasks in a tasklist? I have tried the following:

252
PHPSpreadsheet - How do create text that is red colored?

PHPSpreadsheet - How do create text that is red colored?

I use the next line to color a cell red:

367
MySQl Error while sending QUERY packet if Connection not closed and Reopened

MySQl Error while sending QUERY packet if Connection not closed and Reopened

I'm getting the Warning Error while sending QUERY packet in PID= when I use a SELECT statement that only gets 5 rows of data from 2 columns, then make the script sleep for 10 minutes before waking up to continue an INSERT into the db

180