我想从两个独立工作的查询或一个可以组合我的两个查询生成单个结果响应。
我的第一个查询是: $query =" SELECT *
FROM locationdetails
WHERE user_id = " . $userid . " AND trip IN (SELECT DISTINCT trip FROM locationdetails
GROUP BY trip
HAVING _id = max( _id ))
ORDER BY trip DESC
LIMIT 0 , 5";
和第二个查询几乎相同:
$query =" SELECT *
FROM locationdetails
WHERE user_id = " . $userid . " AND trip IN (SELECT DISTINCT trip FROM locationdetails)
GROUP BY trip
HAVING _id = min( _id )
ORDER BY trip DESC
LIMIT 0 , 5";
,但我需要生成一个响应。需要一些紧急帮助。我还通过如下方法生成响应:
function ExecuteQuery($query) {
$response = new ApiResult();
if (mysqli_query($this->con, $query)) {
$results_array = array();
$sqlResult = mysqli_query($this->con, $query);
while ($row = $sqlResult->fetch_assoc()) {
$results_array[] = $row;
}
if ($results_array != null || !empty($results_array)) {
$response->Message = 'SUCCESSFULL';
$response->Result = $results_array;
} else {
$response->Message = 'SUCCESSFULL';
// $response->Result = 'N;
}
mysqli_close($this->con);
} else {
$response->Message = 'Some Internal problem occurred';
$response->Error = true;
}
return $response;
}
不如这样写:
$query =" SELECT trip, MIN(_id) AS minid, MAX(_id) AS maxid
FROM locationdetails
WHERE user_id = " . $userid . "
GROUP BY trip
ORDER BY trip DESC
LIMIT 0 , 5";
这将为您提供每个不同旅行的列表,以及每个旅行的最小和最大id。
如果您希望两个查询中的记录唯一,请使用UNION和如果需要两个查询中的所有记录,请使用UNION ALL。
(SELECT *
FROM locationdetails
WHERE user_id = " . $userid . " AND trip IN (SELECT DISTINCT trip FROM locationdetails
GROUP BY trip
HAVING _id = max( _id ))
ORDER BY trip DESC
LIMIT 0 , 5
)
UNION
OR
UNION ALL
(SELECT *
FROM locationdetails
WHERE user_id = " . $userid . " AND trip IN (SELECT DISTINCT trip FROM locationdetails)
GROUP BY trip
HAVING _id = min( _id )
ORDER BY trip DESC
LIMIT 0 , 5
)