CODEWITHSUNDEEP
phplaravel-5mariadb
Arshad Ali
Arshad Ali

Reputation: 3274

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

Upvotes: 0

Views: 76

Answers (2)

Haseena P A
Haseena P A

Reputation: 17416

First check $query contain the string to search

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

Upvotes: 2

Dan H
Dan H

Reputation: 3623

Your query has a syntax error and a typo.

CONCAT (fisrt_name, ' ', last_name), LIKE %s%

Should be:

CONCAT (first_name, ' ', last_name) LIKE %s%

You can also do:

SELECT * FROM `users`
CONCAT (first_name, ' ', last_name) AS fullname
WHERE `username` LIKE %s%
HAVING fullname LIKE %s%

Upvotes: 0

Related Questions