如何选择下一个预约Mysql？

I have a table calles reservation and it contains all reservation for every vehicle. and I have a reservationStatus field which mean 1 = scheduled 2= pending, 0=canceled.

我有一个表calles预订,它包含每辆车的所有预订。我有一个reservationStatus字段,表示1 =预定2 =挂起,0 =取消。

So I want to select the next reservation date from the table based on the current pending record.

所以我想根据当前待处理记录从表中选择下一个预约日期。

So when i run this code

所以当我运行这段代码

SELECT dateFrom, dateTo FROM reservation WHERE reservationStatus = 2

that will give me the current pending reservation. Now from here I want to return the very first record where dateFrom field is less than or equal to the previous dateTo?

这将给我当前待定的预订。现在我想从这里返回第一条记录,其中dateFrom字段小于或等于前一个dateTo?

can this be done in one select statement? I know it can be done using 2 separate statement with some php but I was wondering if it can be written in MySql in one query?

这可以在一个选择语句中完成吗?我知道它可以使用2个单独的语句与一些PHP完成但我想知道它是否可以在一个查询中写入MySql?

EDIT - New bit added to the question

编辑 - 新问题添加到问题中

This is how I would solve it with 2 queries and some PHP.

这是我用2个查询和一些PHP来解决它的方法。

$sql = $db->query('SELECT dateTo FROM reservation WHERE Vehicle_id='. $id . ' AND reservationStatus = 2 LIMIT 1 ');
$first = $sql->fetch_assoc();
$sql->free();
$sql = $db->query('SELECT dateFrom FROM reservation WHERE Vehicle_id='. $id . ' AND reservationStatus = 1 AND (dateFrom > \''.$first['dateTo'] .'\') ORDER BY dateFrom ASC ');

$row = $sql->fetch_assoc();

$month = strtotime($first['dateTo']);
$end = strtotime($row['dateFrom']);

3 个解决方案

#1

Try

SELECT dateFrom, dateTo FROM reservation WHERE reservationStatus = 2
ORDER BY dateFome DESC
LIMIT 1

Will order the results by dateFrom (newest first) and just fetch the first row.

将按dateFrom(最新的第一行)排序结果,只需获取第一行。

#2

Here you go:

干得好:

SELECT dateFrom, dateTo FROM reservation WHERE reservationStatus = 2 
ORDER BY dateFrom DESC 
LIMIT 1

#3

Use order by with datefrome,and limit 1. This will give the result you required

使用datefrome的order by,并限制1.这将给出您需要的结果

SELECT dateFrom, dateTo reservation WHERE reservationStatus = 2
ORDER BY dateFome DESC
LIMIT 1

#1

Try

SELECT dateFrom, dateTo FROM reservation WHERE reservationStatus = 2
ORDER BY dateFome DESC
LIMIT 1