SELECT statement using CONCAT() with WHERE using CONCAT()

Question

I want to build a query in MySQL with this statement:

SELECT CONCAT(students.FirstName, ' ', students.LastName) AS FullName FROM `students` WHERE CONCAT(students.FirstName, ' ', students.LastName)LIKE '%John%'

And now, I use Query Builder from Laravel where I can match the column using CONCAT(). Here's my code for reference:

    public function getStudent(Request $request)
    {
        $fullName = Student::select(FacadesDB::raw('SELECT CONCAT(LastName, ", ", FirstName) AS FullName'), )
            ->where(FacadesDB::raw('CONCAT(LastName, ", ", FirstName)', 'LIKE', "'% {{ $request->terms }} %'"))
            ->get();
        return response()->json($fullName);
    }

When I try to run the code, I got a result like this:

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 'SELECT CONCAT(LastName, ", ", FirstName) AS FullName from students where CO...' at line 1 (SQL: select SELECT CONCAT(LastName, ", ", FirstName) AS FullName from students where CONCAT(LastName, ", ", FirstName) is null)

Is there a way that I can achieve this one? It took me like almost 4 hours to solve this code and I still can't solve it. Every help would be appreciated. I'm a new Laravel user, btw.

raghav · Accepted Answer · 2021-10-17 16:28:44Z

1

you can use map function:-

Student::select("firstname","lastname")
->where("firstname","like","%john%")
->orWhere("lastname","like","%john%")
->get()->map(function($query){
    $fullname = $query->firstname." ".$query->lastname
    return ["fullname" => $fullname]
 })

answered Oct 17, 2021 at 16:28

raghav

963 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jhenryj09 Over a year ago

Thanks for the tip, dude. This is similar to what I solved lately. I'll try this one.

Tim Biegeleisen · Accepted Answer · 2021-10-17 11:30:49Z

0

I would keep it simple and just phrase your query as:

SELECT CONCAT(FirstName, ' ', LastName) AS FullName
FROM students
WHERE FirstName LIKE '%John%' OR LastName LIKE '%John%';

Updated Laravel code:

public function getStudent(Request $request)
{
    $fullName = Student::select(FacadesDB::raw('CONCAT(LastName, ", ", FirstName) AS FullName'), )
        ->where(FirstName, 'LIKE', '%John%')
        ->orWhere(LastName, 'LIKE', '%John%')
        ->get();
    return response()->json($fullName);
}

edited Oct 17, 2021 at 11:30

answered Oct 17, 2021 at 10:58

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

4 Comments

jhenryj09 Over a year ago

I still have an error like this: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'SELECT CONCAT(LastName, ", ", FirstName)' in 'field list'

Tim Biegeleisen Over a year ago

Try removing SELECT from the input to the raw function.

jhenryj09 Over a year ago

I remove SELECT and still got this error SQLSTATE[42S22]: Column not found: 1054 Unknown column 'CONCAT(LastName, ", ", FirstName)' in 'field list'

Tim Biegeleisen Over a year ago

Check the docs for making a raw query. The SQL query I gave you at the top of my answer should work fine.

Collectives™ on Stack Overflow

SELECT statement using CONCAT() with WHERE using CONCAT()

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related