CODEWITHSUNDEEP
phpmysqlcsv
user1990386
user1990386

Reputation: 23

Insert and update CSV file data into MySQL table using PHP

I am inserting record from CSV file to MySQL table if any record already exists update that record and insert other records and if there is no match of the record insert all records.

Here is my code

<?php

$connect = mysql_connect('localhost', '', '');
if (!$connect) {
    die('Could not connect to MySQL: ' . mysql_error());
}

$cid = mysql_select_db('test', $connect);


define('CSV_PATH', '/home/ubc/Documents/');

$csv_file = CSV_PATH . "test.csv";
$csvfile  = fopen($csv_file, 'r');
$theData  = fgets($csvfile);

$i = 0;
while (!feof($csvfile)) {

    $csv_data[] = fgets($csvfile, 1024);
    $csv_array  = explode(",", $csv_data[$i]);
    $insert_csv = array();

    $insert_csv['name']  = $csv_array[0];
    $insert_csv['email'] = $csv_array[1];

    $query = mysql_query("select * from test");
    $count = mysql_num_rows($query);

    if ($count == 0) {
        $query = "INSERT INTO test(name,email)VALUES('" . $insert_csv['name'] . "','" . $insert_csv['email'] . "')";

        $n = mysql_query($query, $connect);
        $i++;
    } else {
        $query = mysql_query("select name from test where name='" . $insert_csv['name'] . "'");
        while ($row = mysql_fetch_array($query)) {

            if ($count) {
                $sql = "update test set email='" . $insert_csv['email'] . "'";
                $qu  = mysql_query($sql);

            } else {

                $query = "INSERT INTO test(name,email)VALUES('" . $insert_csv['name'] . "','" . $insert_csv['email'] . "')";

                $n = mysql_query($query, $connect);

                $i++;
            }
        }
    }
}

fclose($csvfile);

echo "File data successfully imported to database!!";
mysql_close($connect);
?>

Upvotes: 0

Views: 4744

Answers (2)

Akash
Akash

Reputation: 114

    <?php

        $connect = mysql_connect('localhost', '', '');
        if (!$connect) {
            die('Could not connect to MySQL: ' . mysql_error());
        }

        $cid = mysql_select_db('test', $connect);


        define('CSV_PATH', '/home/ubc/Documents/');

        $csv_file = CSV_PATH . "test.csv";
        $csvfile  = fopen($csv_file, 'r');
        $theData  = fgets($csvfile);

        $i = 0;
        while (!feof($csvfile)) {

            $csv_data[] = fgets($csvfile, 1024);
            $csv_array  = explode(",", $csv_data[$i]);
            $insert_csv = array();

            $insert_csv['name']  = $csv_array[0];
            $insert_csv['email'] = $csv_array[1];

            $query = mysql_query("select name from test where name='" . $insert_csv['name'] . "'");
            $count = mysql_num_rows($query);

            if ($count == 0) {
                $query = "INSERT INTO test(name,email)VALUES('" . $insert_csv['name'] . "','" . $insert_csv['email'] . "')";

                $n = mysql_query($query, $connect);

            } else {
                $sql = "update test set email='" . $insert_csv['email'] . "'";
                        $qu  = mysql_query($sql);
            }
 $i++;
            }
        }

        fclose($csvfile);

        echo "File data successfully imported to database!!";
        mysql_close($connect);
        ?>

Upvotes: 1

Prashant M Bhavsar
Prashant M Bhavsar

Reputation: 1164

Use bellow query..

INSERT INTO table (primkey, col2, col3) VALUES(1, 2, 3) ON DUPLICATE KEY UPDATE    
col1=VALUES(1), col2=VALUES(2)

Its example.

This may help you

http://dev.mysql.com/doc/refman/5.0/en/replace.html

Upvotes: 0

Related Questions