dskm94301 2019-04-11 21:06
浏览 159
已采纳

使用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 00: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;
    }
    

    展开全部

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)
编辑
预览

报告相同问题?

悬赏问题

  • ¥66 android运行时native和graphics内存详细信息获取
  • ¥100 求一个c#通过CH341读取数据的Demo,能够读取指定地址值的功能
  • ¥15 rk3566 Android11 USB摄像头 微信
  • ¥15 torch框架下的强化学习DQN训练奖励值浮动过低,希望指导如何调整
  • ¥35 西门子博图v16安装密钥提示CryptAcquireContext MS_DEF_PROV Error of containger opening
  • ¥15 mes系统扫码追溯功能
  • ¥40 selenium访问信用中国
  • ¥20 在搭建fabric网络过程中遇到“无法使用新的生命周期”的报错
  • ¥15 Python中关于代码运行报错的问题
  • ¥500 python 的API,有酬谢
手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部