dskm94301
2019-04-12 05:06
浏览 147
已采纳

使用php将excel数据导入数据库表

problem is in my excel 369 rows are there. when I echo/print that data it showing correct but when I am inserting same data in DB table in inserted only 18 - 30 records.

if (isset($_POST['Submit'])) {
    $file = $_FILES['csv_file']['tmp_name'];
    $handle = fopen($file, "r");
    if ($file == NULL) {
    error(_('Please select a file to import'));
    redirect(page_link_to('excel_data_upload'));
    }else {
        $conn = connect();
        while(($filesop = fgetcsv($handle, 1000, ",")) !== false)
        {
            $num3 = $filesop[3];
            $num8 = $filesop[8];
            $num9 = $filesop[9];
            $num20 = $filesop[20];
            if($num3!='ExpiryDate' &&$num8!='VCNO' &&$num20!='TotalB2CAmount' && $num9 !='STBNO'){

                $insertAgent =  mysqli_query($conn, "INSERT INTO `upload_billing_data` 
                (`vc_number`,`stb_number`,`operator_id`,`expiry_date`,`monthly_bill_amount`) 
                VALUES ('$num8','$num9',140,'$num3','$num20')");

                if($insertAgent)
                {
                    echo 'succss';
                }else{
                    echo 'error';
                }

            }  
        }
        close($conn);

    }
}

I am fetching from the excel data. I want to insert all records

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • doushi8187 2019-04-12 08:51
    已采纳

    Change the code as below and you might get to save all data using one query to the database:

    $query_insert = array();
    
    while(($filesop = fgetcsv($handle, 1000, ",")) !== false) {
       $num3 = filterString($filesop[3]);
       $num8 = filterString($filesop[8]);
       $num9 = filterString($filesop[9]);
       $num20 = filterString($filesop[20]);
    
       if ($num3!='ExpiryDate' &&$num8!='VCNO' &&$num20!='TotalB2CAmount' && $num9 !='STBNO') {
          $query_insert[] = "('{$num8}', '{$num9}', 140, '{$num3}', '{$num20}')";
       }  
    }
    
    // If no row matched your if, then there will be no row to add to the database
    if (count($query_insert)>0) {
       $conn = connect();
    
       $query_insert_string = implode(', ', $query_insert);
    
       $query = "INSERT INTO `upload_billing_data` (`vc_number`, `stb_number`, `operator_id`, `expiry_date`, `monthly_bill_amount`) VALUES {$query_insert_string};";
       $insertAgent = mysqli_query($query);
    
       // The rest of you code 
       ...
    
       close($conn);
    }
    
    
    
    // This function makes sure that you string doesn't contain characters that might damage the query
    function filterString($string) {
       $string = str_replace(array("\'", '"'), array('', ''), $string);
       $string = filter_var($string, FILTER_SANITIZE_STRING);
       return $string;
    }
    
    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • doudao1282 2019-04-12 09:33

    Please check this modified code

    if (isset($_POST['Submit'])) {
        $file = $_FILES['csv_file']['tmp_name'];
        $handle = fopen($file, "r");
        if ($file == NULL) {
        error(_('Please select a file to import'));
        redirect(page_link_to('excel_data_upload'));
        }else {
            $conn = connect();
            while(($filesop = fgetcsv($handle, 1000, ",")) !== false)
            {
                $num3 = $filesop[3];
                $num8 = $filesop[8];
                $num9 = $filesop[9];
                $num20 = $filesop[20];
                if($num3!='ExpiryDate' &&$num8!='VCNO' &&$num20!='TotalB2CAmount' && $num9 !='STBNO'){
    
                    $insertAgent =  mysqli_query($conn, "INSERT INTO `upload_billing_data` 
                    (`vc_number`,`stb_number`,`operator_id`,`expiry_date`,`monthly_bill_amount`) 
                    VALUES ('".mysqli_real_escape_string($num8)."','".mysqli_real_escape_string($num9)."',140,'".mysqli_real_escape_string($num3)."','".mysqli_real_escape_string($num20)."')");
    
                    if($insertAgent)
                    {
                        echo 'succss';
                    }else{
                        echo 'error';
                    }
    
                }  
            }
            close($conn);
    
        }
    }
    

    BY using mysqli_real_escape_string() you will be able to avoid sqlinjection issues and you will be able to handle issue of quotes which might be causing an issue.

    in your else block where you are echo "error". you can use mysqli_error($conn); to get exact what error is occurring while performing an insert

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题