Import excel/ csv file into phpmyadmin using php

Question

I'm trying to write a code that will upload a document directly to phpMyAdmin using PHP. I tried this code and It looks like it works and there are no errors, but the data was not uploaded to the database.. Can someone please help point out the problem?

<?php
$host="localhost";
$username = "root";
$password = "";
$database= "schoolydb";
$connect = new mysqli($host,$username,$password,$database);
$connect ->set_charset("utf8");
$message = '';

if(isset($_POST["upload"]))
{
 if($_FILES['product_file']['name'])
 {
  $filename = explode(".", $_FILES['product_file']['name']);
  if(end($filename) == "csv")
  {
   $handle = fopen($_FILES['product_file']['tmp_name'], "r");
   while($data = fgetcsv($handle))
   {
    $student_id = mysqli_real_escape_string($connect, $data[0]);
    $student_login   = mysqli_real_escape_string($connect, $data[1]);  
    $student_password = mysqli_real_escape_string($connect, $data[2]);
    $student_first_name = mysqli_real_escape_string($connect, $data[3]);
    $student_last_name = mysqli_real_escape_string($connect, $data[4]);
    $student_phone_number = mysqli_real_escape_string($connect, $data[5]);
    $student_gender = mysqli_real_escape_string($connect, $data[6]);
    $original_back_school = mysqli_real_escape_string($connect, $data[7]);
    $original_end_time = mysqli_real_escape_string($connect, $data[8]);
    $original_class = mysqli_real_escape_string($connect, $data[9]);
    $class_Halom= mysqli_real_escape_string($connect, $data[10]);
    $parent_id = mysqli_real_escape_string($connect, $data[11]);
    $teacher_id = mysqli_real_escape_string($connect, $data[12]);


    $query = "INSERT INTO `student`(`student_id`, `student_login`, `student_password`, `student_first_name`, `student_last_name`, `student_phone_number`, `student_gender`, `original_back_school`, `original_end_time`, `original_class`, `class_Halom`, `parent_id`, `teacher_id`) VALUES ($student_id, '$student_login','$student_password','$student_first_name','$student_last_name', '$student_phone_number','$student_gender','$original_back_school',' $original_end_time','$original_class','$class_Halom','$parent_id','$teacher_id') ";
    mysqli_query($connect, $query);
   }
   fclose($handle);
   header("location: index.php?updation=1");
  }
  else
  {
   $message = '<label class="text-danger">Please Select CSV File only</label>';
  }
 }
 else
 {
  $message = '<label class="text-danger">Please Select File</label>';
 }
}

if(isset($_GET["updation"]))
{
 $message = '<label class="text-success">Updation Done</label>';
}

$query = "SELECT * FROM student";
$result = mysqli_query($connect, $query);
?>
<!DOCTYPE html>
<html>
 <head>
  <title>Upload Mysql Database through Upload CSV File using PHP</title>
  <script src="../jquery.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
  <script src="../bootstrap.min.js"></script>
 </head>
 <body>
  <br />
  <div class="container">
   <h2 align="center">Update Mysql Database through Upload CSV File using PHP</h2>
   <br />
   <form method="post" enctype='multipart/form-data'>
    <p><label>Please Select File(Only CSV Formate)</label>
    <input type="file" name="product_file" /></p>
    <br />
    <input type="submit" name="upload" class="btn btn-info" value="Upload" />
   </form>
   <br />
   <?php echo $message; ?>
   <h3 align="center">Student table</h3>
   <br />
   <div class="table-responsive">
    <table class="table table-bordered table-striped">
     <tr>
      <th>student_id</th>
      <th>student_login</th>
      <th>student_password</th>
      <th>student_first_name</th>
      <th>student_last_name</th>
      <th>student_phone_number</th>
      <th>student_gender</th>
      <th>original_back_school</th>
      <th>original_end_time</th>
      <th>original_class</th>
      <th>class_Halom</th>
      <th>parent_id</th>
      <th>teacher_id</th>

     </tr>
     <?php
     while($row = mysqli_fetch_array($result))
     {
      echo '
      <tr>
       <td>'.$row["student_id"].'</td>
       <td>'.$row["student_login"].'</td>
       <td>'.$row["student_password"].'</td>
       <td>'.$row["student_first_name"].'</td>
       <td>'.$row["student_last_name"].'</td>
       <td>'.$row["student_phone_number"].'</td>
       <td>'.$row["student_gender"].'</td>
       <td>'.$row["original_back_school"].'</td>
       <td>'.$row["original_end_time"].'</td>
       <td>'.$row["original_class"].'</td>
       <td>'.$row["student_login"].'</td>
       <td>'.$row["class_Halom"].'</td>
       <td>'.$row["parent_id"].'</td>
       <td>'.$row["teacher_id"].'</td>
      </tr>
      ';
     }
     ?>
    </table>
   </div>
  </div>
 </body>
</html>

and this is the student table from my database

..............

phpMyAdmin is only an administration tool for MySQL and MariaDB. So I think you need to parse an excel/csv file and enter the data in the files to database — Ajanyan Pradeep, Mar 10 '19 at 17:51
Thank you so much!! but it supposed to be code that already works, I just made adjustments for my database and I really need it to work. I just can not figure out what the problem :( — Rinat Ben Rubi, Mar 10 '19 at 18:15
duplicate: [https://stackoverflow.com/questions/8816129/read-csv-file-and-store-into-mysql-database](https://stackoverflow.com/questions/8816129/read-csv-file-and-store-into-mysql-database) — Aashish Kumar, Mar 10 '19 at 18:51
Possible duplicate of [Read CSV file and store into MySQL Database](https://stackoverflow.com/questions/8816129/read-csv-file-and-store-into-mysql-database) — Aashish Kumar, Mar 10 '19 at 18:53

Ajanyan Pradeep · Accepted Answer · 2019-03-10T19:24:36.127

There is an error in the line

$query = "INSERT INTO `student`(`student_id`, `student_login`, `student_password`, `student_first_name`, `student_last_name`, `student_phone_number`, `student_gender`, `original_back_school`, `original_end_time`, `original_class`, `class_Halom`, `parent_id`, `teacher_id`) VALUES ($student_id, '$student_login','$student_password','$student_first_name','$student_last_name', '$student_phone_number','$student_gender','$original_back_school',' $original_end_time','$original_class','$class_Halom','$parent_id','$teacher_id') ";

$student_id is not written within ' ' It should be like '$student_id' So actual code will be


$query = "INSERT INTO `student`(`student_id`, `student_login`, `student_password`, `student_first_name`, `student_last_name`, `student_phone_number`, `student_gender`, `original_back_school`, `original_end_time`, `original_class`, `class_Halom`, `parent_id`, `teacher_id`) VALUES ('$student_id', '$student_login','$student_password','$student_first_name','$student_last_name', '$student_phone_number','$student_gender','$original_back_school',' $original_end_time','$original_class','$class_Halom','$parent_id','$teacher_id') ";

This will solve your issue.

score 0 · Answer 2 · 2019-03-11T11:43:46.633

Since you're using .csv files, you can tell PHP that your separator is a semicolon.

<?php 

while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) 

?>

To search each value of your file, you can use a strategy like this:

<?php

$row = 0;
if (($handle = fopen("c:\\temp\\test.csv", "r")) !== FALSE)
{
    //Searching line by line of the file
    while (($data = fgetcsv($handle, 1000, ";")) !== FALSE)
    {
        $num = count($data);
        $row++;
  
        //Searching column by column of the current line
        for ($col = 0; $col < $num; $col++)
        {
   //Here you can use the desired values
   switch ($col) {
    case 0:
     $student_id = mysqli_real_escape_string($connect,$data[$col]);
     break;
    case 1:
     $student_login = mysqli_real_escape_string($connect,$data[$col]);
     break;
    case 2:
     $student_password = mysqli_real_escape_string($connect,$data[$col]);
     break;
    case 3:
     $student_first_name = mysqli_real_escape_string($connect,$data[$col]);
     break;
    case 4:
     $student_last_name = mysqli_real_escape_string($connect,$data[$col]);
     break;
    case 5:
     $student_phone_number = mysqli_real_escape_string($connect,$data[$col]);
     break;
    case 6:
     $student_gender = mysqli_real_escape_string($connect,$data[$col]);
     break;
    case 7:
     $original_back_school = mysqli_real_escape_string($connect,$data[$col]);
     break;
    case 8:
     $original_end_time = mysqli_real_escape_string($connect,$data[$col]);
     break;
    case 9:
     $original_class = mysqli_real_escape_string($connect,$data[$col]);
     break;
    case 10:
     $class_Halom = mysqli_real_escape_string($connect,$data[$col]);
     break;
    case 11:
     $parent_id = mysqli_real_escape_string($connect,$data[$col]);
     break;
    case 12:
     $teacher_id = mysqli_real_escape_string($connect,$data[$col]);
     break;
   }//end switch
   
        }//endfor (columns of each line)
  
  //If your file has a header and you wish to skip it, you can do something like this:
  //if ($row > 1) { //create your query and execute it... }
  
  
   $query = "INSERT INTO `student`(`student_id`, `student_login`, `student_password`, `student_first_name`, `student_last_name`, `student_phone_number`, `student_gender`, `original_back_school`, `original_end_time`, `original_class`, `class_Halom`, `parent_id`,`teacher_id`) VALUES ";
   $query.=                      "($student_id, '$student_login','$student_password','$student_first_name','$student_last_name','$student_phone_number','$student_gender','$original_back_school','$original_end_time','$original_class','$class_Halom', $parent_id, $teacher_id) ";
   mysqli_query($connect, $query);  
  //}
  
    }
    fclose($handle);
}

?>

Import excel/ csv file into phpmyadmin using php

2 Answers2