phpmyadmin Question posted in
The official documentation can be found here.

Validation for three unique fields and soft deletes – Phpmyadmin

Last year I made a laravel site with an events table where I needed three fields to be unique for any event (place, date and time). I wasn’t able to set up a validation request to do this so I added an unique index for these three fields directly through phpmyadmin and catching the exception that could happen if a duplicated event was inserted.

So basically my store() method has a try/catch like this:

try {

    $event = new Event;
    $event->place = $request->input('place');
    $event->date = $request->input('date');
    $event->time = $request->input('time');
    $event->save();

    return view(...);
} catch (IlluminateDatabaseQueryException $e) {

    // Exception if place-date-time is duplicated
    if($e->getCode() === '23000') {
        return view('event.create')
                ->withErrors("Selected date and time is not available");
    }
}

Well, now I had to change the app so events could be soft deleted and I simply added the ‘deleted_at’ field to the unique index, thinking it would be so easy… This approach doesn’t work anymore so I’ve been reading here and there about this problem and the only thing I get is I should do it through a validation request with unique, but honestly I just don’t get the syntax for this validation rule with three fields that can’t be equal while a fourth one, deleted_at, being null.

My app checks for the available places, dates and times and doesn’t let the user choose any not available event but no matter how many times I’ve told them there’s always someone who uses the browser back button and saves the event again 🙁

Any help will be much appreciated. Thank you!

Tags:

2

Answers


  1. 0

    This is not a good approach to solve the problem.
    You can do follow things to solve this problem

    • Before insert into database get a specific row if exist from database
      and store into a variable.

    • Then check the data is already stored into the database or not.

    • If data is already there create custom validation message using Message Bag Like below.
    $ifExist = $event
            ->wherePlace(request->input('place'))
            ->whereDate(request->input('date'))
            ->whereTime(request->input('time'))
            ->exist();

if ($ifExist) return 'already exist';

    It might help you.

    Login or Signup to reply.
  2. 0

    @narayanshama91 have pointed the right way.

    You said you would like to use the unique rule to validate the input but the problem is that last week there was a post in Laravel Blog warning users of a possible SQL Injection via the unique rule if the input is provided by the user.

    I would highly advise you to NOT USE this rule in this case since you depend on users input.

    The correct approach in your case would be @narayanshama91 answer. 

    $ifExist = $event
    ->wherePlace(request->input('place'))
    ->whereDate(request->input('date'))
    ->whereTime(request->input('time'))
    ->exist();
    if ($ifExist) {
       return 'already exist';
    }
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search