php 存储复杂的json格式查询（如：经纬度）

在开发中，有时我们可能存了一些复杂json格式不知道怎么查。我这里提供给大家参考下：

一、先上表数据格式（location字段的possiton经纬度以逗号分开的）

{"title":"澳海·文澜府","position":"112.850437,28.287553"} 

打印的sql语句如下

 SELECT id,title,location FROM `g_property` WHERE (  `status` = 0  AND `delete_time` IS NULL  AND (  SUBSTRING_INDEX(TRIM(BOTH '"' FROM json_extract(location, "$.position")),",",1) >=112.071284 and SUBSTRING_INDEX(TRIM(BOTH '"' FROM json_extract(location, "$.position")),",",1) <=112.871284  ) )

 二、php tp8框架代码如下：

1.拼接where条件sql语句

参数：

$location = input('location','');if($location != ''){$location = json_decode($location,true);$location = ' SUBSTRING_INDEX(TRIM(BOTH \'"\' FROM json_extract(location, "$.position")),",",1) >='. $location[0].' and SUBSTRING_INDEX(TRIM(BOTH \'"\' FROM json_extract(location, "$.position")),",",1) <='. $location[2].' and SUBSTRING_INDEX(TRIM(BOTH \'"\' FROM json_extract(location, "$.position")),",",-1) >='. $location[1].' and SUBSTRING_INDEX(TRIM(BOTH \'"\' FROM json_extract(location, "$.position")),",",-1) <= '.$location[3].' ';}

 2.查询代码：

$res = Property::field('id,title,img,p_prices_trend_id,city,area_id,block_id,place,average_price,location,status,is_topped,is_new,block_name')->where($location)->select();

以上代码具体根据你自已的业务来。本人只上传了核心的代码