Parameterize this Query

Well instead of passing the value you can pass in a ? or a string that represents the value

$cities = DB::select(DB::raw(' select concat(City, \', \',State) as displayText,
                                  City as city, State as state,
                                  min(( 3959 * acos(
                                    cos( radians(:latitude) ) *
                                    cos( radians( latitude ) ) *
                                    cos( radians( longitude ) - radians(:longitude) ) +
                                    sin( radians(:latitude) ) *
                                    sin( radians( latitude ) ) ) )) AS distance
                                  from locations
                                  where city like ":city%"
                                  group by city,state,displayText
                                  order by distance asc', 
[
    'latitude' => $latitude,
    'longitude' => $longitude,
    'city' => $city,
]));

Instead of using the keyword you can also use ? instead of :latitude and :longitude. However you need to use the correct order of the parameters for each ?.

Source: https://stackoverflow.com/questions/20864872/how-to-bind-parameters-to-a-raw-db-query-in-laravel-thats-used-on-a-model

$cities = DB::unprepared('select concat(City, \', \',State) as displayText,
                                  City as city, State as state,
                                  min(( 3959 * acos(
                                    cos( radians( :latitude) ) *
                                    cos( radians( latitude ) ) *
                                    cos( radians( longitude ) - radians(:longitude) ) +
                                    sin( radians( :latitude) ) *
                                    sin( radians( latitude ) ) ) )) AS distance
                                  from locations
                                  where city like :city%
                                  group by city,state,displayText
                                  order by distance asc', [
    'latitude' => $latitude,
    'longitude' => $longitude,
    'city' => $city,
]);

Notice how the attributes are named :latitude, and then passed in as an array as the 2nd parameter.

@Cronix this seems to be returning a sql syntax error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':latitude) ) * cos( radians( latitude ) ) * ' at line 4 (SQL: select concat(City, ', ',State) as displayText, City as city, State as state, min(( 3959 * acos( cos( radians( :latitude) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(:longitude) ) + sin( radians( :latitude) ) * sin( radians( latitude ) ) ) )) AS distance from locations where city like :city% group by city,state,displayText order by distance asc)

sorry, change unprepared to selectRaw.

@Cronix Do I need to refactor the query to use selectRaw, seems like an undefined method.

I also get a SQL Syntax error when using:

$cities = DB::select(DB::raw(' select concat(City, \', \',State) as displayText,
                                  City as city, State as state,
                                  min(( 3959 * acos(
                                    cos( radians(:latitude) ) *
                                    cos( radians( latitude ) ) *
                                    cos( radians( longitude ) - radians(:longitude) ) +
                                    sin( radians(:latitude) ) *
                                    sin( radians( latitude ) ) ) )) AS distance
                                  from locations
                                  where city like ":city%"
                                  group by city,state,displayText
                                  order by distance asc',
                                  [
                                      'latitude' => $latitude,
                                      'longitude' => $longitude,
                                      'city' => $city,
                                  ]));

@Cronix any ideas?

You have to remove DB::raw():

$cities = DB::select('select ...', ['latitude' => $latitude, ...]);

$cities = DB::select(' select concat(City, ', ',State) as displayText, City as city, State as state, min(( 3959 * acos( cos( radians(:latitude) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(:longitude) ) + sin( radians(:latitude) ) * sin( radians( latitude ) ) ) )) AS distance from locations where city like ":city%" group by city,state,displayText order by distance asc', [ 'latitude' => $latitude, 'longitude' => $longitude, 'city' => $city, ]);