Importing CSV file into MySQL with PHP

Question

I am writing a PHP script that imports CSV data from a myriad of customers. The data has 12 rows and 10 columns (see below). When I use phpMyAdmin's MySQL Import, there is NO problem (all 12 rows import fine!) However, when I try to import using the PHP script, it only imports the first line, or the last line. I've Googled many different ways to write the same script to no avail. The goal is for the user to be able to upload a CSV into the table automatically.

if ($_FILES[csv][size] > 0) { 
//get the csv file 
$file = $_FILES[csv][tmp_name]; 
$handle = fopen($file,"r"); 

//loop through the csv file and insert into database 
do { 
    if ($data) { 
        mysql_query("INSERT INTO summary_td (customer_id, month, Income, Savings, Revolving_Expenses, Utilities, Services, Luxuries, Charities, month_id) VALUES 
            ( 
                '".addslashes($data[0])."', 
                '".addslashes($data[1])."', 
                '".addslashes($data[2])."',
                '".addslashes($data[3])."',
                '".addslashes($data[4])."',
                 '".addslashes($data[5])."',
                  '".addslashes($data[6])."',
                   '".addslashes($data[7])."', 
                    '".addslashes($data[8])."',
                     '".addslashes($data[9])."'
            ) 
        "); 
    } 
} while ($data = fgetcsv($handle,1000,",","'")); 


  //redirect 
    header('Location: import.php?success=1'); die; 
    } 
    ?>

CSV Data:

1,January,5000,899,899,899,899,899,899,1 1,February,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2 1,March,NULL,NULL,NULL,NULL,NULL,NULL,NULL,3 1,April,NULL,NULL,NULL,NULL,NULL,NULL,NULL,4 1,May,NULL,NULL,NULL,NULL,NULL,NULL,NULL,5 1,June,NULL,NULL,NULL,NULL,NULL,NULL,NULL,6 1,July,NULL,NULL,NULL,NULL,NULL,NULL,NULL,7 1,August,NULL,NULL,NULL,NULL,NULL,NULL,NULL,8 1,September,NULL,NULL,NULL,NULL,NULL,NULL,NULL,9 1,October,NULL,NULL,NULL,NULL,NULL,NULL,NULL,10 1,November,NULL,NULL,NULL,NULL,NULL,NULL,NULL,11 1,December,NULL,NULL,NULL,NULL,NULL,NULL,NULL,12

[Another way to write the script](http://stackoverflow.com/questions/11448307/importing-csv-data-using-php-mysql) — , Nov 12 '15 at 03:24
@William, thanks for the reply! I tried that. No change in data output. It still only adds the first line. — esneaker, Nov 12 '15 at 03:49
@Terminus, the code posted there yields the same results. I'm really stuck on this one. It still only imports just the first row. Here's what my table structure looks like: summary int(11) NO PRI NULL auto_increment customer_id Int(10) NO NULL month varchar(30) YES NULL Income varchar(30) YES NULL Savings varchar(30) YES NULL Revolving_Expenses varchar(30) YES NULL Utilities varchar(30) YES NULL Services varchar(30) YES NULL Luxuries varchar(30) YES NULL Charities varchar(30) YES NULL month_id int(10) NO NULL — esneaker, Nov 12 '15 at 12:03

Sanjuktha · Answer 1 · 2015-11-12T03:50:01.500

This works fine for me -

function upload(){
       ini_set('auto_detect_line_endings',TRUE);

        if (($handle = fopen($_FILES['userfile']['tmp_name'], "r")) !== FALSE) {
        $firstRow = true;
        while (($data = fgetcsv($handle, 4096, ",",'"')) !== FALSE)
        {
             $num = count($data);

                         $data1 = array(
                        'name' => $data[0],
                         'nric' => $data[1],
                         'email' => $data[2],
                         'address' => $data[3] ,
                         'postal' => $data[4],
                         'mobile_country_code' => $data[5],
                         'mobile' => $data[6],
                         'gender' => $data[7],
                         'country' => $data[8],
                         'nationality' => $data[9],
                         'dob' => $dob,
                         'notes' => $data[11],
                         'newsletter' => $data[12],
                         'delete' => $data[13]
                       );
                      //echo "<pre/>";print_r($data) ;exit(); 
                           $this->db->insert('members', $data1);
                           $member_id = $this->db->insert_id();
            }

    //echo "<pre/>";print_r($data) ;exit();
         fclose($handle); 
        }
    ini_set('auto_detect_line_endings',FALSE);             
               }

    }

how do I implement this? I tried the code as you have written...I changed the names of the variables to match my information and stuck the function between php tags, and the called the upload();and I get no results into my table. nothing. What am I doing wrong? Again...I'm a novice. First time working with functions in php. Thanks again for your help!! — esneaker, Nov 12 '15 at 12:12
you don't need to call it as function,you can just edit the code inside the function upload and add it in your section — Sanjuktha, Nov 12 '15 at 14:26
ok. One question. Are "$member_id" and "insert_id" your PKs for your database table? I think that is where I'm failing in my code. — esneaker, Nov 12 '15 at 14:53
insert_id() is to get the last inserted id,that is a common function we use and $member_id is my variable assigned for that. — Sanjuktha, Nov 13 '15 at 03:08

Importing CSV file into MySQL with PHP

1 Answers1