How to update a database using php by uploading a CSV file?

I have some code here to update my database based on two columns in the CSV. The CSV file would look like this:

ID Response

1 Hello1

2 Hello2

3 Hello3

In my database I have a table that also contains an id column and an extra column that matches response. The idea is this CSV file is uploaded and will populate the response that matches the ID number.

Basically this: “UPDATE tbl_data SET response = {$response} WHERE id = {$id}“

The form that performs this action look like this:

<form method="post" name="uploadCSV" enctype="multipart/form-data">
    <label>Choose CSV File</label>
    <input type="file" name="csv_file" id="file" accept=".csv" />
    <button type="submit" name="import" class="read-more smaller">Upload</button>
</form>

However, I don’t think I’ve understood how to do this properly, as I get SQL errors, or the form just sits there as if nothing has happened. See code below.

if (isset($_POST["import"])) {

    if($_FILES["csv_file"]["name"]){

        $filename = explode(".", $_FILES["csv_file"]["name"]);

        if(end($filename) == "csv"){

            $handle = fopen($_FILES["csv_file"]["tmp_name"], "r");

            while ($data = fgetcsv($handle)){

                $id = $data[0];
                $response = $data[1];

                $query ="UPDATE tbl_data SET response = {$response} WHERE id = {$id}";
                $update_data = mysqli_query($connection,$query);

                if (!$update_data) {
                    $message = "There was a problem updating your CSV file. If this problem reoccurs, please contact admin";
                    die (mysqli_error($connection));
                }

            }

            fclose($handle);

            header("Location: upload.php?uploaded=1");

        } else {
            $message = "You can only upload a CSV file.";
        }

    } else {
        $message = "Please select a CSV file.";
    }

}

I have the $message to shows the message. but it doesn’t show up any of the messages, and the update in the database doesn’t appear to take place either.

Is there any errors that I may have overlooked in my code? Or is there a much better way to do this?

Answer

Got it working by using the following

if(isset($_POST["importcsv"])){

        $file = $_FILES["csv_file"]["tmp_name"];
        $handle = fopen($file,"r");

        while ($row = fgetcsv($handle)) {

            $id = $row[0];
            $response = $row[1];

            $sql = "UPDATE table SET response = ? WHERE id = ?";
            $update_data_stmt = mysqli_stmt_init($connection);

            if (!mysqli_stmt_prepare($update_data_stmt, $sql)){
                die("Something went wrong with the upload. " . mysqli_error($connection));
            } else {
                mysqli_stmt_bind_param($update_data_stmt, "ss", $response, $id);
                mysqli_stmt_execute($update_data_stmt);
                if ($id == "ID" && $response == "Response"){
                    echo "";
                } else {
                    echo "Lead <b>{$id}</b>'s response was updated to <b>{$response}</b>.</p>";
                }
            }

        }

    }

Advertisement

Answer