CODEWITHSUNDEEP
phpmysqlcsv
hans
hans

Reputation: 29

insert data from csv file to mysql with php

i want to insert data from a csv file into my mysql database with php. But i dont know what i doing wrong.

This is my php code

if ($_FILES[csv][size] > 0){
$csv_file = $_FILES[csv][tmp_name]; // Name of your CSV file
$csvfile = fopen($csv_file, 'r');
$theData = fgets($csvfile);


$i = 0;
while (!feof($csvfile)) {
$csv_data[] = fgets($csvfile, 1024);
$csv_array = explode(",", $csv_data[$i]);

$insert_csv = array();
$insert_csv['id'] = $csv_array[0];
$insert_csv['name'] = $csv_array[1];
$insert_csv['email'] = $csv_array[2];

if(!empty($insert_csv['email'])){
$query = "INSERT INTO contacts(id,name,email)
VALUES('','".$insert_csv['name']."','".$insert_csv['email']."')";
$n=mysqli_query($database->connection,$query);
}

$i++; 
}

fclose($csvfile);

}

This is my csv looks like.

id---- name ------- email

1 ---- [email protected]

2 ---- user2 --------blah

3------ user 3 ------ blah

When i run this code my mysql results are

in email table = ##0.00 "TL")$# en in my name table= also ##0.00 "TL")$#;

What do i wrong?

Upvotes: 0

Views: 2108

Answers (3)

Siraj Khan
Siraj Khan

Reputation: 2348

Please try like this as a example , it should work for you as you want

I think you missed qoutes in "

$query = "INSERT INTO contacts(id,name,email)
VALUES('".$col1."','".$col2."','".$col3."')";

"

<?php

    $csv_file = 'C:\wamp\www\stockmarket\test.csv'; // Name of your CSV file with path
    if (($getfile = fopen($csv_file, "r")) !== FALSE) { 
            $data = fgetcsv($getfile, 1000, ",");
            while (($data = fgetcsv($getfile, 1000, ",")) !== FALSE) {
             $num = count($data); 
             for ($c=0; $c < $num; $c++) {
                 $result = $data; 
                 $str = implode(",", $result); 
                 $slice = explode(",", $str);
                 $col1 = $slice[0]; 
                 $col2 = $slice[1];
                 $col3 = $slice[2]; 

    // SQL Query to insert data into DataBase

    $query = "INSERT INTO contacts(id,name,email)
    VALUES('".$col1."','".$col2."','".$col3."')";

    $s=mysql_query($query, $connect ); 
         }
       } 
      }
    ?>

Upvotes: 0

user188654
user188654

Reputation:

You might want to use MySQL to do the whole loading process with the LOAD DATA INFILE statement.

if($_FILES['csv']['error'] === UPLOAD_ERR_OK && $_FILES['csv']['size'] > 0) {
    $query = "LOAD DATA INFILE '" . $_FILES['csv']['tmp_name'] 
    . "' INTO TABLE contacts FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' (id, name, email);";

    if(!mysqli_query($query)){
        die('Oops! Something went wrong!');
    }
}

If required you can tweak the loading parameters (FIELDS TERMINATED BY, ENCLOSED BY, LINES TERMINATED BY).

Do take note that if you use this approach your temporary file needs to be stored in a place where its accessible by the MySQL server (like /tmp).

Upvotes: 1

MarcoS
MarcoS

Reputation: 17721

To start with, I think you should remove the first 

$data = fgetcsv($getfile, 1000, ",");

line, outside of the while loop...

Upvotes: 0

Related Questions