时间戳

首页 > 解决方案 > Trying to Get Data From Db using Form Under 10 Km

php - Trying to Get Data From Db using Form Under 10 Km

问题描述

i am trying to get data from database under 10 km records through latitude and longitude using form but i am facing erorr

erorr

Illuminate\Database\QueryException SQLSTATE[42S22]: Column not found: 1054 Unknown column 'distance' in 'having clause' (SQL: select count(*) as aggregate from users having distance < 25)

Web.php

Route::get('/map','MapController@index');

Route::get('/userview','MapController@getUserByLatLong');

Route::post('/showdata','Mapcontroller@showdata');

User.blade.php

<form method="POST" action="{{ action('Mapcontroller@showdata') }}">
@csrf
<lable>Latitude</lable>
<input type="text" name="latitude" /><br>
<lable>Longitude</lable>
<input type="text" name="longitude" /><br>
<lable>Kilomneter</lable>
<input type="text" name="kilometer" /><br>
<input type="submit" name="submit" /><br>
</form>

Controller code

public function showdata(Request $request)
    {
        $this->validate($request, [
            'latitude' => 'required',
            'longitude' => 'required'
        ]);
        $latitude = $request->input('latitude');
        $longitude = $request->input('longitude');

        $users = DB::table('users')
        ->select(DB::raw($latitude,$longitude, 'SQRT(POW(69.1 * (latitude - 24.900110), 2) + POW(69.1 * (67.099760 -longitude) * COS(latitude / 57.3), 2)) AS distance'))
        ->havingRaw('distance < 25')
        ->OrderBy('distance')
        ->paginate(10)
        ->get();
        echo $users;
        return view('userview',['users' => $users]);


    }

标签： phplaravel

解决方案

我认为对您来说最好的解决方案是在您的用户表中添加列距离，而不是在您的模型中添加一个 mutator，如下所示：

public function setDistanceAttribute($value)
{
    return $this->attributes['distance'] = SQRT(POW(69.1 * ($this->['latitude'] - 24.900110), 2) + POW(69.1 * (67.099760 -$this->['longitude']) * COS(latitude / 57.3), 2));
}

然后更新您的查询：

 $users = DB::table('users')
        ->select(DB::raw($latitude,$longitude))
        ->havingRaw('distance < 25')
        ->OrderBy('distance')
        ->paginate(10)
        ->get();

希望它有效:)

推荐阅读