0

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?

Improve this question
12
  • "I have the $message variable echo'd to the page but it doesn't show up any of the messages"...have you? where? And this one $message = "There was a problem updating your CSV file... will never show anyway because you die() immediately afterwards which stops the script executing. But you should see the mysqli error in that scenario. Do you ever see that? You mentioned sometimes getting SQL errors, but then didn't tell us what they were. Commented Nov 7, 2019 at 17:15
  • 1
    Does this answer your question? Importing CSV data using PHP/MySQL Commented Nov 7, 2019 at 17:17
  • I'd suspect it might be something to do with strings though, since $response is a string (e.g. "hello") yet you aren't enclosing it in quote marks in the SQL statement. To be honest though you really should use prepared statements and parameterised queries to guard against the possibility of malicious input in the CSV file turning you into a victim of SQL injection. It will also take care of things like escaping string inputs properly, thus avoiding unexpected syntax errors in the SQL statement. Commented Nov 7, 2019 at 17:21
  • P.S. the LOAD DATA command with the REPLACE modifier might also work better for you here, and speed things up. Check the question linked by RichYen and also the MySQL documentation Commented Nov 7, 2019 at 17:29
  • 1
    I don't see you echoing $message. Commented Nov 7, 2019 at 18:52

1 Answer 1

Reset to default
2

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>";
                }
            }

        }

    }
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

It is a very bad idea to use die(mysqli_error($conn)); in your code, because it could potentially leak sensitive information. See this post for more explanation: mysqli or die, does it have to die?
@Dharman you are correct, thanks for reminding me I left it in. There's a few I've left in for testing but when live they're not necessary!
Better to avoid manual error checking and enable mysqli errors instead with mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.