How can we do multiple unique validation?

Let me explain my problem through an example. Let's say I have a table. otherusers table. I have Country ID Number, Email and Phone number in this table.

While the user is registering, if there is a input record that fits all three, I want it not to save. So let's say

11111111111, [email protected], 0555 555 555 55(5)

There is such a record in the table. If someone wants to register with the same information, i want they can not to register. I want it will say that there is already a record with this information.

But if one of these information is different, it will be able to register. for example

11111111111, [email protected], 0555 555 555 55(6)

If he tries to register in as a user, he will be able to register.

Of course, the table is not like this. I'm giving this example to explain the process I'm looking for.

Here my table settings

///...
Schema::create('otherusers', function (Blueprint $table) {
     $table->string("CountryIDNumber",10);
     $table->string("Email",100);
     $table->string("Phone",15);
     $table->unique(['CountryIDNumber', 'Email', 'Phone'],"my_uniques");
}
///...

and here my store function in controller.

    public function store(Request $request)
    {
        $request->validate([
            'CountryIDNumber' => 'required|max:10|unique:other_users',
            'Email' => 'required|max:100|unique:other_users',
            'Phone' => 'required|max:15|unique:other_users',
        ]);
        $data = $request->all();

        OtherUsers::Create($data);
        return redirect()->route('otherusers.show'); // stok listesi sayfası
    }

If I give it unique for each row as above it will check for each independently. However, I want to do validation in accordance with the index I have given here. If there is a line with the same three information entered from outside, the record should not be made.

And how can I show this error on the front side. I am using below codes to show validation errors

        @if ($errors->any())
        <div >
            <ul>
                @foreach ($errors->all() as $error)
                <li>{{ $error }}</li>
                @endforeach
            </ul>
        </div>
        @endif

The index I added to the table is working. When I try to make a recording I get the following error.

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '[email protected] 555 555 555' for key 'my_uniques'

How can I do this what I want? I would appreciate your help.

CodePudding user response：

How can i solve this... I am writing how I solved this problem in case anyone else has it. I wrote a query that gets the number of records that match the conditions.

$checkcount = DB::select("select count(*) as count from table where .....");

And by checking this number with if, I sent an error message if there is a record, that is, if it is greater than 0.

if ($checkcount[0]->count === 0) {
 MyModel::Create($data);
 return redirect()->route('show');
}
else {
  return redirect()->back()->withErrors(['This record has been insert before.']);
}

This error message is naturally displayed on the front-end (blade codes I gave above).

CodePudding user response：

I'm adding it as a new answer so that Christoph's comment remains in response.

I changed my first answer with the codes below. (I deleted the selection query code and changed the controls from (If) to (Try Catch))

        try {
             MyModel::Create($data);
             return redirect()->route('show');
        } catch (\exception $th) {
            return redirect()->back()->withErrors(['This record has been insert before.']);
        }