SQL INSERT: syntax error in PHP

Question

I have the following piece of code that is suppose to produce a query string for me to insert into my mysql table. After a number of alterations that keep producing the same error result I'm asking for some help to diagnose the problem.

Please take a look at the code

foreach($this->csv2sqlInsertMultiLine as $csv2sqlInsertMultiLine)
            {
                $field_values = explode(',',$csv2sqlInsertMultiLine);

                $fv = 0;
                 $sqlLine = "";
                 $theInsertSQL[$fv] = "INSERT INTO {$_SESSION['WorkTable']} SET \n";
                //$theInsertSQL .= $csv2sqlInsertMultiLine;
                 foreach($this->tableFields as $Fields)
                 {
                     // $Fields.''.$field_values;
                     $Fields = mysqli_real_escape_string($MySQLDnCxn->MySQLCxn, $Fields);
                        $letters = array("(''", "'')","'");//
                        $Quotes   = array('', '');
                        $value  = str_replace($letters, $Quotes, $field_values[$fv]);

                     if($value == "'('" or $value == "')'" or $Fields == 'id' or $Fields == 'cDate') {}
                     elseif($sqlLine === "`id` = '',") { $sqlLine = " ";  }
                     else
                     {
                          $sqlLine .= "`$Fields` = '".$value."',";
                     }

                     $fv++;
                 }
                 $theInsertSQL[$fv] .= $sqlLine;
                $theInsertSQLnew = join(',', $theInsertSQL);
                $theInsertSQLrt = rtrim($theInsertSQLnew,',');


                 //EXECUTE INSERT TO MySQL $TABLE QUERY
                if ($insert = mysqli_query($MySQLDnCxn->MySQLCxn, $theInsertSQLrt))
                {
                    $show .= 'Contacts Have been successfully inserted...'; 
                } 

                else
                {
                    $show .= "<p>could not insert into db <br> &nbsp; ";
                    $show .= mysqli_error($MySQLDnCxn->MySQLCxn);
                    //.'</p><br> Columns count: <b>'.$this->fields_Column.'</b>';       
                }

I keep getting the following error no matter what i do; could not insert into db

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`Title` = ' ',`First Name` = ' ',`Middle Name` = ' ',`Last Name` = ' ',`Suffix` ' at line 2

The query thats produced:

INSERT INTO nfcontactsdb SET ,`Title` = ' ',`First Name` = ' ',`Middle Name` = ' ',`Last Name` = ' ',`Suffix` = ' ',`Company` = ' ',`Department` = ' ',`Job Title` = ' ',`Business Street` = ' ',`Business Street 2` = ' ',`Business Street 3` = ' ',`Business City` = ' ',`Business State` = ' ',`Business Postal Code` = ' ',`Business Country/Region` = ' ',`Home Street` = ' ',`Home Street 2` = ' ',`Home Street 3` = ' ',`Home City` = ' ',`Home State` = ' ',`Home Postal Code` = ' ',`Home Country/Region` = ' ',`Other Street` = ' ',`Other Street 2` = ' ',`Other Street 3` = ' ',`Other City` = ' ',`Other State` = ' ',`Other Postal Code` = ' ',`Other Country/Region` = ' ',`Assistant\'s Phone` = ' ',`Business Fax` = ' ',`Business Phone` = ' ',`Business Phone 2` = ' ',`Callback` = ' ',`Car Phone` = ' ',`Company Main Phone` = ' ',`Home Fax` = ' ',`Home Phone` = ' ',`Home Phone 2` = ' ',`ISDN` = ' ',`Mobile Phone` = ' ',`Other Fax` = ' ',`Other Phone` = ' ',`Pager` = ' ',`Primary Phone` = ' ',`Radio Phone` = ' ',`TTY/TDD Phone` = ' ',`Telex` = ' ',`Account` = ' ',`Anniversary` = '00-0-0',`Assistant\'s Name` = ' ',`Billing Information` = ' ',`Birthday` = '00-0-0',`Business Address PO Box` = ' ',`Categories` = ' ',`Children` = ' ',`Directory Server` = ' ',`E-mail Address` = '[email protected]',`E-mail Type` = 'SMTP',`E-mail Display Name` = ' [email protected]',`E-mail 2 Address` = ' ',`E-mail 2 Type` = ' ',`E-mail 2 Display Name` = ' ',`E-mail 3 Address` = ' ',`E-mail 3 Type` = ' ',`E-mail 3 Display Name` = ' ',`Gender` = 'Unspecified',`Government ID Number` = ' ',`Hobby` = ' ',`Home Address PO Box` = ' ',`Initials` = ' ',`Internet Free Busy` = ' ',`Keywords` = ' ',`Language` = ' ',`Location` = ' ',`Manager\'s Name` = ' ',`Mileage` = ' ',`Notes` = ' ',`Office Location` = '',`Organizational ID Number` = '',`Other Address PO Box` = '',`Priority` = '',`Private` = '',`Profession` = '',`Referred By` = '',`Sensitivity` = '',`Spouse` = '',`User 1` = '',`User 2` = '',`User 3` = '',`User 4` = '',`Web Page` = ''

Where is your query statement? Is this line: $theInsertSQL[$fv] = "INSERT INTO {$_SESSION['WorkTable']} SET \n";? Also, remove single quotes in: $_SESSION['WorkTable']. — bcesars
– bcesars, Commented Mar 10, 2015 at 13:37
I think your problem is on the line $theInsertSQLnew = join(',', $theInsertSQL); - this is putting a , between the SET and the first name / value pair. I think this is invalid SQL syntax — J Richard Snape
– J Richard Snape, Commented Mar 10, 2015 at 13:40

MatejG · Accepted Answer · 2015-03-10 13:45:40Z

1

Remove comma before Title

INSERT INTO nfcontactsdb SET `Title` = ' '...

So in your PHP change line:

$theInsertSQLnew = join(',', $theInsertSQL);

to

$theInsertSQLnew = join(' ', $theInsertSQL);

Looks like comma is added here so we just don't add it at beginning.

edited Mar 10, 2015 at 13:45

answered Mar 10, 2015 at 13:39

MatejG

1,4231 gold badge17 silver badges26 bronze badges

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

Comments

J Richard Snape · Accepted Answer · 2015-03-10 13:46:37Z

0

There is a , between the SET and the first name / value pair in your SQL statement. This is invalid SQL syntax

I think your problem is on the line

$theInsertSQLnew = join(',', $theInsertSQL);

this will insert the , where the \n was in your statement before. I would recommend using

$theInsertSQLnew = join('', $theInsertSQL);

answered Mar 10, 2015 at 13:46

J Richard Snape

20.4k5 gold badges55 silver badges85 bronze badges

Comments

Think Different · Accepted Answer · 2015-03-10 13:47:47Z

0

Try the following:

change this line:

$theInsertSQL[$fv] = "INSERT INTO {$_SESSION['WorkTable']} SET \n";

With:

$_prequery = "INSERT INTO {$_SESSION['WorkTable']} SET \n";

Then change the following lines:

$theInsertSQL[$fv] .= $sqlLine;
$theInsertSQLnew = join(',', $theInsertSQL);
$theInsertSQLrt = rtrim($theInsertSQLnew,',');

To

$theInsertSQL[$fv] .= $sqlLine;
$theInsertSQLnew = join(',', $theInsertSQL);
$theInsertSQLrt = $_prequery.$theInsertSQLnew;

answered Mar 10, 2015 at 13:47

Think Different

2,8251 gold badge14 silver badges18 bronze badges

1 Comment

Geraldo Isaaks Over a year ago

Nice try @Think Different didnt work, same error - ok safe the comma before title

Collectives™ on Stack Overflow

SQL INSERT: syntax error in PHP

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related