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

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

    打赏 评论

相关推荐 更多相似问题