PHP/PDO - Best way to loop through an array and adding it to a database

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.

Andrei Duca · Accepted Answer · 2017-04-25T12:53:07.573

0

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

edited Apr 25 '17 at 12:53

answered Apr 25 '17 at 12:51

Andrei Duca

372
2
11

Consuming "too much memory"? How so? – Jay Blanchard Apr 25 '17 at 12:52
Because you're building an entire array; instead just reuse a single variable. – Andrei Duca Apr 25 '17 at 12:53
Down when I do the inserting into the database, should I use the array like: $data[0], $data[1], $data[2] etc.? – oliverbj Apr 25 '17 at 12:54
If I use the array like this, to insert into the database: $stmt->bindParam(":product_code", $data[0]); - I get that "Column 'product_code' cannot be null" – oliverbj Apr 25 '17 at 12:57
`$data` inside the `while` loop will look like `Array ( [0] => 02-038 [1] => TEMPCELL60 TIF 120 HOUR(BROWN) [2] => OK1 [3] => [4] => [5] => 15 )` – Andrei Duca Apr 25 '17 at 12:58
Also, check the value of `$data[0]` and make sure the format **02-018** is accepted by your field in the database. If your `product_code` in the database is `int` or `varchar`, this will most likely not work as expected. – Andrei Duca Apr 25 '17 at 13:03

PHP/PDO - Best way to loop through an array and adding it to a database

1 Answers1