Salih Mohamed Salih Mohamed - 28 days ago 12
MySQL Question

Searching Records From MySql Database Using Laravel 5.3

I'm new to laravel 5.3 please help me to solve this issues. I need to filter the matching records from database by three parameters such as category, location, search string. this is my code:

$results = DB::table('ads')
->join('category', 'category.categoryID', '=', 'ads.categoryID')
->join('sub_categories', 'sub_categories.subCategoryID', '=', 'ads.subCategoryID')
->join('site_users', 'site_users.siteUserID', '=', 'ads.createdBy')
->join('location', 'location.locationID', '=', 'site_users.location')
->select('ads.*', 'category.categoryName', 'sub_categories.subCategoryName', 'location.locationName')
->where([
'ads.title', 'like', '%' . $data['q'] . '%',
'ads.isActive', 1
])
->orWhere([
'location.locationName', 'like', '%' . $data['loc'] . '%',
'category.categoryName', 'like', '%' . $data['cat'] . '%'
])
->toSql();
dd($results);


I debugged this code the result is :

"select `ads`.*, `category`.`categoryName`, `sub_categories`.`subCategoryName`, `location`.`locationName` from `ads` inner join `category` on `category`.`categoryID` = `ads`.`categoryID` inner join `sub_categories` on `sub_categories`.`subCategoryID` = `ads`.`subCategoryID` inner join `site_users` on `site_users`.`siteUserID` = `ads`.`createdBy` inner join `location` on `location`.`locationID` = `site_users`.`location` where (`0` = ? and `1` = ? and `2` = ? and `3` = ? and `4` = ?) or (`0` = ? and `1` = ? and `2` = ? and `3` = ? and `4` = ? and `5` = ?)"



How can i do simple search function with multiple like operation
please give me a guide. Thanks

Answer

Something like this:

  $results = DB::table('ads')
            ->join('category', 'category.categoryID', '=', 'ads.categoryID')
            ->join('sub_categories', 'sub_categories.subCategoryID', '=', 'ads.subCategoryID')
            ->join('site_users', 'site_users.siteUserID', '=', 'ads.createdBy')
            ->join('location', 'location.locationID', '=', 'site_users.location')
            ->select('ads.*', 'category.categoryName', 'sub_categories.subCategoryName', 'location.locationName')
            ->where(function ($query) use ($data){
              $query->where('ads.title', 'LIKE', '%'.$data['q'].'%')
                   ->orWhere('location.locationName', 'LIKE', '%'.$data['loc'].'%')
                   ->orWhere('category.categoryName', 'LIKE', '%'.$data['cat'].'%')                        
            })
            ->where([
                'ads.isActive', 1
            ])
            ->toSql();

Hope it helps

Comments