CODEWITHSUNDEEP
phplaravellaravel-5.8laravel-excel
Peter Amo
Peter Amo

Reputation: 201

The First Repeated Data Gets Inserted

I'm working with Laravel 8 and LaravelExcel for importing an Excel file which has a user National Code id and this field must be unique for each user.

So I coded this at the Import Class:

$ncodes = DB::table('olympiad_1400')->select('mys_ncode')->get();
$n = 0; 
$repeated = [];
foreach($formatArray as $arr){
    if($ncodes->isEmpty()){
        DB::table('olympiad_1400')->insert([
            'mys_name' => $arr['name'],
            'mys_ncode' => $arr['nationalCode'],
        ]);
    }else{
        if($ncodes[$n]->mys_ncode == $arr['nationalCode']){
            DB::table('olympiad_1400')->insert([
                'mys_name' => $arr['name'],
                'mys_paid_price' => $arr['price'],
            ]);
        }else{
            array_push($moghayerat, $arr['nationalCode']);
        }
    }
}

Now then at the Blade, I added this:

    @if (session()->has('khata'))
        <div class="row justify-content-center">
            <div class="col-md-12 ">
                <div class="form-group alert alert-danger">
                    These national codes exist at the DB: 
                    <ul>
                    @foreach(session('khata') as $khat)
                        <li>{{ $khat }}</li>
                    @endforeach
                    </ul>
                </div>
            </div>
        </div>
    @endif

Now this works fine and rejects inserting repeated National Codes but it ONLY inserts the 1st repeated national code!

For example, if my Excel file has 10 rows (and all of the national codes already exist in the DB), it inserts the 1st row of the file, and then rejects the other repeated national codes properly.

So what's going wrong here? How can I reject ALL the National Code of repeated data from inserting into the DB?

UPDATE #1:

When I do try this code, it works fine:

$ncodes = DB::table('olympiad_1400')->where('mys_creator_id',auth()->user()->usr_id)->get();

$n = 0; 
$repeated = [];
foreach($formatArray as $arr){
    if($ncodes->isEmpty()){
        DB::table('olympiad_1400')->insert([
            'mys_name' => $arr['name'],
            'mys_ncode' => $arr['nationalCode'],
        ]);
    }else{
        if($ncodes[$n]->mys_ncode == $arr['nationalCode']){
            DB::table('olympiad_1400')->insert([
                'mys_name' => $arr['name'],
                'mys_paid_price' => $arr['price'],
            ]);
        }else{
            array_push($moghayerat, $arr['nationalCode']);
        }
        $n++;
    }
}

But when I remove the ->where('mys_creator_id',auth()->user()->usr_id) part, it does not show repeated data and insert them all.

The reason for this is that, the collection with mys_creator_id of auth()->user()->usr_id only contains a collection that matches with entered $arr['nationalCode'] like this:

$ncodes[$n]->mys_ncode                  $arr['nationalCode']

1274925657                              1274925657
1275119859                              1275119859
1274051096                              1274051096
1273884817                              1273884817
1273884817                              1273884817

But when I remove the where('mys_creator_id',auth()->user()->usr_id), it does not match the returned collection and that's why it keeps inserting repeated datas.

So I need something to check in the Whole Collection for repeated National Code (not just searching for an index of it).

I would really appreciate any idea or suggestion about this...

Thanks in advance.

Upvotes: 0

Views: 124

Answers (3)

Sethu
Sethu

Reputation: 1379

@nagidi I've checked your code and it seems you're inserting the data if the National Code exists in the database which is the first mistake:

if($ncodes[$n]->mys_ncode == $arr['nationalCode']){

You're supposed to skip inserting if the national code matches.

The second mistake is the value of the variable $n is never get changed, it always being 0 on your code. That's why only the first row (0th index) got inserted in database.

I hope this code will solve your problem

}else{
    // Make insert if the national code isn't exist in db
    if($ncodes[$n]->mys_ncode !== $arr['nationalCode']){ 
        DB::table('olympiad_1400')->insert([
            'mys_name' => $arr['name'],
            'mys_paid_price' => $arr['price'],
        ]);
    }else{
        array_push($moghayerat, $arr['nationalCode']);
    }
    $n++;   // Increment the value for the key
}

Upvotes: 0

ozahorulia
ozahorulia

Reputation: 10084

I guess you're looking for something like this:

$ncodes = DB::table('olympiad_1400')->select('mys_ncode')->get();

// First collect all ncodes into a separate array
$usedNCodes = [];
if (!$ncodes->isEmpty()) {
    $usedNCodes = array_map(
        function($item) {
            return $item->mys_ncode
        },
        $ncodes
    );
}

$repeated = [];
foreach($formatArray as $arr){
    if (!\in_array($usedNCodes, $arr['nationalCode']) {
        // Insert all data if ncode was not found
        DB::table('olympiad_1400')->insert([
            'mys_name' => $arr['name'],
            'mys_ncode' => $arr['nationalCode'],
            'mys_paid_price' => $arr['price'],
        ]);

        // Also add new ncode to the used ncodes array, so duplicates from the $formatArray won't be inserted anyway.
        $usedNCodes[] = $arr['nationalCode'];
    } else {
        $repeated[] = $arr['nationalCode'];
    }
}

// If $formatArray had duplicates and you only need them once in the $repeated array
$repeated = array_unique($repeated);

I didn't understand why you have different kinds of insert for the cases when the table is completely empty and when you didn't find your id. If you needed to update existing records with the same ncode, use DB:update in the else branch.

Upvotes: 1

Elnur Ibrahim-zade
Elnur Ibrahim-zade

Reputation: 821

I think your problem is with this code

if($ncodes[$n]->mys_ncode == $arr['nationalCode'])

change it to

if($ncodes->where('mys_ncode ', $arr['nationalCode'])->isEmpty())

Upvotes: 0

Related Questions