search

Home  >  Q&A  >  body text

How to solve the problem of not being able to update the "Date Column": How to correctly upload a CSV file when updating a MySQL database table

<p>The main problem in my code is that I cannot update the "date" from the csv file into the mysql database table using php. The line of code $date = mysqli_real_escape_string($connect, $data[1]); is the main problem here. I'm looking for any alternative query for this particular line of code. </p> <p>This is the csv file: https://drive.google.com/file/d/1EdMKo-XH7VOXS5HqUh8-m0uWfomcYL5T/view?usp=sharing</p> <p>This is the complete code: </p> <pre class="brush:php;toolbar:false;"><?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]）; //我的问题 $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？update = 1”); } else { $message ='<label class="text-danger">Please select only CSV files</label>'; } } else { $message ='<label class="text-danger">Please select file</label>'; } } if(isset($_GET["updation"])){ $message ='<label class="text-success">Product update completed</label>'; } $query = "SELECT * FROM my_table"; $result = mysqli_query($connect, $query); ?> <!DOCTYPE html> <html> <head> <title>Update Mysql database by uploading 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 class="container"> <h2 align="center">Update Mysql database by uploading CSV file using PHP</a></h2> <br /> <form method="post" enctype='multipart/form-data'> <p><label>Please select a file (CSV format only)</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">Birthss</h3> <br /> <div class="table-responsive"> <table class="table table-bordered table-striped"> <tr> <th>Date</th> <th>Birth</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></pre></p>
P粉529581199P粉529581199512 days ago580

reply all(1)I'll reply

  • P粉817354783

    P粉8173547832023-08-29 00:13:45

    First, you need to read and discard the header row in the CSV. Then, using appropriate, prepared, and parameterized queries, you can update the database correctly. Since the dates in the .csv file are in the correct format, no action is required, but this may not be the case with other CSV files, and often the dates will need to be reformatted before they can be stored correctly into 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");
            // 读取并忽略标题行
            $data = fgetcsv($handle, 1000, ",");

            // 准备查询一次
            $query = "UPDATE my_table 
                            SET date = ?, 
                                births = ?
                          WHERE data_id = ?";
            $stmt = $connect->prepare($query);

            // 循环遍历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;   // 重定向后一定要使用exit
        } else {
            $message = '';
        }
    } else {
        $message = '';
    }
}

    NOTE: I assume all 3 columns are of type text. 

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

    If date_id is of type integer, you can change it to 'ssi', although 3 s will usually work fine too.

    Reference:

    fgetcsv
    mysqli_prepare
    mysqli_bind_param

    reply
    0
  • Cancelreply