Insert using for each inserts only last row

Question

Good Day all

I am working on a small php project where I send a comma delimated string to my php script. The issue I am having is, I am trying to get the script to execute 5 times and the 5 values be inserted but so far I see to be doing something wrong and only the final value gets inserted.

I have changed the value of $variable to see if the issue was with the string i was using but there is still no change. Grateful for any assistance as i have placed my code below.

 $variable = 'value1, value2, value3, value4, value5';
    $arrs = explode(',', $variable);
    foreach($arrs as $arr){ 


 $sql = "INSERT INTO diver(refno,f_name,l_name,sig) VALUES ('$refno','$f_name','$variable',',')";

   }


 if(mysqli_query($conn,$sql)){
 echo "Works ";
 }else{
echo("Error description: " . mysqli_error($conn));

 }
 }else{
echo("Error description: " . mysqli_error($conn));
}

You overwrite $sql with each iteration thus the last one is the only one executed — John Conde, Mar 06 '17 at 16:11
just move the query execution inside the loop.. (and delete the second `else`, it may have been copied twice) — Kaddath, Mar 06 '17 at 16:12
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! [Don't believe it?](http://stackoverflow.com/q/38297105/1011527) — Jay Blanchard, Mar 06 '17 at 16:33
[You may want to consider this before posting an answer for this question](http://meta.stackoverflow.com/q/344703/). — Jay Blanchard, Mar 06 '17 at 16:36

score 0 · Answer 1 · edited Mar 08 '17 at 13:24

0

You should perform the mysqli_query in each iteration of the foreach

  foreach($arrs as $arr){ 
      $sql = "INSERT INTO diver(refno,f_name,l_name,sig) VALUES   
      ('$refno','$f_name','$variable',',')";
       mysqli_query($conn,$sql);
   }

otherwise you execute the insert only the last time (after the end of foreach)

edited Mar 08 '17 at 13:24

Jay Blanchard

34,243
16
77
119

answered Mar 06 '17 at 16:13

ScaisEdge

131,976
10
91
107

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! [Don't believe it?](http://stackoverflow.com/q/38297105/1011527) – Jay Blanchard Mar 06 '17 at 16:36
@JayBlanchard correct .. and you comment inform the OP of this fact .. – ScaisEdge Mar 06 '17 at 16:38
If you post an answer without prepared statements [you may want to consider this before posting an answer](http://meta.stackoverflow.com/q/344703/). – Jay Blanchard Mar 06 '17 at 16:39
i will consider you advice in my future answers. @JayBlanchard – Sudhanshu Jain Mar 06 '17 at 16:42
@SudhanshuJain ... the comment is not mine ..but of JayBlanchard ... you should post your comment to him . .. (SO comment work only for one destination ) – ScaisEdge Mar 06 '17 at 16:44

score 0 · Answer 2 · answered Mar 06 '17 at 16:14

0

You need to run your query inside the foreach

   foreach($arrs as $arr){ 
        $sql = "INSERT INTO diver(refno,f_name,l_name,sig) VALUES ('$refno','$f_name','$variable',',')";
        mysqli_query($conn,$sql)
   }

although nothing is actually changing? im guessing $variable should be $arr? VALUES ('$refno','$f_name','$arr',',')

answered Mar 06 '17 at 16:14

Luke Bradley

326
5
16

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! [Don't believe it?](http://stackoverflow.com/q/38297105/1011527) – Jay Blanchard Mar 06 '17 at 16:36
1

If you post an answer without prepared statements [you may want to consider this before posting an answer](http://meta.stackoverflow.com/q/344703/). – Jay Blanchard Mar 06 '17 at 16:41

score -1 · Accepted Answer · answered Mar 06 '17 at 16:22

-1

try this

$conn = mysqli_connect('host','user','pw','db');
$variables = 'value1, value2, value3, value4, value5';
$variables = explode(',', $variables);
$sql = "INSERT INTO diver(refno,f_name,l_name,sig) VALUES ";
$rows = array();
foreach( $variables as $variable ){ 
    $rows[] = "('$refno','$f_name','$variable','')";
}
$sql .= implode(",",$rows);
if(mysqli_query($conn,$sql)){
   echo "Works ";
}
else{
  echo("Error description: " . mysqli_error($conn));
}

answered Mar 06 '17 at 16:22

Sudhanshu Jain

494
3
11

Nice idea... ;-) – arkascha Mar 06 '17 at 16:25
Do or do not. There is no "try". A ***good answer*** will always have an explanation of what was done and why it was done in such a manner, not only for the OP but for future visitors to SO. – Jay Blanchard Mar 06 '17 at 16:33
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! [Don't believe it?](http://stackoverflow.com/q/38297105/1011527) – Jay Blanchard Mar 06 '17 at 16:36
yes you are right and there many thing what we can do.. its just about insertion problem. but it is good practise to use `mysqli_escape_string` for avoiding sql injection – Sudhanshu Jain Mar 06 '17 at 16:38
If you post an answer without prepared statements [you may want to consider this before posting an answer](http://meta.stackoverflow.com/q/344703/). – Jay Blanchard Mar 06 '17 at 16:39
its telling me that I have an error in your SQL syntax here: `'(m178','value1'),('m178','value2')` 'up to value 5, but i dont think there's an error...at least i dont see one – Niana Mar 06 '17 at 17:51
you need to `exit($sql)` before `mysqli_query($conn,$sql)` and checks the query. – Sudhanshu Jain Mar 06 '17 at 18:04

Insert using for each inserts only last row

3 Answers3