How do I insert data from a CSV file into my database?

Question

I'm trying to upload my txt file into my database but I don't think anything happens. I checked my database in phpmyadmin but nothing was inserted. How do I load and insert my data into mysql database?

Here's my code:

<?php 
$conn = mysql_connect("localhost", "login", "password") or die(mysql_error()); 

mysql_select_db("database", $conn);

if(!isset($_POST['submit']))
{
    $uploadtxt = "nyccrash.txt";

    $handle= fopen($uploadtxt, "r");

    // error checking.
    if($handle === false) {
   die("Error opening $uploadtxt");
}


    while($fileop = fgetcsv($handle, 1000, ",") !== false) { 

    $crash_year = $fileop[0];
    $accident_type = $fileop[1];
    $collision_type = $fileop[2];
    $weather_condition = $fileop[3];
    $light_condition = $fileop[4];
    $x_coordinate = $fileop[5];
    $y_coordinate = $fileop[6];


    $sql = mysql_query("INSERT INTO nyccrash (crash_year, accident_type, collision_type, weather_condition, light_condition, x_coordinate, y_coordinate) VALUES ($crash_year, $accident_type, $collision_type, $weather_condition, $light_condition, $x_coordinate, $y_coordinate)"); 

    } }

?>

<!DOCTYPE html> 
<html>
<head> 
<meta charset="utf-8">
<title> NYC Crash Data </title> 
<link ref="stylesheet" type "text/css" href="../style/style.css" /> 

</head> 
<body> 
<div id="mainWrapper"> 

    <form method="post" action="" enctype="multipart/form-data"> 
        <input type="file" name="file"/>
        <br/> 
        <input type="submit" name="submit" value="submit"/> 
    </form> 

</div>

You should add error handling and output or log the result of `mysql_error()` to figure out what doesn't work properly. Also...please don't use `mysql_*` functions in new code. It's deprecated and will at some point be removed from PHP. Use [MySQLi](http://php.net/mysqli) or [PDO](http://php.net/PDO) instead. — Till Helge, Apr 18 '13 at 11:04
You`ve uploaded file incorrectly. Check `$_FILES['file']['tmp_name']` and read about correct [uploads](http://php.net/manual/en/features.file-upload.php). — BlitZ, Apr 18 '13 at 11:06
@martincarlin87, Yeah I've connected to my database. For some reason, Stack overflow doesn't want to show that part of my code. — Kala J, Apr 18 '13 at 11:11

score 0 · Answer 1 · answered Apr 18 '13 at 11:03

0

If this is text data then you forgot ' around data

$sql = mysql_query("INSERT INTO nyccrash (crash_year, accident_type, 
collision_type, weather_condition, light_condition, x_coordinate, y_coordinate) 
VALUES ('$crash_year', '$accident_type', '$collision_type', 
'$weather_condition', '$light_condition', '$x_coordinate', '$y_coordinate')");

answered Apr 18 '13 at 11:03

Yogesh Suthar

30,424
18
72
100

These are the column headers for the data. I want to insert about 10,000 data points under these headers or attributes. Am I doing it wrong? – Kala J Apr 18 '13 at 11:12
NO NO NO NO NO. This is a SQL injection vulnerability, and allows people to execute arbitrary SQL on your database. What if $crash_year was set to `'; drop table nyccrash; --`? See http://bobby-tables.com – hdgarrood Apr 18 '13 at 11:14
**@hdgarrood** and you're suggesting to read a lection about database defence here? I suggest you to comment on an OP's question about it. – BlitZ Apr 18 '13 at 11:16
@YogeshSuthar because this is exactly the wrong way to parameterise SQL queries from untrusted input. – hdgarrood Apr 18 '13 at 11:19
@KalaJ If your data is text and datatype in database is `(varchar,text)` then you have to enclose the variables in `'`. – Yogesh Suthar Apr 18 '13 at 11:19
@CORRUPT What is a 'lection'? Yes, I absolutely am suggesting reading that site. Doing it properly is only marginally more effort. – hdgarrood Apr 18 '13 at 11:20
@hdgarrood We can't parameterise SQL queries in `mysql_query`. – Yogesh Suthar Apr 18 '13 at 11:22
1

**@hdgarrood**, sorry, it was "lecture". But, still, you missing point. Your suggestion / notice / warning / whatever addressed wrong. – BlitZ Apr 18 '13 at 11:22
@YogeshSuthar If that's true, you should _never_ use it. As Till said in the comment on the question, you should use mysqli instead. – hdgarrood Apr 18 '13 at 11:27
1

@hdgarrood If you are saying **this is exactly the wrong way to parameterise SQL queries from untrusted input.** then show to the OP the right way. – Yogesh Suthar Apr 18 '13 at 11:27
@hdgarrood If the question is asked for `mysql_query` then the answer will be in `mysql_query`, no one will write code in `mysqli` OR `PDO` for user. We can only advise him to stop using it. – Yogesh Suthar Apr 18 '13 at 11:31
1

**@Yogesh Suthar**, leave him. It's a pointless discussion (going nowhere) anyway, and inappropriate place for it. – BlitZ Apr 18 '13 at 11:33
@YogeshSuthar done. And I disagree -- we should not answer questions with any `mysql_*` code, even if it is followed by a "don't use this" warning. – hdgarrood Apr 18 '13 at 11:34

hdgarrood · Answer 2 · 2013-04-18T11:38:08.160

0

Here's how to parameterise SQL statements with untrusted input.

$stmt = $db->prepare("INSERT INTO nyccrash (crash_year, accident_type, 
  collision_type, weather_condition, light_condition, x_coordinate, y_coordinate) 
  VALUES (?, ?, ?, ?, ?, ?, ?)");
$stmt->bind_param('sssssss', $crash_year, ...);
$stmt->execute();

See http://codular.com/php-mysqli for more info on this.

If you don't understand why you should be doing it this way, look up SQL injection, and don't write another line of code until you do understand it.

edited Apr 18 '13 at 11:38

answered Apr 18 '13 at 11:32

hdgarrood

2,141
16
23

Out of curiosity, what is trusted vs. untrusted input? This is interesting. – Kala J Apr 18 '13 at 11:41
**@Kala J** He means _"input sanitizing is not an option"_, I think. The question about old ***mysql_**** functions (deprecated extension) against ***PDO_**** or ***mysqli_**** should be discussed on **[meta](http://meta.stackoverflow.com/)** and not here, if my opinion matters. – BlitZ Apr 18 '13 at 11:45
Yay! It depends on whether it's come from you or from somebody else. For example, if it's a string literal in your source code, you can trust it. If it's from an HTTP POST submission from somewhere in the interwebs, you shouldn't trust it. – hdgarrood Apr 18 '13 at 11:51
Having said that, I would always use bound parameters in database code. It's good practice -- for example, it protects you if a string which previously came from a trusted source starts coming from an untrusted one instead. – hdgarrood Apr 18 '13 at 11:52
@CORRUPT meta is for questions and discussion about the site itself. mysql_* vs pdo or mysqli_* is definitely on topic for this question. – hdgarrood Apr 18 '13 at 11:53

score 0 · Answer 3 · edited May 23 '17 at 11:50

You can do it with the library of this answer

$csv    =   New CSVReader();
$result =   $csv->parse_file('Test.csv');//path to file should be csv
echo '<pre>';     //
print_R($result); // Only for testing

if($result){
    foreach($result as $row){
            $crash_year     =   $row['crash_year'];
            $accident_type      =   $row['accident_type'];
            $collision_type =   $row['collision_type'];
            $weather_condition  =   $row['weather_condition'];
            $light_condition    =   $row['light_condition'];
            $x_coordinate       =   $row['x_coordinate'];
            $y_coordinate       =   $row['y_coordinate'];

            $query  =   "INSERT INTO nyccrash";
            $query  .=  "(crash_year, accident_type, collision_type, weather_condition, light_condition, x_coordinate, y_coordinate)";
            $query  .=  " VALUES ";
            $query  .=  "('$crash_year','$accident_type','$collision_type', '$weather_condition', '$light_condition', '$x_coordinate', '$y_coordinate')";   
            mysqli_query($query);
            unset($query);
    }
}

One thing i noticed that in the insert query you must have some varchar fields in your database table and for that you are missing commas. Wrap the varchar fields with commas. This might be the problem and use die and mysql_error to see what the error really is.

click [here](http://stackoverflow.com/questions/11337221/codeigniter-rest-csv-import-to-mysql/11339125#11339125) for CSV Class CSVReader in the answer. include it in your page and use it. — Muhammad Raheel, Apr 18 '13 at 11:56
deal it with yourself i have presented only a simple solution! — Muhammad Raheel, Apr 18 '13 at 11:56
I am able to deal with it -- php newbies reading the question might not be — hdgarrood, Apr 18 '13 at 12:13

How do I insert data from a CSV file into my database?

3 Answers3