mysql data insertion with foreach loop

Question

I was inserting records successfully with this code:

 foreach($R as $k=>$v)
 {
     $test_id = str_replace('rep_result_', '', $k);
     if(strstr($k, 'rep_result_'))
     {      
         $content = $v; 
         $SQL = "INSERT INTO report SET
             rep_te_id   = '$test_id',
             rep_result  = '$content',
             record_id = '$R[payment_id]',
             rep_date    = '$dt'";

But now I have two extra fields in my table, remark and nor. So now, for inserting all data I made this code:

foreach($R as $k=>$v)
{
    $test_id = str_replace('rep_result_', '', $k);
    if(strstr($k, 'rep_result_'))
    {
        $content = $v; 
        if(strstr($k, 'remark_'))
   {
         $remark=$v;
         if(strstr($k, 'nor_'))
         {
             $nor=$v;
         $SQL = "INSERT INTO report SET
                 rep_te_id   = '$test_id',
             rep_result  = '$content',
             record_id = '$R[payment_id]',
             remark  = '**$remark**',
             nor  = '**$nor**',                    
             rep_date    = '$dt'";

I did not get anything in the database. Not everything is ok here. If I use only one if condition then data is being inserted like (rep_result,remark,nor any one).

if(strstr($k, 'remark_'))
   $remark=$v;

But when I use all the three condition, nothing is stored. I know I have ifstatement or foreach loop problem.

Please - learn to use MySQLi or PDO with prepared statements - if you're just learning, learn to use good practises rather than bad — Mark Baker, Apr 13 '13 at 11:11
Also learn the syntax for SQL, you're combining the structure of INSERT with the structure for UPDATE — Mark Baker, Apr 13 '13 at 11:13
**You are leaving yourself wide open to SQL injection attacks.** Please learn about using parametrized queries, preferably with the PDO module, to protect your web app. http://bobby-tables.com/php has examples to get you started. — Andy Lester, Apr 15 '13 at 01:25

score 2 · Accepted Answer · edited May 23 '17 at 12:04

As others have said, your SQL syntax is fundamentally incorrect (mixing INSERT and UPDATE syntax). A single row insert statement would have a structure like this:

INSERT INTO report (rep_te_id, rep_result, record_id, rep_date )
VALUES ( '$test_id', '$content', '$R[payment_id]', '$dt' )

Do read up on prepared statements, MySQLi and PDO to learn more about performance and efficient use of the database server.

Also, just in a general sense, sending numerous independent SQL statements to the database from within a loop is potentially a huge performance issue. There is communication and connection overhead associated with each one of those calls that has nothing to do with the actual data insertion work that you want to the database server to perform.

MySQL allows you to insert multiple rows with the same statement, so you could build up a single SQL statement in your loop, then send all the inserts in one call to the database.

The syntax to insert multiple rows with one statement looks like:

INSERT INTO report (rep_te_id, rep_result, record_id, rep_date )
VALUES ( '1', 'Row 1 content', '1', '2013-04-15' ),
( '2', 'Row 2 content', '2', '2013-04-15' ),
( '3', 'Row 3 content', '3', '2013-04-15' ),
( '4', 'Row 4 content', '4', '2013-04-15' );

For documentation and more examples, see:

http://dev.mysql.com/doc/refman/5.5/en/insert.html

https://stackoverflow.com/a/6889087/618649

https://stackoverflow.com/a/1307652/618649

score 1 · Answer 2 · answered Apr 13 '13 at 11:27

Your INSERT query statment has mistakes. I can't test your code but i assume your condition works here is a sample of mysqli connection and INSERT query.

    //opening connection
    $mysqli = new mysqli($dbserver, $dblogin, $dbpassword, $dbname);
    if (mysqli_connect_errno()) 
    {
        printf("Connection failed: %s\n", mysqli_connect_error());
        exit();
    }

    foreach($R as $k=>$v)
    {
        $test_id = str_replace('rep_result_', '', $k);
        if(strstr($k, 'rep_result_'))
        {
            $content = $v; 
            if(strstr($k, 'remark_'))
            {
                  $remark=$v;
                  if(strstr($k, 'nor_'))
                  {
                      $nor=$v;

                      $SQL = "INSERT INTO report (`rep_te_id`, `rep_result`, `record_id`, `remark`, `nor`, `rep_date`) VALUES ('".$test_id."', '".$content."', '".$R['payment_id']."', '".$remark."', '".$nor."', '".$dt."')";
                      echo $SQL   //let's see the query
                      $mysqli->query($SQL) or die($msqli->error.__LINE__);
                  }
            }
        }
    }

as you see i placed an echo right after the $SQL statement to see if your condition are matched. If the query will not be printed so yuo have problem with all those if condition

score 1 · Answer 3 · answered Apr 13 '13 at 11:32

1

hey buddy your insert query syntax is wrong user correct and learn some sql query syntax

INSERT INTO report (rep_te_id,rep_result,remark,nor,rep_date) VALUES ('$test_id','$content','$R[payment_id]','**$remark**','**$nor**','$dt');

answered Apr 13 '13 at 11:32

Yadav Chetan

1,874
2
23
43

score 0 · Answer 4 · answered Apr 13 '13 at 11:21

Try implement an else for each of your if condition and try echoing something from their, because look like any of your If condition is not getting satisfied.

I would suggest you to echo $k only from all three else because you will be able to know current value of $k.

also specify else no. in echo so you will be able to get which else got called.

mysql data insertion with foreach loop

4 Answers4