Null values inserted in database table using php pdo

Question

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

Make sure you haven't hit any errors before you try submitting. Either create a flag variable (false at the beginning, set to true in any of the ifs), or check for any of the error messages. — aynber
– aynber, Commented Feb 19, 2019 at 17:35

MihanEntalpo · Accepted Answer · 2019-02-20 05:45:52Z

1

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)]);

}

edited Feb 20, 2019 at 5:45

answered Feb 19, 2019 at 17:40

MihanEntalpo

2,1022 gold badges17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Asif Over a year ago

SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'lastname' cannot be null I created that function and used that. Thenceforth this code above appears at the top of my page how do I remove it!

tadman Over a year ago

Add a validation for that column.

Asif Over a year ago

How can you explain more?

Alex Howansky · Accepted Answer · 2019-02-19 17:35:30Z

0

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;
    }
}

answered Feb 19, 2019 at 17:35

Alex Howansky

54.1k8 gold badges85 silver badges102 bronze badges

2 Comments

Asif Over a year ago

It is good idea to push all errors in one array then display it all together, but I would like to write error of every single input below it! how do I do that

Alex Howansky Over a year ago

Then just use the form element's id attribute as a key into the array: $errors['name'] = "You must provide a name."; Then when you're rendering <input id="name" ... >, you can check to see if $errors['name'] exists and if so, render the message nearby.

Collectives™ on Stack Overflow

Null values inserted in database table using php pdo

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related