php - 使用php将csv文件数据插入mysql数据库

Question

This is my html page for adding data

用于将数据插入数据库位置并将 csv 文件插入“eligilist”数据库的 php 脚本。注意:rownd1，rownd2 ....是基于选择轮数的javascript事件，然后javascript代码显示选择框的数量，即rownd1，rownd2，rownd3，因此这些是基于用户选择的值。

<?php
        require_once('dbconfig/config.php');
        if(isset($_POST['submit']))
        {
          $name = $_POST['placename'];
          $eligibility = $_POST['elig'];
          $backlogs=$_POST['blogs'];
          $rounds=$_POST['rownds'];

          if( isset($_POST["rownd1"]) && isset($_POST["rownd2"] ) && isset($_POST["rownd3"] ) && isset($_POST["rownd4"] ))
          {
              $round1=$_POST['rownd1'];
              $round2=$_POST['rownd2'];
              $round3=$_POST['rownd3'];
              $round4=$_POST['rownd4'];
          }
          elseif (isset($_POST["rownd1"]) && isset($_POST["rownd2"] ) && isset($_POST["rownd3"] )) {
              $round1=$_POST['rownd1'];
              $round2=$_POST['rownd2'];
              $round3=$_POST['rownd3'];
              $round4=NULL;
          }
          elseif (isset($_POST["rownd1"]) && isset($_POST["rownd2"] )) {
              $round1=$_POST['rownd1'];
              $round2=$_POST['rownd2'];
              $round3=NULL;
              $round4=NULL;
          }
          elseif (isset($_POST["rownd1"]))
          {
              $round1=$_POST['rownd1'];
              $round2=NULL;
              $round3=NULL;
              $round4=NULL;
          }

          $venu=$_POST['location'];
          $date=$_POST['InterviewTime'];

          $fileName = $_FILES['myFile']['name'];
          $fileTmpName = $_FILES['myFile']['tmp_name'];
          $fileExtension = pathinfo($fileName,PATHINFO_EXTENSION);
          $allowedType = array('csv');
          if(!in_array($fileExtension, $allowedType))
          {
            echo '<script type="text/javascript"> alert("invalid file extension")</script>';
           } else{

            $handle = fopen($fileTmpName,'r');
            while (($myData = fgetcsv($handle,1000,',')) !== FALSE){
                $fname = $myData[0];
                $regno = $myData[1];
                $branch = $myData[2];
                $percentage = $myData[3];
                $back_logs = $myData[4];
                $mobile = $myData[5];
                $email = $myData[6];

                 $query = "INSERT INTO eliglist (sr, fname, regno, branch, percentage, back_logs, mobile, email) VALUES (NULL,".$fname.",".$regno.",".$branch.",".$percentage.",".$back_logs.",".$mobile.",".$email.")";
          $query_run = mysqli_query($con,$query);
            }

            $query1 = "INSERT INTO `placements` (`id`,`name`,`eligibility`,`backlogs`,`rounds`,`round1`,`round2`,`round3`,`round4`,`venu`,`date`) VALUES (NULL,'$name','$eligibility','$backlogs','$rounds','$round1','$round2','$round3','$round4','$venu','$date')";
          $query_run1 = mysqli_query($con,$query1);



          if($query_run && $query_run1)
          {
            echo '<script type="text/javascript"> alert("Successfully added")</script>';
          }
          else
          {
            echo '<script type="text/javascript"> alert("Error!")</script>';
          }

          }
        }
    }
  ?>

我的问题是，当我提交所有数据以及 csv 文件时，它显示错误...但是除了 csv 文件之外，其余数据都插入到展示位置数据库中，唯一的问题是 csv 文件未存储在数据库中的 Eligilist 表中...如果有任何错误，请帮助我并解决我的代码...谢谢。

Answer 1

您需要在插入内容中的字段值周围加上引号(与展示位置表的操作方式相同)...

$query = "INSERT INTO eliglist (sr, fname, regno, branch, percentage, back_logs, mobile, email) 
           VALUES (NULL,'$fname','$regno','$branch','$percentage','$back_logs','$mobile','$email')";

但是您应该使用准备好的语句和绑定(bind)变量，因为这有助于防止出现一些问题...

        $query = "INSERT INTO eliglist (sr, fname, regno, branch, percentage, back_logs, mobile, email)       
              VALUES (NULL,?,?,?,?,?,?,?)";

        $prep = mysqli_prepare ( $con,$query );
        $handle = fopen($fileTmpName,'r');
        while (($myData = fgetcsv($handle,1000,',')) !== FALSE){
            $fname = $myData[0];
            $regno = $myData[1];
            $branch = $myData[2];
            $percentage = $myData[3];
            $back_logs = $myData[4];
            $mobile = $myData[5];
            $email = $myData[6];

            mysqli_stmt_bind_param($prep, "sssssss", $fname,
                   $regno, $branch, $percentage, $back_logs,
                   $mobile, $email );
            mysqli_stmt_execute($prep);
        }