简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)
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粉529581199
P粉529581199 2023-08-28 13:46:51
0
1
537
<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粉529581199
P粉529581199

reply all(1)
P粉817354783
P粉8173547832023-08-29 00:13:45 1 floor

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

Like +0
Add Reply
Popular Topics
More>
Popular Articles
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn：Public welfare online PHP training，Help PHP learners grow quickly！