How to update mysql database table each time csv file get uploaded - problem here is I cannot update-CodePudding

My main problem in my code is I cannot update the "date" from a csv file to mysql database table using php. The line of code $date = mysqli_real_escape_string($connect, $data[1]); is the main problem here. I am looking for any alternative query for this specific line of code.

Here is the csv file: https://drive.google.com/file/d/1EdMKo-XH7VOXS5HqUh8-m0uWfomcYL5T/view?usp=sharing

Here is the complete code:

<?php
//index.php
$connect = mysqli_connect("localhost", "root", "1234", "ml_database");
$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)) {
                $data_id = mysqli_real_escape_string($connect, $data[0]);
                $date = mysqli_real_escape_string($connect, $data[1]); //My Problem
                $births = mysqli_real_escape_string($connect, $data[2]);
                $query = "UPDATE my_table 
                            SET date = '$date', 
                                births = '$births', 
                          WHERE data_id = '$data_id'";
                mysqli_query($connect, $query);
            }
            fclose($handle);
            header("location: index.php?updation=1");
        } else {
            $message = '<label >Please Select CSV File only</label>';
        }
    } else {
        $message = '<label >Please Select File</label>';
    }
}

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

$query = "SELECT * FROM my_table";
$result = mysqli_query($connect, $query);
?>
<!DOCTYPE html>
<html>
 <head>
  <title>Update Mysql Database through Upload CSV File using PHP</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
 </head>
 <body>
  <br />
  <div >
   <h2 align="center">Update Mysql Database through Upload CSV File using PHP</a></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"  value="Upload" />
   </form>
   <br />
   <?php echo $message; ?>
   <h3 align="center">Birthss</h3>
   <br />
   <div >
    <table >
     <tr>
      <th>Date</th>
      <th>Births</th>
     
     </tr>
     <?php
     while($row = mysqli_fetch_array($result))
     {
      echo '
      <tr>
       <td>'.$row["date"].'</td>
       <td>'.$row["births"].'</td>
     
      </tr>
      ';
     }
     ?>
    </table>
   </div>
  </div>
 </body>
</html>

CodePudding user response：

First, you need to read and discard the Title line in the CSV. Then using a proper, prepared and parameterised query you can update the database correctly. As the date in the .csv file is in the correct format, nothing is needed to be done there, however this may not always be the case with other CSV files, often the date must be reformatted before it will correct store in the table.

<?php
//index.php
$connect = mysqli_connect("localhost", "root", "1234", "ml_database");
$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");
            // read and ignore the TITLE line
            $data = fgetcsv($handle, 1000, ",");

            // prepare the query ONCE
            $query = "UPDATE my_table 
                            SET date = ?, 
                                births = ?
                          WHERE data_id = ?";
            $stmt = $connect->prepare($query);

            // loop over the remaining rows of the csv
            while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
                $stmt->bind_param('sss', $data[0],$data[1],$data[2]);
                $stmt->execute();
            }
            fclose($handle);
            header("location: index.php?updation=1");
            exit;   // always after a header('Location:.....')
        } else {
            $message = '<label >Please Select CSV File only</label>';
        }
    } else {
        $message = '<label >Please Select File</label>';
    }
}

NOTE: I assumed that all 3 columns were text in the bind...

$stmt->bind_param('sss', $data[0],$data[1],$data[2]);
                   ^^^

If the date_id is an integer you can change that to 'ssi' although the 3 s's will almost definitely work anyway.

References:

fgetcsv
mysqli_prepare
mysqli_bind_param

CodePudding user response：

This is my current code in my workspace @@RiggsFolly, maybe I have put/arrange the code wrong, that is why I cant reflect any update to my table. Can you see it through sir?

<?php
//index.php
$connect = mysqli_connect("localhost", "root", "1234", "ml_database");
$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");
            // read and ignore the TITLE line
            $data = fgetcsv($handle, 1000, ",");

            // prepare the query ONCE
            $query = "UPDATE my_table
                            SET date = ?, 
                                births = ?
                          WHERE data_id = ?";
            $stmt = $connect->prepare($query);

            // loop over the remaining rows of the csv
            while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
                $stmt->bind_param('sss', $data[0],$data[1],$data[2]);
                $stmt->execute();
            }
            fclose($handle);
            header("location: index.php?updation=1");
            exit;   // always after a header('Location:.....')
        }
         else {
            $message = '<label >Please Select CSV File only</label>';
        }
    } else {
        $message = '<label >Please Select File</label>';
    }
}
if(isset($_GET["updation"]))
{
 $message = '<label >Product Updation Done</label>';
}

$query = "SELECT * FROM my_table";
$result = mysqli_query($connect, $query);
?>
<!DOCTYPE html>
<html>
 <head>
  <title>Update Mysql Database through Upload CSV File using PHP</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
 </head>
 <body>
  <br />
  <div >
   <h2 align="center">Update Mysql Database through Upload CSV File using PHP</a></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"  value="upload" />
   </form>
   <br />
   <?php echo $message; ?>
   <h3 align="center">Birthss</h3>
   <br />
   <div >
    <table >
     <tr>
      <th>Date</th>
      <th>Births</th>
     
     </tr>
     <?php
     while($row = mysqli_fetch_array($result))
     {
      echo '
      <tr>
       <td>'.$row["date"].'</td>
       <td>'.$row["births"].'</td>
     
      </tr>
      ';
     }
     ?>
    </table>
   </div>
  </div>
 </body>
</html>