dtoqa66028 2013-09-05 09:37
浏览 48
已采纳

使用PHP如何从mysql中检索多个日期范围并返回日期数组

Table in mySQL holds hotel reservations for next few years.

Each row contains id, start_date, end_date and status. the start_date contains a date type which represents the check-in date and the end_date is the checkout date.

I would like to retrieve an array of individual dates so that I can loop through one year calendar and display the dates for next year that are free and which are reserved.

Since the hotel can rent out the room again on the end_date since checkout is early, the end_date itself doesn't need to be included.

example

ID | start_date |  end_date  | status

1  | 03/15/2014 | 03/18/2014 |  Paid
2  | 05/22/2014 | 05/25/2014 |  Deposit
3  | 08/12/2014 | 08/13/2014 |  Paid

and to array:

 $months_arry = array(1 => '03/15/2014', 1 => '03/16/2014', 1 => '03/17/2014',
 1 => '05/22/2014', 1 => '05/23/2014', 1 => '05/24/2014', 1 => '08/12/2014');

Since all 12 months of the calendar are displayed vertically on the same page it would be easiest to just check each date against the array to know if it should be green for vacant or red for occupied.

In asp 3.0 on sql server i would have just created a record set rs with "

SELECT start_date, end_date from HotelBookings where start_date >= '2013-12-31'
 AND enddate <= '2015-01-01' ORDER BY start_date ASC"

then a .. do until rs.EOF loop .. inside the

loop i would retrieve the start_date and end_date and loop them until the two are equal while adding + 1 to the start_date while building the array of individual dates.

i found this example while looking for answer but it looks ugly and not elegant

<?php
// Make a MySQL Connection
$query = "SELECT * FROM example"; 

$result = mysql_query($query) or die(mysql_error());


while($row = mysql_fetch_array($result)){
    echo $row['name']. " - ". $row['age'];
    echo "<br />";
}
?>
  • 写回答

2条回答 默认 最新

  • dougui5419 2013-09-05 11:13
    关注

    Be careful with a loop that contains an SQL statement in its body, as the SQL will run n times, where n is the number of times the loop body executes. That's potentially quite inefficient.

    The DateTime object can be your friend here.

    // note that an array with the same key pointing to more than one value will have its values override eachother.
    // array(1 => 'foo', 1 => 'bar') is generally not a good idea.
    $months_array = array('03/15/2014', '03/16/2014', '03/17/2014', '05/22/2014', '05/23/2014', '05/24/2014', '08/12/2014');
    
    // covert dates to check to DateTimes so that we can perform comparisons on them
    $to_check_array = array_map(function ($value) { return new DateTime($value); } , $months_array);
    
    // get all taken date ranges
    $result = mysql_query("SELECT `start_date`, `end_date` FROM `date_table`");
    
    // loop over all taken date ranges
    while ($row = mysql_fetch_row($result))
    {
      // convert dates to DateTimes to perform comparisons on them
      $start_date = new DateTime($row[0]);
      $end_date   = new DateTime($row[1]);
    
      // check each date against all taken ranges
      foreach ($to_check_array as $cur)
      {
        if ($cur >= $start_date and $cur < $end_date)
        {
          $taken = $cur->format('m/d/Y');
          echo "{$taken} is taken.<br>";
        }
      }
    }
    

    I think that should solve your problem. As for elegance, well, elegance is not exactly abundant in PHP. It's a language that's sort of been mish mashed together by many people from many backgrounds. It's a nice and simple, ever-evolving language, but not one I would call elegant. If you need to use PHP and are looking for some elegance in your web apps, I would recommend a framework like CodeIgniter or Laravel. The latter especially is quite elegant.

    Also, be careful using the old mysql_ functions, as those are being removed from coming versions of PHP. You might want to look into mysqli or PDO.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 MATLAB怎么通过柱坐标变换画开口是圆形的旋转抛物面?
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题
  • ¥20 求一个html代码,有偿