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

Question

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. This is the csv file: https://drive.google.com/file/d/1EdMKo-XH7VOXS5HqUh8-m0uWfomcY

P粉817354783 · Answer

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.

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 = '请只选择CSV文件';
        }
    } 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