CSV PDO Insert Loop?

Question

I have recently asked how to insert a CSV into a MySQL database. It was suggested to me to use LOAD DATA LOCAL INFILE, however it turns out that this is disabled on my host, so no longer an option. Back to PHP loops..

I'm having an issue looping through the results of a temp upload, since I'm mapping the values to an array on insert. On multiple lines therefore, this causes the same entry to be entered twice (the first line's values), as the array values are explicitly defined.

It's inserting 1, 2, 3, 4 and then 1, 2, 3, 4. I want to insert 1, 2, 3, 4 then 5, 6, 7, 8 of the array.

What's the solution (aside from hacky for's and row++)?

Thanks in advance.

$handle = fopen($_FILES['csv']['tmp_name'], "r");
$sql = "INSERT INTO tbl (col1, col2, col3, col4) VALUES (?, ?, ?, ?)";

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$query = $db->prepare($sql);
if ($query->execute(array($data[0],$data[1],$data[2],$data[3]))) return true;
   else return false;
}

How is this loop running more than once? You have a return statement in there. — Francis Avila, Aug 14 '12 at 16:08

score 1 · Answer 1 · edited May 23 '17 at 10:34

The only thing I can think of is that your loop is only executing once, but you run the loop twice. (You have a "return" statement in your loop.)

The following should work:

function loadCsv($db, $filename){

    $fp = fopen($filename, 'rb');
    $sql = 'INSERT INTO tbl (col1, col2, col3, col4) VALUES (?,?,?,?)';
    $pstmt = $db->prepare($sql);

    while (FALSE !== ($data = fgetcsv($fp, 1000))) {
        $cols = array_slice($data, 0, 4);
        $query->execute($cols);
    }

    $pstmt->closeCursor();
    fclose($fp);
}

For maximum compatibility and performance, I recommend connecting to PDO with a function like this connect_PDO function.

score 0 · Answer 2 · answered Aug 14 '12 at 15:03

First of all, you only need to prepare the query once (that's one of the two main advantages of using prepared statements, with injection prevention being the other one), so put the call to prepare() before the while loop, not inside it.

Outside of that, I see no reason why the code you've posted would behave the way you claim it does, unless your data is duplicated in your CSV file.

score 0 · Answer 3 · answered Aug 14 '12 at 20:07

0

The issue was with the return statement. Removing the return instantly fixed the issue.

Unfortunately the user who posted this answer has since removed it.

Thanks everyone for your suggestion and help with this!

answered Aug 14 '12 at 20:07

Craig Wilson

463
5
8
19

CSV PDO Insert Loop?

3 Answers3