Arshad Ali Arshad Ali - 7 months ago 14
PHP Question

building search query in laravel5 gives error Syntax error or access violation: 1064

I want to implement a simple search functionality in laravel5 app as

use DB;
use App\Models\User;
use Illuminate\Http\Request;

use ConnectIn\Http\Requests;

class SearchController extends Controller
{
public function getResults(Request $request)
{

$query = $request->input('name');
if (!$query) {
return redirect()-> route('home') -> with('info', "Search must contain some charactres");
}
else{

$users = User::where(DB::raw("CONCAT (first_name, ' ', last_name), "),
"LIKE", "%{$query}%")
->orwhere('username', "LIKE", "%{$query}%")
->get();
dd('search is ALL OK' . $users);
}
return view('search.results');
}

}


with the code provided above I get error

QueryException in Connection.php line 673:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' LIKE ? or `username` LIKE ?' at line 1 (SQL: select * from `users` where CONCAT (fisrt_name, ' ', last_name), LIKE %s% or `username` LIKE %s%)
in Connection.php line 673
at Connection->runQueryCallback('select * from `users` where CONCAT (fisrt_name, ' ', last_name), LIKE ? or `username` LIKE ?', array('%{$query}%', '%{$query}%'), object(Closure)) in Connection.php line 629
at Connection->run('select * from `users` where CONCAT (fisrt_name, ' ', last_name), LIKE ? or `username` LIKE ?', array('%{$query}%', '%{$query}%'), object(Closure)) in Connection.php line 342
at Connection->select('select * from `users` where CONCAT (fisrt_name, ' ', last_name), LIKE ? or `username` LIKE ?', array('%{$query}%', '%{$query}%'), true) in Builder.php line 1508
at Builder->runSelect() in Builder.php line 1494
at Builder->get(array('*')) in Builder.php line 596
at Builder->getModels(array('*')) in Builder.php line 303
at Builder->get() in SearchController.php line 25
at SearchController->getResults(object(Request))
at call_user_func_array(array(object(SearchController), 'getResults'), array(object(Request))) in Controller.php line 80
at Controller->callAction('getResults', array(object(Request))) in ControllerDispatcher.php line 146
at ControllerDispatcher->call(object(SearchController), object(Route), 'getResults') in ControllerDispatcher.php line 94
at ControllerDispatcher->Illuminate\Routing\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 52
at Pipeline->Illuminate\Routing\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 103
at Pipeline->then(object(Closure)) in ControllerDispatcher.php line 96
at ControllerDispatcher->callWithinStack(object(SearchController), object(Route), object(Request), 'getResults') in ControllerDispatcher.php line 54

Answer

First check $query contain the string to search

User::where(DB::raw('CONCAT(first_name," ", last_name)'), 'like', '%'.$query.'%')->orwhere('username', 'like', '%'.$query.'%')->get();
Comments