如何使用php和html将csv表导入mysql数据库

I want to import csv sheet to MYSQL database using HTML form,
now i can import successfully but the problem is, i have to give inputfile path in script only, so that user can upload the csv file and as soon as the file is uploaded, call the function and pass the ‘path of file’ as the parameter.
Below i tried this code,

我想使用HTML表单将csv表导入MYSQL数据库，现在我可以成功导入，但问题是，我只需要在脚本中给出inputfile路径，这样用户就可以上传csv文件，一旦文件上传，调用该函数并传递'path of file'作为参数。下面我尝试了这段代码，

 <?php
    $delimiter = ',';

    $db = new mysqli('localhost', 'root', '', 'ProcessTrackingSystem');

    if (($handle = fopen("/var/www/html/new/database_template.csv", "r")) !== FALSE) {
       while (($data = fgetcsv($handle, 1000, $delimiter)) !== FALSE) {
         foreach($data as $i => $content) {
            $data[$i] = $db->real_escape_string($content);
         }
       $db->query("INSERT INTO ProcessTrackingSystem.ProcessDetails VALUES('" . implode("','", $data) . "');");
      }
      fclose($handle);
    }

  ?>
 <!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>

this script is taking input but we have to specify the path in code, help me to take user input.
thanks in advance.

这个脚本正在输入但我们必须在代码中指定路径，帮助我接受用户输入。提前致谢。

2 个解决方案

#1

I got desired output by below code,

我通过以下代码得到了所需的输出，

<?php if (!$_POST) { ?>
<html>
        <body>
        <form action="" method="post" enctype="multipart/form-data">
        Choose your file: <br /> 
        <input name="csv" type="file" id="csv" /> <br /> <br /> 
        <input type="submit" name="Submit" value="Submit" /> 
        </form>
    </body>
</html>
<?php
} else {
$connect = new mysqli("localhost", "root", "", "ProcessTrackingSystem");
if ($_FILES[csv][size] > 0) {
//get the csv file 
$file = $_FILES[csv][tmp_name]; 
$handle = fopen($file, "r");
$i = 0;
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    if ($i > 0) {
$import = ("INSERT INTO ProcessTrackingSystem.ProcessDetails VALUES('" . implode("','", $data) . "');");
        $connect->query($import);
    }
    $i++;
}
fclose($handle);
print "Import done";
}
}
?>

this code is providing user to upload csv file from web form.

此代码提供用户从Web表单上传csv文件。

#2

Assuming you have have a CSV file with following format and first column corresponding your field name:

假设您有一个包含以下格式的CSV文件，并且第一列对应您的字段名称：

column name1    column name2    column name3
Value1           Value2           Value3
Value4           Value5           Value6

<?
//Import uploaded file to Database
$handle = fopen($_FILES['filename']['tmp_name'], "r"); // Get File's data
$columnArray=array();//stores the column
$dataArray=array();//store all rows
$records=""//store the all records in string format
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { //Get Excel sheet data row by row
        if($f==0){ //Store first row as column headings
            foreach ($data as $k=>$v){
                $columnArray[$k]=strtolower(str_replace(" ","_",$v));
            }
        }else{ 
            // Store insert string for payment record
            foreach ($data as $k=>$v){
                $dataArray[$f][$k]=$v;
                $records.="'".$v."',";
            }
            if($records!=""){
                $records.="'0',now()),(";
            }
        }
    $f++;
}
fclose($handle);//close file
if($records!=""){ // Insert payment record string
        $records=substr($records, 0, -3);
        $colStr=implode($columnArray,",");
        $insertQuery="INSERT INTO table_name (".$colStr.",checked,dateval) VALUES (".$records.")";
        $this->EbsPaymentDetail->query($insertQuery);
}
$n=0;
//Display table containing records imported 
$dataTable.= "<h2><strong>Following records has been successfully Imported !!</strong></h2><table width='100%'><tr><td><strong>S No.</strong></td>";
foreach ($columnArray as $k=>$v){
    $dataTable.= "<td><strong>".ucwords(str_replace("_"," ",$v))."</strong></td>";
}
$dataTable.= "</tr>";
foreach ($dataArray as $k=>$v){
    $dataTable.= "<tr>";
    $dataTable.= "<td>".++$n."</td>";
    for($j=0;$j<count($v);$j++){
        $dataTable.= "<td>".$v[$j]."</td>";
    }
    $dataTable.= "</tr>";
}
$dataTable.= "</table>";
?>

#1