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

Question

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:

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.

Answer 1

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