Inserting CSV to MySQL

Question

I would like to insert a CSV to MySQL. How can I only update my table if the column sha1 is existing in MySQL table? If the column sha1 was exist, insert it.

I have this code to insert csv:

while(($csvdata = fgetcsv($handle,10000,","))!== FALSE){
    if($i>0) {
        $sha1 = $csvdata[0];
        $vsdt = $csvdata[1];
        $trendx  = $csvdata[2];
        $notes  = $csvdata[3];

        $sql = "INSERT INTO jeremy_table_trend (date_sourced,sha1,vsdt,trendx,notes) VALUES ('$date','$sha1','$vsdt','$trendx','$notes')";
        $query = mysqli_query($con , $sql);

        $c = $c+1;
        error_reporting(E_ALL ^ E_NOTICE);
    }
    $i++;
    error_reporting(E_ALL ^ E_NOTICE);
}

Don't format SQL with parameters, use a prepared statement and pass in the parameters, this will take care of escaping and formatting the parameters correctly. — SPlatten
– SPlatten, Commented Nov 21, 2018 at 10:16

F5 Buddy · Accepted Answer · 2018-11-21 10:51:25Z

1

Check this, This may help you

<?php
while(($csvdata = fgetcsv($handle,10000,","))!== FALSE){
    if($i>0) {
        $sha1 = $csvdata[0];
        $vsdt = $csvdata[1];
        $trendx  = $csvdata[2];
        $notes  = $csvdata[3];

        // Get record where sha1 
        $check_sha = "SELECT sha1 FROM jeremy_table_trend WHERE sha1='".$sha1."'";
        $check_shaquery = mysqli_query($con , $check_sha);
        if($check_shaquery){
            $sha_count = mysqli_num_rows($check_shaquery);
        }

        // Check if sha1 already in database
        if(isset($sha_count) && $sha_count>0){
            $sql = "UPDATE `jeremy_table_trend` SET `date_sourced`='".$date."',`sha1`='".$sha1."',`vsdt`='".$vsdt."',`trendx`='".$trendx."',`notes`='".$notes."' WHERE sha1='".$sha1."'";
            $query = mysqli_query($con , $sql);
        }else{
            $sql = "INSERT INTO jeremy_table_trend (date_sourced,sha1,vsdt,trendx,notes) VALUES ('$date','$sha1','$vsdt','$trendx','$notes')";
            $query = mysqli_query($con , $sql);
        }

        $c = $c+1;
        error_reporting(E_ALL ^ E_NOTICE);
    }
    $i++;
    error_reporting(E_ALL ^ E_NOTICE);
}
?>

answered Nov 21, 2018 at 10:51

F5 Buddy

4944 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

YetiCGN Over a year ago

Please be aware that this code is prone to SQL injections and very bad coding style all around.

YetiCGN · Accepted Answer · 2018-11-21 11:05:09Z

1

Please, this is 2018. No need for procedural low-level code anymore. Take a look at a library like Spout to read CSV and use PDO for object-oriented access to the database.

I assume the column sha1 has a UNIQUE index set? If not, it should. Then you can do this in one neat SQL statement with the INSERT ... ON DUPLICATE KEY UPDATE syntax:

$sql = "INSERT INTO jeremy_table_trend (date_sourced,sha1,vsdt,trendx,notes) VALUES ('$date','$sha1','$vsdt','$trendx','$notes') ON DUPLICATE KEY UPDATE";

In addition to checking for the existence of an entry with the same hash in the database, you could also use an ORM layer that will take care of this for you.

answered Nov 21, 2018 at 11:05

YetiCGN

8535 silver badges11 bronze badges

1 Comment

YetiCGN Over a year ago

What's the error message? Did you set the UNIQUE index on the column "sha1"? It needs to have the index for the query to work.

Collectives™ on Stack Overflow

Inserting CSV to MySQL

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related