dongyihang3575 2017-04-25 12:44
浏览 65
已采纳

PHP / PDO - 循环数组并将其添加到数据库的最佳方法

I am taking data from a CSV file and adding it to an array, in order for me to save it to my database. This is the array format:

Array
(
    [0] => Array
        (
            [0] => Product code
            [1] => Product text
            [2] => Stockcode
            [3] => Origin
            [4] => Batchnumber
            [5] => Quantity
        )

    [1] => Array
        (
            [0] => 02-018              
            [1] => TEMPCELL13 TIF 120 HOUR 6P/P  
            [2] => OK1
            [3] =>    
            [4] =>                  
            [5] => 13
        )

    [2] => Array
        (
            [0] => 02-038              
            [1] => TEMPCELL60 TIF 120 HOUR(BROWN)
            [2] => OK1
            [3] =>    
            [4] =>                  
            [5] => 15
        )
)

Where, $data[0] is the same name as my columns in my database:

enter image description here

So, I want to loop through all arrays, but skipping $data[0], and add this to my database. This is what I currently have:

function process_csv($file) {

    $file = fopen($file, "r");
    $data = array();

    while (!feof($file)) {
        $data[] = fgetcsv($file,null,';');
    }

    fclose($file);

    unset($data[0]); //Unset the header information, since we only want the values to parse into our inventory database. $data[0] contains the header from the CSV = columns in our database
    foreach($data as $insert){

    //Insert $data to my database
    // $insert[] now holds the array

    }

}

What would be the best way to loop through each array and add it? Furthermore, if the "product_code" is already present in the database, it should just UPDATE that row.

  • 写回答

1条回答 默认 最新

  • douxing8855 2017-04-25 12:51
    关注

    You could drop the foreach statement and move your logic directly in the while loop:

    $header = true;
    while (!feof($file)) {
        $data = fgetcsv($file,null,';');
    
        // skip the first line
        if ($header) {
            $header = false;
        }
        else {
            //Insert $data to my database
        }
    }
    

    Otherwise you're looping twice (while to read the file line by line and foreach to loop the array) and consuming too much memory to build the array to loop through.

    For the SQL, you can use a INSERT ... ON DUPLICATE KEY UPDATE statement: https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥50 悬赏帮写C++编程 诚信
  • ¥15 在竞速练习时输出和输入可以同时进行
  • ¥15 安卓数据提交之后格式不对
  • ¥15 需要数据库运行的图片
  • ¥15 如何获取vue-video-editor?
  • ¥100 vs2019 mfc程序如何实现64*64/48*48大小的真彩色工具栏
  • ¥15 全志v3s耳机音频输出口怎么外接功放
  • ¥15 华为ensp使用基本ACL限制公司网络访问
  • ¥15 帮我做下照片上的PLC题
  • ¥15 labview2022 使用modbus报缺少依赖?