Importing csv to mysql

Question

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.

@Mike this is for an admin panel... if they are going to inject their own site they are just plain stupid, and that does not answer the questions addressed, flagged — kira423
– kira423, Commented Nov 15, 2012 at 23:27
You flagged my comment because I didn't answer all of your questions? That's why it's a comment, not an answer. I was actually in the middle of typing up a full answer to all your questions, but meh. Continue with your insecure code if that's how you're gonna be. — Mike
– Mike, Commented Nov 15, 2012 at 23:30
@Mike I do not normally code like this, it is what was asked for so it is what I am giving.. i know how to avoid injections, and I asked the client if they wanted me to secure it, they told me no for the reason i stated above, therefore I am not. — kira423
– kira423, Commented Nov 15, 2012 at 23:33
Whether or not your write secure code should not be an option given to your clients. It takes VERY little extra effort to use parametrized queries and eliminates not only malicious users from purposefully screwing things up (whether or not that applies in this case), but also from accidental coding errors, such as your use of addslashes instead of mysql_real_escape_string. — Mike
– Mike, Commented Nov 15, 2012 at 23:39

jd42 · Accepted Answer · 2012-11-15 23:32:56Z

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; }

Collectives™ on Stack Overflow

Importing csv to mysql

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related