CODEWITHSUNDEEP
phpmysqlsqlcsv
somejkuser
somejkuser

Reputation: 9040

PHP CSV Import Questions

I'm working with importing CSV files into a database, and it is a multi-step process.

Here are the steps:

My questions are the following:

1./ On step 3, I would have in my possession the columns which the user chose and the original data.

Here is what the original data looks like:

$data = array(
 0 => array('John','Doe','[email protected]'),
 1 => array('Foo','Bar','[email protected]')
);

And here is what my columns chosen from step 2 looks like:

$columns = array('firstname','lastname','emailaddress')

How do I create a sql query that can be like the following:

INSERT into contacts (id,firstname,lastname,emailaddress) values (null,'John','Doe','johndoe@gmailcom')

As you can see, the sql query has the columns chosen in the order that they are within the array and then subsequently the values. I was thinking that since the columns are chosen in the order of the data, I can just assume that the data is in the correct order and is associated to the specific column at that position (for example, I can assume that the data value 'John' was associated to the first position of the columns array, and vice versa).

2./ I was thinking of a possible scenario that when the user does the initial upload of the file, they could potentially send a csv file with the first record having a blank field. The problem is, I determine how many columns to have the user associate to the data based on the number of columns within a csv record. In this case, we have 2 columns and every subsequent record has 3 columns. Well, I'm not going to loop through the entire set of records to determine the correct number of columns. How do I resolve this issue? Any ideas?

EDIT

I think I figured out the answer to question 2. On the parsing of the csv file, I can get a count for each record and the highest count at the end of the parsing is my count. Seems right? Any issues with that?

Upvotes: 1

Views: 265

Answers (1)

Erik Nedwidek
Erik Nedwidek

Reputation: 6184

To parse the data from the CSV file, look at fgetcsv. http://php.net/manual/en/function.fgetcsv.php

It'll load a line from the file and return an array of the CSV fields.

$data = array();
while (($lineFields = fgetcsv($handle)) !== false) {
    $data[] = $lineFields;
}

This assumes you are using PHP5 and opened the file with $handle. In PHP4 fgetcsv needs a second parameter for max length of line to read.

For the query:

$sql = "INSERT into contacts (id," + implode(',', $columns) + ") values";

I'm not including anything after the values. You should be creating prepared statements to protect against sql injections. Also if you are using MySQL, id should be an autoincrement field and omitted from inserts (let MySQL generate it). If you are using Postgres, you'll need to create a sequence for the id field. In any case let the database generate the id for you.

Upvotes: 1

Related Questions