Filter with several options php mysql

Question

I have a database of vacation tours and want to sort it by 3 parameters at the same time (country of destination, departure-date, hotel).

So I created three inputs in html form (destination, date, hotel)

<form action="/search-tours" method="GET">
<input name="destination" type="text">
<input name="date" type="date">
<input name="hotel" type="text">
<button type=submit>Search tours</button>

I am using Laravel, so my php code is in controller ("Tour" is the model = connection to a table "tours" in the DB).

        $destination = $request->get('destination');
        $date = $request->get('date');
        $hotel = $request->get('hotel');


        $tours = Tour::where('destination', '=', $destination)
                      ->where('departure', '=' , $date)
                      ->where('hotel', '=', $hotel)
                     ->get();

It gives correct results when I submit all the inputs, but when any is missing it doesn't work at all.

I thought I could solve it via If-else, but then I realised that there can be 7 situations (1- parameter present, 0 - missing: for ex destination(1)-date(0)-hotel(0) = 100, and total: 111, 100, 010, 001, 110, 011, 101 - we don't care about 000). And if we have 4 inputs it would be 15, 5 params - 31 etc.

So it would be tedious to write so many if-else's, then it means there is another appoach.

What approach should I take to make it work if only 1 or 2 inputs was given?

I removed some excuses from your post, because it is not a crappy question. :) — Félix Adriyel Gagnon-Grenier, Apr 27 '17 at 21:25
actually, can you do something like http://stackoverflow.com/a/14179817/576767 — Félix Adriyel Gagnon-Grenier, Apr 27 '17 at 21:28
thank you Felix) maybe you can change the Subject so it would be more helpful for people with the same problem?) (so they can find it easier via search, cause I couldn't find anything). — Sergej Fomin, Apr 27 '17 at 22:05

Alexey Chuhrov · Answer 1 · 2017-04-27T21:51:57.917

2

$query = (new Tour)->newQuery();
if($request->has('destination')){
    $query->where('destination', '=', $request->get('destination'));
}
if($request->has('date')){
    $query->where('departure', '=', $request->get('date'));
}
if($request->has('hotel')){
    $query->where('hotel', '=', $request->get('hotel'));
}
$tours = $query->get();

edited Apr 27 '17 at 21:51

answered Apr 27 '17 at 21:28

Alexey Chuhrov

1,787
12
25

thanks, but when I hit "submit" it gives "Non-static method Illuminate\Database\Eloquent\Model::newQuery() should not be called statically", what should I update?:) – Sergej Fomin Apr 27 '17 at 21:44
Thank you, Dan! I would have spended ages to find this option if not you. – Sergej Fomin Apr 27 '17 at 22:04
Well `newQuery()` just returns eloquent query builder object. You can trace it in source code if you want to find out how it actually happens. Here's also an article (one of many) with examples of query building (filtering): https://m.dotdev.co/writing-advanced-eloquent-search-query-filters-de8b6c2598db – Alexey Chuhrov Apr 27 '17 at 22:37
1

Thanks, the article is great, I've read it to the end but understood only the first half. Will keep on learing and hope one day i'll be able to understand it completely. – Sergej Fomin Apr 28 '17 at 10:52

Filter with several options php mysql

1 Answers1