1

I have new challenge in Laravel. My client want a situation whereby, they can do mass update by uploading CSV to DB Laravel will cross-check if "incidence ID" already exist, it will ONLY update the columns.

i.e I have a table that have like 20 columns. one of the columns is named "Incidence ID". If the CSV is been imported to DB and some of the CSV has a incidence ID that is the same with the one already in the DB, the script should just UPDATE the record in other 19 columns. I been searching for over two day but can find any solution yet.

This is my CONTROLLER Script that can ONLY Insert CSV to DB

 public function uploadFile(Request $request){
    if ($request->input('submit') != null ){
        $file = $request->file('file');
        // File Details
        $filename = $file->getClientOriginalName();
        $extension = $file->getClientOriginalExtension();
        $tempPath = $file->getRealPath();
        $fileSize = $file->getSize();
        $mimeType = $file->getMimeType();
        // Valid File Extensions
        $valid_extension = array("csv");
        // 2MB in Bytes
        $maxFileSize = 2097152;
        // Check file extension
        if(in_array(strtolower($extension),$valid_extension)){
            // Check file size
            if($fileSize <= $maxFileSize){
                // File upload location
              ////  $location = 'uploads';
                // File upload location
                $location = '../uploads';

                // Upload file
                $file->move($location,$filename);

                // Import CSV to Database
                $filepath = public_path($filename);

                // Reading file
                $file = fopen($filepath,"r");

                $importData_arr = array();
                $i = 0;

                while (($filedata = fgetcsv($file, 1000, ",")) !== FALSE) {
                    $num = count($filedata );

                    // Skip first row (Remove below comment if you want to skip the first row)
                    /*if($i == 0){
                       $i++;
                       continue;
                    }*/
                    for ($c=0; $c < $num; $c++) {
                        $importData_arr[$i][] = $filedata [$c];
                    }
                    $i++;
                }
                fclose($file);

                // Insert to MySQL database
                foreach($importData_arr as $importData){

                    $insertData = array(
                        "incidence_id"=>$importData[1],
                        "serial_no"=>$importData[2],
                        "name"=>$importData[3],
                        "activation_date"=>$importData[4],
                        "sol_id"=>$importData[5],
                        "address"=>$importData[6],
                        "brand"=>$importData[7],
                        "model"=>$importData[8],
                        "state"=>$importData[9],
                        "region"=>$importData[10],
                        "vendor_name"=>$importData[11],
                        "vendor_id"=>$importData[12],
                        "custodian_email"=>$importData[13],
                        "custodian_phone"=>$importData[14],
                        "asset_tag_no"=>$importData[15],
                        "atm_code"=>$importData[16],
                        "sla_level"=>$importData[17],
                        "sla_hour"=>$importData[18],
                        "timers"=>$importData[19],
                        "status"=>$importData[20],
                        "warranty"=>$importData[21]);
                    //   "user_id"=>$importData[11]);
                    BanData::insertData($insertData);
                }
                Session::flash('message','Import Successful.');
            }else{
                Session::flash('message','File too large. File must be less than 2MB.');
            }
        }else{
            Session::flash('message','Invalid File Extension.');
        }
    }

How can I make it to function like 'updating record' instead of "inserting" to DB?

1 Answer 1

2

You can use laravel's updateOrCreate function - https://laravel.com/docs/6.x/eloquent#other-creation-methods

$item = BanData::updateOrCreate(
    ['incidence_id' => $importData[1]],
    [
        "serial_no" => $importData[2],
        "name" => $importData[3],
        "activation_date" => $importData[4],
        "sol_id" => $importData[5],
        "address" => $importData[6],
        "brand" => $importData[7],
        "model" => $importData[8],
        "state" => $importData[9],
        "region" => $importData[10],
        "vendor_name" => $importData[11],
        "vendor_id" => $importData[12],
        "custodian_email" => $importData[13],
        "custodian_phone" => $importData[14],
        "asset_tag_no" => $importData[15],
        "atm_code" => $importData[16],
        "sla_level" => $importData[17],
        "sla_hour" => $importData[18],
        "timers" => $importData[19],
        "status" => $importData[20],
        "warranty" => $importData[21]
    ]
);
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.