Search and filter data using mysql and php

user_5798214 0
Viewed 875

I am just starting to learn php and mysql, and am creating a project using php and mysql. I am stuck while searching and filtering the data from the database. The search is basically a combined search and filter the data according to the user inputs.

enter image description here

Here is my code:

public function filterSearch(Request $request){
    $new  =  $request->get('new');
    $used  =  $request->get('used');
    $registered  =  $request->get('registered');
    $make = $request->get('make');
    $model = $request->get('model');
    $price = $request->get('price');
    $city =  $request->get('city');
    $registration = $request->get('firstRegistration');

    $query =  DB::select("SELECT  * FROM ads WHERE vehicleCond LIKE '".$new."' OR vehicleCond LIKE '".$used."' OR vehicleCond LIKE '".$registered."' ");
    $decode =  json_decode(json_encode($query),true);
    $finalArr = array();
    for($i=0; $i<count($decode); $i++){
        if($make || $model || $registration){
            if(($decode[$i]['make'] == $make) || ($decode[$i]['model'] == $model) ||  ($decode[$i]['registration'] == $registration)){
                array_push($finalArr,$decode[$i]);
            }
        }else{
            array_push($finalArr,$decode[$i]);
        }   
    }
    return response()->json(['success'=>$finalArr], 200);

   }

The result is not correct...for single selection, it is working fine. When I used the combination, then the result is not correct.

filter mysql php
4 Answers

I assume you need your query/filter to be dynamic.

Your html scripts can be follow like below pattern/format:

<input type="checkbox" name="filter1" value="new">
<input type="checkbox" name="filter1" value="used">
<input type="checkbox" name="filter1" value="pre-registered">

<select name="filter2" id="">...</select>
<select name="filter3" id="">...</select>

and in the Php file you may use some conditions to append the query criterias/filter:

$criteria = "";
if($request->get('filter1'))
{
    $criteria .= " AND `filter1` = '".addslashes($request->get('filter1'))."'";
}

if($request->get('filter2'))
{
    $criteria .= " AND `filter2` = '".addslashes($request->get('filter2'))."'";
}

//and so on...

//output
$query =  DB::select("SELECT * FROM your_table WHERE 1 = 1 {$criteria}");

Notes:

You might need be cautious on forming $criteria as it might still have chance of getting Sql Injection using DB::select method, but i believe you can use the same logic to adapt using Laravel Eloquent method.

user_14775900

This is the solution for your issue


    public function filterSearch(Request $request){
        $make = $request->get('make');
        $model = $request->get('model');
        $price = $request->get('price');
        $city =  $request->get('city');
        $registration = $request->get('firstRegistration');
        $searchFields = [
            'new',
            'used',
            'registered'
        ];

        $query =  DB::table('ads')->where(function ($query) use ($searchFields, $request) {
                collect($searchFields)->each(function($field) use($likeCriteria, $query) {
                    $likeCriteria = $request->get($field);
                    if ($likeCriteria === '') {
                        $likeCriteria = '%';
                    }

                    $query->orWhere($field, 'like', '%' . $likeCriteria . '%');
                });
            })->get();

        $decode =  json_decode(json_encode($query),true);
        $finalArr = array();
        for($i=0; $i<count($decode); $i++){
            if($make || $model || $registration){
                if(($decode[$i]['make'] == $make) || ($decode[$i]['model'] == $model) ||  ($decode[$i]['registration'] == $registration)){
                    array_push($finalArr,$decode[$i]);
                }
            }else{
                array_push($finalArr,$decode[$i]);
            }   
        }
        return response()->json(['success'=>$finalArr], 200);
    }
  • First, move all the variables that you want to search for into array $searchFields so we can avoid duplication of code and use clean code.
  • Change your DB query so we can use table method then where with query function to move all the logic inside of it and also avoid SQL injections.

On this link you can learn more about this : https://laravel.com/docs/7.x/queries#where-clauses

Let me know if you have any question.

user_44086970

I would change this:

$new  =  $request->get('new');
$used  =  $request->get('used');
$registered  =  $request->get('registered');
$make = $request->get('make');
$model = $request->get('model');
$price = $request->get('price');
$city =  $request->get('city');
$registration = $request->get('firstRegistration');

to this:

    $new  =  (empty($request->get('new')) ? "%" : $request->get('new');
    $used  =  (empty($request->get('used')) ? "%" : $request->get('used');
    $registered  =  (empty($request->get('registered')) ? "%" : $request->get('registered');
    $make = (empty($request->get('make')) ? "%" : $request->get('make');
    $model = (empty($request->get('model')) ? "%" : $request->get('model');
    $price = (empty($request->get('price')) ? "%" : $request->get('price');
    $city =  (empty($request->get('city')) ? "%" : $request->get('city');
    $registration = (empty($request->get('firstRegistration')) ? "%" : $request->get('firstRegistration');
user_90410750

i would try to store the query to a variable and then add the selection if isset or not empty using ternary condition or ternary if

$query=""SELECT  * FROM ads WHERE 1=1 ";
$query.=(isset($new) && $new!='' ? " OR vehicleCond LIKE '%" . $new . "%'" : "");
$query.=(isset($used) && $used!='' ? " OR vehicleCond LIKE '%" . $used. "%'" : "");
$query.=(isset($registered) && $registered!='' ? " OR vehicleCond LIKE '%" . $registered. "%'" : "");
//and so on depends on your selection variables
//after that
$query =  DB::select($query);

cheers

user_117634110
Related