Getting the following error while posting data PDO, MYSQL

Question

I keep getting an error like this when I submit a form. I have no clue what exactly is wrong in what I am doing here.

Kindly have a look at my code below please:

SQLSTATE[23000]: Integrity constraint violation: 1048 Le champ 'r_maritalstatus' ne peut être vide (null)

In English the message reads, "The column 'r_maritalstatus' may not be empty (null)."

If I select a value in r_maritalstatus then error shows for other field.

Here is this code:

if(!isset($error)){
        try {

        $stmt = $db->prepare('INSERT INTO resumes (memberID, r_name,r_email,r_mobile,r_address,r_dob,r_maritalstatus,r_nationality,r_religion,r_sex,r_objective,r_languagesknown,r_hobbies,r_expcategory,r_exptype,r_exptitle1,r_expcompany1,r_expcity1,r_expfrom1,r_expto1,r_expdescription1,r_exptitle2,r_expcompany2,r_expcity2,r_expfrom2,r_expto2,r_expdescription2,r_exptitle3,r_expcompany3,r_expcity3,r_expfrom3,r_expto3,r_expdescription3,r_exptitle4,r_expcompany4,r_expcity4,r_expfrom4,r_expto4,r_expdescription4,r_exptitle5,r_expcompany5,r_expcity5,r_expfrom5,r_expto5,r_expdescription5,r_eduinstname1,r_edudegree1,r_edustudyfield1,r_educity1,r_edupassed1,r_eduinstname2,r_edudegree2,r_edustudyfield2,r_educity2,r_edupassed2,r_eduinstname3,r_edudegree3,r_edustudyfield3,r_educity3,r_edupassed3,r_eduinstname4,r_edudegree4,r_edustudyfield4,r_educity4,r_edupassed4,r_eduinstname5,r_edudegree5,r_edustudyfield5,r_educity5,r_edupassed5,r_skill1,r_skillexp1,r_skill2,r_skillexp2,r_skill3,r_skillexp3,r_skill4,r_skillexp4,r_skill5,r_skillexp5) VALUES (:memberID, :r_name, :r_email, :r_mobile, :r_address, :r_dob, :r_maritalstatus, :r_nationality, :r_religion, :r_sex, :r_objective, :r_languagesknown, :r_hobbies, :r_expcategory, :r_exptype, :r_exptitle1, :r_expcompany1, :r_expcity1, :r_expfrom1, :r_expto1, :r_expdescription1, :r_exptitle2, :r_expcompany2, :r_expcity2, :r_expfrom2, :r_expto2, :r_expdescription2, :r_exptitle3, :r_expcompany3, :r_expcity3, :r_expfrom3, :r_expto3, :r_expdescription3, :r_exptitle4, :r_expcompany4, :r_expcity4, :r_expfrom4, :r_expto4, :r_expdescription4, :r_exptitle5, :r_expcompany5, :r_expcity5, :r_expfrom5, :r_expto5, :r_expdescription5, :r_eduinstname1, :r_edudegree1, :r_edustudyfield1, :r_educity1, :r_edupassed1, :r_eduinstname2, :r_edudegree2, :r_edustudyfield2, :r_educity2, :r_edupassed2, :r_eduinstname3, :r_edudegree3, :r_edustudyfield3, :r_educity3, :r_edupassed3, :r_eduinstname4, :r_edudegree4, :r_edustudyfield4, :r_educity4, :r_edupassed4, :r_eduinstname5, :r_edudegree5, :r_edustudyfield5, :r_educity5, :r_edupassed5, :r_skill1, :r_skillexp1, :r_skill2, :r_skillexp2, :r_skill3, :r_skillexp3, :r_skill4, :r_skillexp4, :r_skill5, :r_skillexp5)');         
        $stmt->execute(array(
                     ':memberID' => $uid,
                     ':r_name' => $rname,
                     ':r_email' => $remail,
                     ':r_mobile' => $rmobile,
                     ':r_address' => $raddress,
                     ':r_dob' => $rdob, 
                     ':r_sex' => $rsex,
                     ':r_maritalstatus' => $rmaritalstatus,
                     ':r_nationality' => $rnationality,
                     ':r_religion' => $rreligion,
                     ':r_objective' => $robjective,
                     ':r_languagesknown' => $rlanguagesknown,
                     ':r_hobbies' => $rhobbies,
                     ':r_expcategory' => $rjobcategory,
                     ':r_exptype' => $rjobtype, 
                     ':r_exptitle1' => $rjobtitle1, 
                     ':r_expcompany1' => $rjobcompany1,
                     ':r_expcity1' => $rjobcity1, 
                     ':r_expfrom1' => $rjobfrom1, 
                     ':r_expto1' => $rjobto1, 
                     ':r_expdescription1' => $rjobdescription1,  
                     ':r_exptitle2' => $rjobtitle2, 
                     ':r_expcompany2' => $rjobcompany2, 
                     ':r_expcity2' => $rjobcity2, 
                     ':r_expfrom2' => $rjobfrom2, 
                     ':r_expto2' => $rjobto2, 
                     ':r_expdescription2' => $rjobdescription2,  
                     ':r_exptitle3' => $rjobtitle3, 
                     ':r_expcompany3' => $rjobcompany3, 
                     ':r_expcity3' => $rjobcity3, 
                     ':r_expfrom3' => $rjobfrom3, 
                     ':r_expto3' => $rjobto3, 
                     ':r_expdescription3' => $rjobdescription3,  
                     ':r_exptitle4' => $rjobtitle4, 
                     ':r_expcompany4' => $rjobcompany4, 
                     ':r_expcity4' => $rjobcity4, 
                     ':r_expfrom4' => $rjobfrom4, 
                     ':r_expto4' => $rjobto4, 
                     ':r_expdescription4' => $rjobdescription4,  
                     ':r_exptitle5' => $rjobtitle5, 
                     ':r_expcompany5' => $rjobcompany5, 
                     ':r_expcity5' => $rjobcity5, 
                     ':r_expfrom5' => $rjobfrom5, 
                     ':r_expto5' => $rjobto5, 
                     ':r_expdescription5' => $rjobdescription5,  
                     ':r_eduinstname1' => $reduinst1, 
                     ':r_edudegree1' => $redudegree1, 
                     ':r_edustudyfield1' => $redustudyfield1, 
                     ':r_educity1' => $reducity1, 
                     ':r_edupassed1' => $reduyear1,  
                     ':r_eduinstname2' => $reduinst2, 
                     ':r_edudegree2' => $redudegree2, 
                     ':r_edustudyfield2' => $redustudyfield2, 
                     ':r_educity2' => $reducity2, 
                     ':r_edupassed2' => $reduyear2,  
                     ':r_eduinstname3' => $reduinst3, 
                     ':r_edudegree3' => $redudegree3, 
                     ':r_edustudyfield3' => $redustudyfield3, 
                     ':r_educity3' => $reducity3, 
                     ':r_edupassed3' => $reduyear3,  
                     ':r_eduinstname4' => $reduinst4, 
                     ':r_edudegree4' => $redudegree4, 
                     ':r_edustudyfield4' => $redustudyfield4, 
                     ':r_educity4' => $reducity4, 
                     ':r_edupassed4' => $reduyear4,  
                     ':r_eduinstname5' => $reduinst5, 
                     ':r_edudegree5' => $redudegree5, 
                     ':r_edustudyfield5' => $redustudyfield5, 
                     ':r_educity5' => $reducity5, 
                     ':r_edupassed5' => $reduyear5,  
                     ':r_skill1' => $rskill1,
                     ':r_skillexp1' => $rskillexp1, 
                     ':r_skill2' => $rskill2,
                     ':r_skillexp2' => $rskillexp2, 
                     ':r_skill3' => $rskill3,
                     ':r_skillexp3' => $rskillexp3, 
                     ':r_skill4' => $rskill4,
                     ':r_skillexp4' => $rskillexp4, 
                     ':r_skill5' => $rskill5,
                     ':r_skillexp5' => $rskillexp5
         ));

        } catch(PDOException $e) {
            $error[] = $e->getMessage();
        }
    }

echo print_r($_POST);
}

thanks and regards

so where does `$rmaritalstatus` get defined? did you confirm that your massive pile of placeholders and fields are actually aligned properly, and you didn't miss one somewhere? — Marc B, Jul 06 '16 at 15:24
My French is kinda rusty but I'm guessing you've got a `NOT NULL` constraint on a field into which you're attempting to insert a `NULL` value... comme çi : http://stackoverflow.com/questions/20058252/sqlstate23000-integrity-constraint-violation-1048-column-post-cannot-be-nu — CD001, Jul 06 '16 at 15:25
im confused, is this related with placeholder in the form u mean? — rodrix, Jul 06 '16 at 15:26
When dealing with large field sets you might want to consider using `INSERT table SET field = :value` syntax. Makes it easier to keep track of what data goes where — Machavity, Jul 06 '16 at 15:28
possible duplicate of [PHP: “Notice: Undefined variable” and “Notice: Undefined index”](http://stackoverflow.com/questions/4261133/php-notice-undefined-variable-and-notice-undefined-index) — Funk Forty Niner, Jul 06 '16 at 15:28

score 2 · Answer 1 · answered Jul 06 '16 at 15:48

In your database schema, you have r_maritalstatus column defined as NOT NULL. This means that whenever you do INSERT, you have to pass some value to this column, you can't just omit it or set to NULL.

Solutions

If you need to always have r_maritalstatus set to something, this means that NOT NULL constraint is correct, and it stops operation to warn you about this. Do a var_dump($rmaritalstatus); before ->execute, it will most likely be NULL. So, the solution is to validate this variable before doing an INSERT, that you always receive the value here from the resume form.
If you don't care about r_maritalstatus always having a value, it means you need to remove the constraint. Just go to your database tool and change this field to NULL instead of NOT NULL.
If you do this, but you have the same problems with many other fields and you're getting tired of changing them to NULL, it's most likely that this code was meant to work in the non-strict MySQL mode. In this mode, MySQL would ignore NOT NULL and set the omitted string field to empty and numeric field to 0. It's recommended to do the following only if you're desperate to fix the application that suddenly got broken after moving to another server or updating MySQL to the newer version. Change my.cnf file to have this line: sql_mode="NO_ENGINE_SUBSTITUTION" (the least strict mode). Restart mysql server and try again. Additionally, if you want to change it only for your application, you can add these right after you connect to the database with $db = new PDO(...):
```
$db->exec('SET SESSION sql_mode="NO_ENGINE_SUBSTITUTION"');
$db->exec('SET GLOBAL sql_mode="NO_ENGINE_SUBSTITUTION"');
```

Symptoms:

It often happens when developers create database schema when in non-strict mode and don't notice NOT NULL that is being set by the DB management tool they use by default (such as phpMyAdmin, for example). Then they move it to the server or upgrade to the newer version of MySQL and wonder, why they suddenly receive many errors like '1048 Integrity constant violation' or '1364 Field doesn't have a default value'. Usually it has to do with the changed mode.

This is a well-crafted and helpful answer. Welcome to Stack Overflow! — O. Jones, Jul 06 '16 at 15:50

Getting the following error while posting data PDO, MYSQL

1 Answers1