我有2个表。工人表和工作表。工作表接受latitude
,longitude
和work radius
。作业表具有latitude
和longitude
。我需要一个选择的查询,该查询从作业表中获取行,但在工作表的work radius
中。
下面是表的结构,以及到目前为止我拥有的结构,即基于纬度和经度的表格从表中进行选择。
感谢您的任何帮助。
工作表:
CREATE TABLE "worker" (
"id" varchar(25) NOT NULL,
"latitude" varchar(25) NOT NULL,
"longitude" varchar(25) NOT NULL,
"work_radius" int(25) NOT NULL,
"address" varchar(255) NOT NULL,
PRIMARY KEY ("id")
);
作业表:
CREATE TABLE "jobs" (
"id" varchar(25) NOT NULL,
"latitude" varchar(25) NOT NULL,
"longitude" varchar(25) NOT NULL,
"address" varchar(255) NOT NULL,
PRIMARY KEY ("id")
);
选择查询:
$jobs_load = $dbh->prepare("
SELECT
`*`,
(
6371 *
acos(
cos( radians( :lat_id ) ) *
cos( radians( `lat_id` ) ) *
cos(
radians( `long_id` ) - radians( :long_id )
) +
sin(radians(:lat_id) *
sin(radians(`lat_id`))
)
) `work_radius`
FROM
`jobs`
HAVING
`work_radius` < :work_radius
ORDER BY
`work_radius`
LIMIT
25");
您需要在所有worker
上循环,然后为每个工人在其"范围"中找到作业,请尝试以下
$conn = $conn = new PDO("mysql:host=localhost;dbname=test", 'root', '');
$result = $conn->query("SELECT * FROM worker");
$allWorkers = [];
foreach($result as $row)
{
$allWorkers[] = array(
'id'=> $row["id"],
'latitude'=>$row["latitude"],
'longitude'=>$row["longitude"],
'work_radius'=> $row["work_radius"]
);
}
$distance_query = 'SELECT
*, (
6371 * acos (
cos ( radians(:latitude) )
* cos( radians( latitude) )
* cos( radians( longitude ) - radians(:longitude) )
+ sin ( radians(:latitude) )
* sin( radians( latitude) )
)
) AS distance
FROM jobs
having distance < :work_radius
ORDER BY distance
LIMIT 0 , 20;';
$workers_jobs = array();
$stmt = $conn->prepare($distance_query);
foreach( $allWorkers as $worker)
{
$stmt->bindParam(':latitude', $worker['latitude']);
$stmt->bindParam(':longitude', $worker['longitude']);
$stmt->bindParam(':work_radius', $worker['work_radius']);
$stmt->execute();
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$workers_jobs[$worker['id']]['id'] = $row['id'];
$workers_jobs[$worker['id']]['latitude'] = $row['latitude'];
$workers_jobs[$worker['id']]['longitude'] = $row['longitude'];
}
}
//$ workers_jobs每位工人和他的工作
update
假设您有一个额外的列说" shift "(varchar 50(在作业表中
+----+-----------+-----------+---------+
| id | latitude | longitude | Shift |
+----+-----------+-----------+---------+
| 1 | 51.919438 | 19.145136 | morning |
| 2 | 49.852276 | 15.015519 | evening |
| 3 | 49.208705 | 11.946989 | night |
+----+-----------+-----------+---------+
如何将其包括在$distance_query
中?绑定良好有点棘手,所以在进一步前进之前,您必须阅读此部分
$conn = $conn = new PDO("mysql:host=localhost;dbname=test", 'root', '');
$result = $conn->query("SELECT * FROM worker");
$allWorkers = [];
foreach($result as $row)
{
$allWorkers[] = array(
'id'=> $row["id"],
'latitude'=>$row["latitude"],
'longitude'=>$row["longitude"],
'work_radius'=> $row["work_radius"]
);
}
//** bind IN - placeholders**//
$shifts = ['morning','night'];
$in = '';
foreach ($shifts as $i => $item)
{
$key = ':shift'.$i;
$in .= "$key,";
}
$in = rtrim($in, ',');
//** bind IN - placeholders end **//
$distance_query = 'SELECT
*, (
6371 * acos (
cos ( radians(:latitude) )
* cos( radians( latitude) )
* cos( radians( longitude ) - radians(:longitude) )
+ sin ( radians(:latitude) )
* sin( radians( latitude) )
)
) AS distance
FROM jobs
where shift IN (' . $in . ')
having distance < :work_radius
ORDER BY distance
LIMIT 0 , 20;';
$workers_jobs = array();
$stmt = $conn->prepare($distance_query);
foreach( $allWorkers as $worker)
{
$stmt->bindParam(':latitude', $worker['latitude']);
$stmt->bindParam(':longitude', $worker['longitude']);
$stmt->bindParam(':work_radius', $worker['work_radius']);
//** bind IN - values**//
$shifts = ['morning','night'];
$in = '';
foreach ($shifts as $i => $item)
{
$key = ':shift'.$i;
$stmt->bindParam($key, $item);
}
//** bind IN - values ends**//
$stmt->execute();
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$workers_jobs[$worker['id']]['id'] = $row['id'];
$workers_jobs[$worker['id']]['latitude'] = $row['latitude'];
$workers_jobs[$worker['id']]['longitude'] = $row['longitude'];
}
}
echo '<pre>'; print_r($workers_jobs);