php - 酒店预订查询

Question

我在酒店预订系统中有三个查询，其中预订自动从房间总数中扣除。我只希望这三个查询全部出现在一个查询中。我的查询如下所示:

$p = 在 foreach 循环中，其中 $p['id'] 是 id每个房间等级的最大房间数，$p['qty'] 是每个房间等级的最大房间数。

$a = $p['id'];
$query = mysql_query("SELECT sum(qty) FROM prereservation where '$arrival' BETWEEN arrival and departure and room_id ='$a' and status = 'active'");
        while($rows = mysql_fetch_array($query))
          {
          $inogbuwin=$rows['sum(qty)'];
          }
$query = mysql_query("SELECT sum(qty) FROM prereservation where departure BETWEEN '$arrival' and '$departure' and room_id ='$a' and status = 'active'");
        while($rows = mysql_fetch_array($query))
          {
          $inogbuwin2=$rows['sum(qty)'];
          }
$query = mysql_query("SELECT sum(qty) FROM prereservation where '$departure' BETWEEN arrival and departure and room_id ='$a' and status = 'active'");
        while($rows = mysql_fetch_array($query))
          {
          $inogbuwin3=$rows['sum(qty)'];
          }

<select>
 <option value="0"></option>
 <? $counter = 1; ?>
 <? while ($counter <= ($p['qty'])-($inogbuwin + $inogbuwin2 + $inogbuwin3)){ ?>
 <option value="<?php echo $counter ?>"><?php echo $counter ?></option>
 <? $counter++;
 }?>
</select>

每次我在这三个查询的范围内放置一个日期时，扣除的总额也会增加三倍，这就是我希望这三个查询全部合而为一的原因。

例如我在数据库中有一条记录到达 = 27/10/2012 和离开 = 29/10/2012

并且范围内不可用的输入日期是

 $arrival = 27/10/2012 and $departure = 29/10/2012 
 $arrival = 26/10/2012 and $departure = 27/10/2012 
 $arrival = 29/10/2012 and $departure = 30/10/2012 
 $arrival = 26/10/2012 and $departure = 30/10/2012 
 $arrival = 29/10/2012 and $departure = 29/10/2012 

所以这三个查询中的每个日期也是扣除，所以我希望这些查询合而为一。谢谢大家

毕竟我修正了错误，但只剩下一个问题。我只将预订日期固定在一个月内，问题是当到达和离开的输入不在同一个月时，它也不起作用。下面是我的代码。

<?
$a = $p['id'];
    $query = mysql_query("SELECT
    SUM(IF('$arrival' BETWEEN arrival and departure, qty, 0)) AS bu1,
    SUM(IF('$departure' BETWEEN arrival and departure, qty, 0)) AS bu2,
    SUM(IF(arrival > '$arrival' and departure < '$departure', qty, 0)) AS bu3
    FROM prereservation WHERE room_id ='$a' and status = 'active'");
    $row = mysql_fetch_array($query);

    $test1 = $row['bu1'];
    if ($row['bu1'] == $row['bu2']){
        $test2 = $row['bu2'] - $row['bu1'];
    }else{
        $test2 = $row['bu2'];
    }       
        $test3 = $row['bu3'];
    ?>      
<select id="select" name="qty[]" style=" width:50px;" onchange="checkall()">
<option value="0"></option>
    <? $counter = 1; ?>
<? while ($counter <= ($p['qty']) - ($test1 + $test2 + $test3)){ ?>
    <option value="<?php echo $counter ?>"><?php echo $counter ?></option>
    <? $counter++;
    }?>

请大家帮帮我如何解决这个预订查询，或者也许有其他方法可以在 php 代码中解决这个问题。谢谢大家。

终于完成并找到了解决方案，这里是:

            $a = $p['id'];
        $query1 = mysql_query("SELECT DISTINCT id, SUM(qty)
        FROM prereservation 
        WHERE 
        (
            ( '$arival1' BETWEEN arrival AND departure ) OR 
            ( '$departure1' BETWEEN arrival AND departure ) OR 
            ( arrival > '$arival1' AND departure < '$departure1' )
        )
            AND room_id ='$a' 
            AND STATUS = 'active'");  
        while($rows1 = mysql_fetch_assoc($query1)){
        $set1 = $rows1['SUM(qty)'];
        }   
        ?> 
        <select id="select" name="qty[]" style=" width:50px;" onchange="checkall()">
        <option value="0"></option>
        <? $counter = 1; ?>
        <? while ($counter <= ($p['qty']) - $set1){ ?>
        <option value="<?php echo $counter ?>"><?php echo $counter ?></option>
        <? $counter++;
        }?>
        </select>

谢谢你们分享你们的想法，这个解决方案是你们答案的组合..再次感谢!!

Answer 1

你可以这样做:

SELECT
    SUM(IF('$arrival'    BETWEEN arrival   and departure,    qty, 0)) AS bu1,
    SUM(IF(departure    BETWEEN '$arrival' and '$departure', qty, 0)) AS bu2,
    SUM(IF('$departure' BETWEEN arrival   and departure,    qty, 0)) AS bu3
FROM prereservation WHERE room_id ='$a' and status = 'active'"

然后在 PHP 中:

$query = mysql_query(...);
$row = mysql_fetch_array($query);
$inogbuwin =$row['bu1'];
$inogbuwin2=$row['bu2'];
$inogbuwin3=$row['bu3'];
mysql_free($query);

包括习惯警告 mysql_ 函数 are discouraged 迁移到 PDO

你会做得很好

The old API should not be used, and one day it will be deprecated and eventually removed from PHP. It is a popular extension so this will be a slow process, but you are strongly encouraged to write all new code with either mysqli or PDO_MySQL.