CODEWITHSUNDEEP
phpmysqlcsv
Godshand
Godshand

Reputation: 631

Inserting CSV to MySQL

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

Upvotes: 0

Views: 102

Answers (2)

YetiCGN
YetiCGN

Reputation: 833

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.

Upvotes: 1

F5 Buddy
F5 Buddy

Reputation: 494

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);
}
?>

Upvotes: 1

Related Questions