SQL error with inserting

[toxicandy]

I have the following code:

<?php

$fieldsClause = array();
$bindArray = array();

$fields = array('status','fname','middle','lname','address','city','state','zip','county','mailing','hphone','mphone','wphone','ext','fax','email','ethgroup','other1','occupation','poe','dob','gender','bhv','mrc','general','evv','allCounties','fillmore','johnson','otoe','richardson','seward','butler','gage','lancaster','pawnee','saline','thayer','cass','jefferspm','nemaha','polk','saunders','york','other2','aro','clergy','intskills','bus','child','law','cpr','data','security','emergency','computer','mechanical','administration','firstaid','translation','construction','basicclean','foodprep','animalcare','heavy','dataentry','identification','phone','runner','greeter','interviewer','safety','other2','interpt','cdl','cpryn','translate','other3','license1','verf1','num1','exp1','license2','verf2','num2','exp2','license3','verf3','num3','exp3','license4','verf4','num4','exp4','license5','verf5','num5','exp5','lsrd','syes','bcert','pauth','dist1','tdate1','dist2','tdate2','dist3','tdate3','dist4','tdate4','dist5','tdate5','felony','felonys','ename','erelation','ehphone','eaddress','ecity','ezip','hdyhau','bdesc','record');

foreach($fields as $field){
 if($san = filter_input(INPUT_POST, $field, FILTER_SANITIZE_STRING)) {
     $fieldClause[] = "`$field` = :$field";
     $bindArray[":field"] = $san;
 }
}
$sql = "INSERT INTO volunDB(";
if (!empty($fieldClause)) $sqlF = implode(', ', $fieldClause);
$stmtString = $sql;
if(isset($sqlF)) {
    $stmtString .= $sqlF .")";
}
$dbh = new PDO('mysql:host=localhost;dbname=petrzilk_test;charset=utf8','petrzilk_dbAdmin', '');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
echo $stmtString;
$stmt = $dbh->prepare($stmtString);
$stmt->execute($bindArray);

And I get an error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '= ?, fname = ?, middle = ?, lname = ?, address = ?, county = ?, ethgroup = ?, ge' at line 1' in /home/petrzilk/public_html/Database/testaddVolunteer.php:24 Stack trace: #0 /home/petrzilk/public_html/Database/testaddVolunteer.php(24): PDO->prepare('INSERT INTO vol...') #1 {main} thrown in /home/petrzilk/public_html/Database/testaddVolunteer.php on line 24

I am trying to simplify the following code: http://pastebin.com/8He9JYRt

I am going off of a code that diafol helped with. The code that is working that this is based off of is for searching. The code can be seen: http://pastebin.com/PE70ucux

Am I missing something huge here or am I just missing a minor detail. I've tried debugging, but I can't seem to figure out the exact problem. The only thing I could think of was if something like fname, lname, ... VALUES :fname, :lname, ... had to be done or if I can do the fname = :fname, lname = :lname, ...

[diafol]

bit confused as the code you paste here is not the one causing the error. it is your pastebin code (first link).

[toxicandy]

The pastebin code works, when I switch the page back to it the code works fine and everything is sent to the database just fine. But when I use the code that is in the thread it doesn't work and gives me the error listed above

[diafol]

Please echo your prepared statement before you execute and paste the output here.

[toxicandy]

The new code is:

<?php
$valuesClause = array();
$fieldsClause = array();
$bindArray = array();

$fields = array('status','fname','middle','lname','address','city','state','zip','county','mailing','hphone','mphone','wphone','ext','fax','email','ethgroup','other1','occupation','poe','dob','gender','bhv','mrc','general','evv','allCounties','fillmore','johnson','otoe','richardson','seward','butler','gage','lancaster','pawnee','saline','thayer','cass','jefferspm','nemaha','polk','saunders','york','other2','aro','clergy','intskills','bus','child','law','cpr','data','security','emergency','computer','mechanical','administration','firstaid','translation','construction','basicclean','foodprep','animalcare','heavy','dataentry','identification','phone','runner','greeter','interviewer','safety','other2','interpt','cdl','cpryn','translate','other3','license1','verf1','num1','exp1','license2','verf2','num2','exp2','license3','verf3','num3','exp3','license4','verf4','num4','exp4','license5','verf5','num5','exp5','lsrd','syes','bcert','pauth','dist1','tdate1','dist2','tdate2','dist3','tdate3','dist4','tdate4','dist5','tdate5','felony','felonys','ename','erelation','ehphone','eaddress','ecity','ezip','hdyhau','bdesc','record');
foreach($fields as $field){
 if($san = filter_input(INPUT_POST, $field, FILTER_SANITIZE_STRING)) {
     if ($san == "") {
         $field = "";
     }
     $fieldClause[] = "`$field`";
     $valuesClause[] = ":$field";
     $bindArray[":field"] = $san;
 }

}
$sql = "INSERT INTO VolunDB(";
if (!empty($fieldClause)) $sqlF = implode(', ', $fieldClause);
if (!empty($valuesClause)) $sqlV = implode(', ', $valuesClause);
$stmtString = $sql;
if(isset($sqlF)) {
    $stmtString .= $sqlF .") VALUES (" . $sqlV . ")";
}
$dbh = new PDO('mysql:host=localhost;dbname=petrzilk_test;charset=utf8','petrzilk_dbAdmin', '');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
echo $stmtString;
$stmt = $dbh->prepare($stmtString);
$stmt->execute($bindArray);

this is a slightly different code because I made it foo VALUE :foo
The output is below. this is the echo $stmtString;

INSERT INTO VolunDB(status, fname, middle, lname, address, city, state, county, mailing, hphone, mphone, wphone, ext, fax, email, ethgroup, other1, occupation, poe, dob, gender, mrc, general, otoe, pawnee, child, data, safety, license1, verf1, num1, exp1, lsrd, syes, bcert, pauth, dist2, record) VALUES (:status, :fname, :middle, :lname, :address, :city, :state, :county, :mailing, :hphone, :mphone, :wphone, :ext, :fax, :email, :ethgroup, :other1, :occupation, :poe, :dob, :gender, :mrc, :general, :otoe, :pawnee, :child, :data, :safety, :license1, :verf1, :num1, :exp1, :lsrd, :syes, :bcert, :pauth, :dist2, :record)

[diafol]

Ok. The VALUES clause values look fine. The fields values look ok too - I can't see any reserved words there.

if($san = filter_input(INPUT_POST, $field, FILTER_SANITIZE_STRING)) {
     if ($san == "") {
         $field = "";
     }
     $fieldClause[] = "`$field`";
     $valuesClause[] = ":$field";
     $bindArray[":field"] = $san;
}

May be wrong:

$bindArray[":field"] = $san;

should be

$bindArray[":$field"] = $san;

Also this:

if ($san == "") {
  $field = "";
}

Not sure what that's supposed to do.

[toxicandy]

Worked, You were right with the $bindArray[]; I did forget the $field and just wrong field. Thank you much. The $san == "" wasn't needed because it worked without it without giving me an error trying to insert a Null value. Thank you!