mysql query for storing string array in php SQL syntax error

Question

I have a php form , where the formfields are array of strings.Following is the code to extract a string from the array and save it in the databse with other extracted strings.

 $sql = "INSERT INTO student (formid,firstname,lastname,dob,school,year,sex,touch,reason,other,need,improve1) values";  

$valuesArr = array();
$i=0;
for ($i=1; $i <=$childtoen; $i++) //childtoen is a form variable
        { 
            $improve_list="";
        if ($improve[$i][0]!="") // converting this array to a list 
                {

                    $improve_list = implode( ',', $improve[$i]);$improve_list = mysql_real_escape_string( $improve_list ); 
                }

    $improve_list = mysql_real_escape_string($improve_list);
    $firstname = mysql_real_escape_string( $firstname[$i] );
    $lastname = mysql_real_escape_string( $lastname[$i] );
    $dob = mysql_real_escape_string( $dob[$i] );
    $school = mysql_real_escape_string( $school[$i] );
    $year = mysql_real_escape_string( $year[$i] );
    $sex = mysql_real_escape_string( $sex[$i] );
    $touch1 = mysql_real_escape_string( $touch[$i] );
    $reason = mysql_real_escape_string( $reason[$i] );
    $other = mysql_real_escape_string( $other[$i] );
    $need = mysql_real_escape_string( $need[$i] );


    $valuesArr[] = "('$id', '$firstname' , '$lastname' , '$dob' , '$school' , '$year' , '$sex' , '$touch1' , '$reason' , '$other' , '$need' , '$improve_list')"; // Error at or near reason


$sql .= implode(',', $valuesArr);

$query=mysql_query($sql,$connection); if(!$query) exit(mysql_error());
echo $query; 
}

Now i am receiving the following error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server for the right syntax to use near '('23', '12321' , '12321' , '01/22/2015' , '321' , '3' , 'male' , 'yes' , 'Interv' at line 1 where interv is actually Intervention and is the value of $reason I tried to change the enclosing quotes of fields from single ' ' to double " " in the valuesArr but no help. Unable to get the reason of error

Answer 1

Your code isn't resetting $sql. The second time through the loop, it still has the value it had from the first time through the loop.

So, you're appending a new list of values onto the previous statement. As a short demonstration, the first time through the loop, $sql has a value of:

INSERT INTO foo (bar) VALUES ('fee') 

Second time through the loop, $sql has a value of:

INSERT INTO foo (bar) VALUES ('fee')('fi')
                                    ^

And MySQL is going to throw a syntax error right there: ('fi'

As a quick fix, move the initial assignment, $sql = "INSERT INTO ... inside the for loop, like right before you append the imploded $valuesArr.

---

For debugging issues like this, echo (or vardump) the SQL text, immediately before it's executed:

echo "SQL=" . $sql ;

---

Also, the mysql_ interface is deprecated, and will not be supported in the future. New development should use either PDO or mysqli, and make use of prepared statements with bind placeholders. (But if you are stuck with mysql interface, then kudos for at least using the mysql_real_escape_string function to thwart SQL injection attacks.)

---

FOLLOWUP

Look at this line of code, and consider what happens the first time through the loop, when $i = 1.

$dob = mysql_real_escape_string( $dob[$i] );

Now, consider what happens the second time through the loop. Ask yourself this question: What value is currently stored in $dob?

Was a previous assignment done that changed the value of $dob? What is the result of this expression: $dob[2], when $dob contains a string value such as '01/22/2015'? (Hint: we'd expect a single character to be returned.)