CODEWITHSUNDEEP
phpmysqlcsvimport
kira423
kira423

Reputation: 427

Importing csv to mysql

I have seen several questions on this issue, but none of them have been able to help me.

This is the script I am using.

<?php  

//connect to the database 
$connect = mysql_connect("localhost","username","password"); 
mysql_select_db("mydatabase",$connect); //select the table 
// 

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[0]) { 
            mysql_query("INSERT INTO contacts (contact_first, contact_last, contact_email) VALUES 
                ( 
                    '".addslashes($data[0])."', 
                    '".addslashes($data[1])."', 
                    '".addslashes($data[2])."' 
                ) 
            "); 
        } 
    } while ($data = fgetcsv($handle,1000,",","'")); 
    // 

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

} 

?> 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
<html xmlns="http://www.w3.org/1999/xhtml"> 
<head> 
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> 
<title>Import a CSV File with PHP & MySQL</title> 
</head> 

<body> 

<?php if (!empty($_GET[success])) { echo "<b>Your file has been imported.</b><br><br>"; } //generic success notice ?> 

<form action="" method="post" enctype="multipart/form-data" name="form1" id="form1"> 
  Choose your file: <br /> 
  <input name="csv" type="file" id="csv" /> 
  <input type="submit" name="Submit" value="Submit" /> 
</form> 

</body> 
</html>

It works fine as it is, but you cannot define a header row.

What I am wanting to do, if it is possible is to get each column from the first row and use it to define the columns in which the items will be placed within the database.

So i want to split $data[0] into each column defined in the csv file. I have tried using LOAD DATA LOCAL INFILE but it isn't working for me for some reason.

Upvotes: 0

Views: 1206

Answers (1)

jd42
jd42

Reputation: 91

You might wanna grab the first line first and create column list string from that:

<?php

if ($_FILES[csv][size] > 0) { 

    //get the csv file 
    $file = $_FILES[csv][tmp_name]; 
    $handle = fopen($file,"r"); 

    // get headers
    $columns = fgetcsv($handle,1000,",","'");
    $column_list = implode(",",$columns);

    //loop through the csv file and insert into database 
    while ($data = fgetcsv($handle,1000,",","'")) { 
        if ($data[0]) { 
        for ($i=0;$i<count($columns);$i++){
            $data[$i]="'".mysql_real_escape_string($data[$i])."'";
        }
        $values = implode(",",$data);

        $query = "INSERT INTO contacts ($column_list) ($values)";

        mysql_query($query);

        } 
    }

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

}

Upvotes: 2

Related Questions