【Laravel】How to Check for Duplicate DB Data During Registration/Update (Validator＋unique, DB::table＋whereRaw)

When working with Laravel, there are times when you want to prevent registration if the same email address, or a combination of email address and name, already exists.
In this article, I’ll introduce how to perform duplicate checks during registration and updates using “Validator＋unique” or “DB::table＋whereRaw”.

Prepared users Table Structure

We prepared the following users table.

users Table

Name	Description
id	Primary key
name	User name
email	Email address

Duplicate Check for Email Address using Validator and unique

Controller Description for Registration Duplicate Check using Validator and unique

※ In the entry() registration process inside UsersController, a duplicate check is performed for the email address during validation.
From the description “’email’ => ‘required|unique:users,email'”, “unique:users,email” checks whether the same email address already exists in the users table.

    public function entry(Request $request) {
        //Validation
        $validator = Validator::make($request->all(), [
            'name' => 'required',
            'email' => 'required|unique:users,email'
        ]);

        //Redirect destination when validation fails (registration screen)
        if ($validator->fails()) {
                return redirect('/usersregister')
                    ->withInput()
                    ->withErrors($validator);
        }
  
        //Registration process
        $users = new User;
        $users->name = $request->name;
        $users->email = $request->email;
        $users->save();
        return redirect('/userslist')->with('message', 'Registration completed successfully.');
    }

Controller Description for Update Duplicate Check using Validator and unique

※ In the update() process inside UsersController, a duplicate check is performed for the email address during validation.
From the description “’email’ => ‘required|unique:users,email,’.$request->id.’,id'”, “unique:users,email,’.$request->id.’,id” checks whether there is a duplicate email in the users table, excluding the record whose id matches the updating data.

    public function update(Request $request) {
        //Validation
        $validator = Validator::make($request->all(), [
            'name' => 'required',
            'email' => 'required|unique:users,email,'.$request->id.',id'	//Exclude the updating data's id from the users table id column
        ]);

        //Redirect destination when validation fails (update screen)
        if ($validator->fails()) {
            return redirect('/usersedit/'.$request->id)
                 ->withInput()
                ->withErrors($validator);
        }
  
        //Update process
        $users = User::find($request->id);
        $users->name = $request->name;
        $users->email = $request->email;
        $users->save();
        return redirect('/userslist')->with('message', 'Update completed successfully.');
    }

Duplicate Check for Same Email and Name Combination using DB::table and whereRaw

Controller Description for Registration Duplicate Check using DB::table and whereRaw

※ In the entry() registration process inside UsersController, a duplicate check is performed when the same combination of email address and name exists.
From the comment “//Duplicate data check”, the $sql variable builds the SQL condition where the “name” and “email” columns match.
Using “DB::table(table_name)->whereRaw(SQL_condition)->get()”, matching data is retrieved, and if data exists, an error message is displayed on the registration screen.

    public function entry(Request $request) {
        //Validation
        $validator = Validator::make($request->all(), [
            'name' => 'required',
            'email' => 'required'
        ]);

        //Redirect destination when validation fails (registration screen)
        if ($validator->fails()) {
                return redirect('/usersregister')
                    ->withInput()
                    ->withErrors($validator);
        //Duplicate data check
        }else{
            $chk=[];
            $sql = '1=1';
            $sql .= " AND name = '".$request->name."'";
            $sql .= " AND email = '".$request->email."'";
            $chk = DB::table('users')
            ->whereRaw($sql)
            ->get();

            if(count($chk) > 0){
                return redirect('/usersregister')
                    ->withInput()
                    ->withErrors("Email address + Name is already registered.");
            }
        }
  
        //Registration process
        $users = new User;
        $users->name = $request->name;
        $users->email = $request->email;
        $users->save();
        return redirect('/userslist')->with('message', 'Registration completed successfully.');
    }

Controller Description for Update Duplicate Check using DB::table and whereRaw

※ In the update() process inside UsersController, a duplicate check is performed when the same combination of email address and name exists.
From the comment “//Duplicate data check”, the $sql variable builds the SQL condition where the “name” and “email” columns match.
By adding “$sql .= ‘ AND id != ‘.$request->id” to the SQL condition, it excludes the record whose id matches the updating data.
Using “DB::table(table_name)->whereRaw(SQL_condition)->get()”, matching data is retrieved, and if data exists, an error message is displayed on the update screen.

    public function update(Request $request) {
        //Validation
        $validator = Validator::make($request->all(), [
            'name' => 'required',
            'email' => 'required'
        ]);

        //Redirect destination when validation fails (update screen)
        if ($validator->fails()) {
            return redirect('/usersedit/'.$request->id)
                 ->withInput()
                ->withErrors($validator);
        //Duplicate data check
        }else{
            $chk=[];
            $sql = '1=1';
            $sql .= " AND name = '".$request->name."'";
            $sql .= " AND email = '".$request->email."'";
            $sql .= " AND id != ".$request->id;	//Exclude the updating data's id from the users table id column
            $chk = DB::table('users')
            ->whereRaw($sql)
            ->get();

            if(count($chk) > 0){
                return redirect('/usersedit/'.$request->id)
                    ->withInput()
                    ->withErrors("Email address + Name is already registered.");
            }
        }
  
        //Update process
        $users = User::find($request->id);
        $users->name = $request->name;
        $users->email = $request->email;
        $users->save();
        return redirect('/userslist')->with('message', 'Update completed successfully.');
    }

Route Description

※ Please change the page URLs as needed.

//Registration screen
Route::get('/usersregister','UsersController@register');
//Registration process
Route::post('/usersentry','UsersController@entry');
//Edit screen
Route::get('/usersedit/{users}','UsersController@edit');
Route::post('/usersedit/{users}','UsersController@edit');
//Update process
Route::post('/users/update','UsersController@update');

※ Please use it at your own risk if you reuse this content.