使用foreach循环插入mysql数据

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.

Answer 1

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