如何将CSV文件中的数据插入数据库？

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?

我正在尝试将我的txt文件上传到我的数据库,但我认为没有任何事情发生。我在phpmyadmin中检查了我的数据库,但没有插入任何内容。如何将我的数据加载并插入mysql数据库?

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>

3 个解决方案

#1

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')");

#2

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

以下是如何使用不受信任的输入参数化SQL语句。

$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.

有关详细信息,请参见http://codular.com/php-mysqli。

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.

如果您不明白为什么要这样做,请查看SQL注入,并且在您理解之前不要编写另一行代码。

#3

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.

有一件事我注意到在插入查询中你必须在数据库表中有一些varchar字段,为此你缺少逗号。用逗号包装varchar字段。这可能是问题,并使用die和mysql_error来查看错误究竟是什么。

#1