Clever Geek Handbook

Skip duplicate values ​​when importing CSV data

I have a csv file with 2 columns (company name, customer name). I am inserting company_name into tbl_company and name_name and company_id into tbl_customer . So company_id is a foreign key in tbl_customer. Now the problem I am facing is that I do not want to insert the company name more than once in tbl_company. Can anyone check the code where I am writing this condition.

My php code

$filename=$_FILES["file"]["tmp_name"];
    if($_FILES["file"]["size"] > 0)
    {
        $file = fopen($filename, "r");
        $x = 0;
        while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE)
        {
                    ini_set('max_execution_time', 300);
                    if($x > 0) {
                    // Insert company name into company table
                    $sql_comp = "INSERT into tbl_company(company_name) values ('$emapData[0]')";
                    mysql_query($sql_comp);
                    //$lastID = mysql_insert_id();
                    // get last ID from tbl_company and insert as foreign key in tbl_customer
                    $sql_LID = "SELECT * FROM tbl_company ORDER BY id DESC";
                    $res_LID = mysql_query($sql_LID);
                    $row_LID = mysql_fetch_array($res_LID);
                    $lastID = $row_LID['id'];
                    // insert company id and customer name into table customer
                    $sql_cust = "INSERT into tbl_customer(comp_id,customer_name) values ('$lastID', '$emapData[1]')";
                    mysql_query($sql_cust);
                    }
                    $x++;
        }
        fclose($file);
        echo 'CSV File has been successfully Imported';
        header('Location: index.php');
    }
+3


source to share


2 answers


This may not be the best way, but you can do something like this:



$filename = $_FILES["file"]["tmp_name"];
if($_FILES["file"]["size"] > 0) {
    $file = fopen($filename, "r");
    ini_set('max_execution_time', 300);

    while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE) {

        $company_name = $emapData[0];

        // checking
        // get ID from tbl_company and insert as foreign key in tbl_customer
        $sql_LID = "SELECT * FROM tbl_company WHERE company_name = '$company_name'";
        $res_LID = mysql_query($sql_LID);


        if(mysql_num_rows($res_LID) <= 0) {
            // if does not exist
            // Insert company name into company table
            $sql_comp = "INSERT into tbl_company(company_name) values ('$company_name')";
            mysql_query($sql_comp);   
            $lastID = mysql_insert_id();

        } else {
            $row_LID = mysql_fetch_array($res_LID);
            $lastID = $row_LID['id'];
        }


        // insert company id and customer name into table customer
        $sql_cust = "INSERT into tbl_customer(comp_id,customer_name) values ('$lastID', '$emapData[1]')";
        mysql_query($sql_cust);


    }
    fclose($file);
    echo 'CSV File has been successfully Imported';
    header('Location: index.php');
}
+2


source


Map the CSV file to the array using the following line:

$array = array_map('str_getcsv', file('filename.csv'));

Then you can loop over that array and insert the string into your database in a loop foreach

.

Example:



foreach($array as $key => $val) {
    $update = $db->prepare('UPDATE table SET value = ? WHERE key = ?');
    $update->execute(array($val, $key));

}

To skip duplicate entries, create an empty array before entering the loop, and inside the loop, add a conditional expression to check if the iteration value inside a separate array is like this:

$records = [];

//inside of foreach loop

if(!in_array($value, $records)) {
    //insert record
    $records[] = $value;
}

Also try to refrain from using the mysql_ * functions as they are deprecated and not the best available for communicating with your database.

0


source






More articles:


All Articles