current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free.
up vote 0 down vote favorite

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

share|improve this question
1  
This line: $valuesArr[] = ... adds 1 element to the array and doesn't makes any sense! Just change this line: $valuesArr[] = ... to: $valuesArr = ... and then: $sql .= $valuesArr; –  Rizier123 Dec 30 '14 at 20:14
    
Leave a space after values in the first part of $sql.Also do a basic debugging,echo out your $sql at the end. –  Mihai Dec 30 '14 at 20:17
1  
Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. Learn about prepared statements instead, and use PDO or MySQLi. –  Jay Blanchard Dec 30 '14 at 20:19
    
@Rizier123 if i change it to $valuesArr it throws this error 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 '' at line 1 –  himanshu oberoi Dec 30 '14 at 20:22
    
After doing what Rizier123 says echo $sql; so you can see th query and identify the problem. –  Jay Blanchard Dec 30 '14 at 20:24

1 Answer 1

up vote 0 down vote

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.)

share|improve this answer
    
my bad i forgot to reset the $sql So i did it but the error is still the same . and i am recieving the variables through sesion where reason is a dropdown and i am fetching the value via $reason =$_SESSION['post']['reason']; –  himanshu oberoi Dec 30 '14 at 20:40
    
For debugging, echo or vardump the actual SQL Text that's being submitted to the database, before it's executed. Then, figure out what's wrong with the SQL text; that will lead you to what needs to be fixed in the code. –  spencer7593 Dec 30 '14 at 20:48
    
I did some changes as Jay Blanchard suggested and the query Query worked but only the first row has the right values. The second row is stripping the values ` INSERT INTO student (formid,firstname,lastname,dob,school,year,sex,touch,reason,other,need,improve1) values('29', '12321' , '12321' , '01/22/2015' , '321' , '3' , 'male' , 'no' , 'Intervention' , '12321' , 'no' , 'ADHDandAUTISMandDYSPRAXIAandOther')1INSERT INTO student (formid,firstname,lastname,dob,school,year,sex,touch,reason,other,need,improve1) values('29', '3' , '3' , '/' , '1' , '' , 'l' , 'no' , 't' , '3' , '' , 'fooandfoo')1` –  himanshu oberoi Dec 30 '14 at 20:51
    
I added a followup in my answer. The first time through the loop, you are changing the values that are assigned to $firstname, $lastname, $dob, etc. The second time through the loop, the same expressions are operating on values that are different from what they were the first time through the loop. (See the followup in the answer above.) –  spencer7593 Dec 30 '14 at 21:03
    
Thankyou very much for pointing me to the right direction ,Everything is working Fine now . –  himanshu oberoi Dec 30 '14 at 21:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.