检查数据库中的时间是否超过特定小时 [英] Check if time in a database surpasses specific hour

问题描述

我正在使用PHP制作日历，并遇到了一些问题：

我希望日历更自动化，因为我手动创建表格并把时间和日子放在自己身上，这很好，但是我的问题是我每个小时都需要一个SQL函数（从10点到17点），这似乎是非常低效的。

我的HTML表格代码的结构如下：

 < TR> 
< td> 
< span> 
< p style =float：left> 10：00< / p> 
< p style =float：right>& nbsp;& nbsp;& nbsp;（<？php echo $ var-> monday_10;？>）< / p> 
< / span> 
< / td> 
< td>< / td> 
< td>< / td> 
< td>< / td> 
< td>< / td> 
< td>< / td> 
< td>< / td> 
< / tr> 
  

我有这个PHP函数：

<$ （$ conn）
{
// TODO：评论这个函数
// TODO：如果预订时间过去了（例如： 。13:00）然后添加到计数
$ sql =

SELECT DAYNAME（arrivalTime）AS day，COUNT（*）AS count
FROM bookings
WHERE HOUR（arrivalTime）= 10
AND DAYNAME（arrivalTime）='星期一'
;
$ result = $ conn-> query（$ sql）;
$ b $ if（$ result-> num_rows> 0）
{
while（$ row = $ result-> fetch_assoc（））
{
$ this-> monday_10 = $ row [count];
}
}
else
{
echo0 results;






$ b

正如你所看到的，这个例子中的时间（ 13.00）和星期一（星期一）手动输入。我怎样才能改变它，以便从表中获得时间/日期，并保持每个td的功能相同？

此外，我想改变我的SQL查询（如PHP get_CalendarCount函数所示），以便它检查数据库中的预订是否超过一段时间（例如，到达时间10:00 - pickup时间12:00 - 两者都是10:00和11:00需要计算预订时间（每个小时1小时）：

非常感谢帮助因为我有点卡在这里，不能真正找到任何解决方案。

，你应该改变，但在查询中使用变量的问题，所以你不必手动把它们放入。对于你的qu关于制作小时和 dayname 变量，你需要改变它：

  public function get_CalendarCount（$ conn）
 {
 $ sql =SELECT DAYNAME（arrivalTime）AS day，COUNT（*）AS count 
 FROM预订WHERE HOUR（arrivalTime）= 10 AND DAYNAME（arrivalTime）='星期一'; 
  

转换为：

  public function get_CalendarCount（$ conn，$ hour，$ dayname）
 {
 $ sql =SELECT DAYNAME（arrivalTime）AS day，COUNT（*）AS count 
 FROM预订WHERE HOUR（arrivalTime）=。$ hour。和DAYNAME（arrivalTime）='。 $ dayname。'; 
  

I'm making a calendar using PHP and have run into some issues:

I want the calendar to be more automated at the moment as I'm manually creating the table and putting the times and days in myself, which is fine, but my issue is that I'd need a SQL function for every single hour (from 10:00-17:00) for every single day which seems extremely inefficient.

My HTML table code is structured as such:

<tr>
  <td>
    <span>
      <p style="float:left">10:00</p>
      <p style="float:right">&nbsp;&nbsp;&nbsp;(<?php echo $var->monday_10; ?>)</p>
    </span>
  </td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
</tr>

And I have this PHP function:

public function get_CalendarCount($conn)
{
    // TODO: Comment this function
    // TODO: If a booking goes over a time (eg. 13:00) then add to count
    $sql = 
    "
    SELECT DAYNAME(arrivalTime) AS day, COUNT(*) AS count
    FROM bookings
    WHERE HOUR(arrivalTime) = 10
    AND DAYNAME(arrivalTime) = 'Monday'
    ";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) 
    {
        while($row = $result->fetch_assoc()) 
        {
            $this->monday_10 = $row["count"];
        }
    } 
    else 
    {
        echo "0 results";
    }
}

As you can see, the time in this example (13.00) and day (Monday) is manually put in. How can I change this so it gets the time / day from the table and keep is as the same function for every td?

Furthermore, I would like to alter my SQL query (as shown in the PHP get_CalendarCount function) so that it checks if a booking in the database goes over a time (eg. arrivalTime 10:00 - pickupTime 12:00 - both 10:00 and 11:00 need to count as the booking is for the hours 10:00 & 11:00 (1 hour each):

Would really appreciate help as I'm a bit stuck here, can't really find anything for this solution.

解决方案

This is not answering the logic, you should change but the question of using variables in your query, so you don't have to manually put them in. For your question about making the hour and dayname variable you need to change this:

public function get_CalendarCount($conn)
{
$sql ="SELECT DAYNAME(arrivalTime) AS day, COUNT(*) AS count
FROM bookings WHERE HOUR(arrivalTime) = 10 AND DAYNAME(arrivalTime) = 'Monday'";

into:

public function get_CalendarCount($conn,$hour,$dayname)
{
$sql ="SELECT DAYNAME(arrivalTime) AS day, COUNT(*) AS count
FROM bookings WHERE HOUR(arrivalTime) = ".$hour." AND DAYNAME(arrivalTime) = '" . $dayname ."'";

这篇关于检查数据库中的时间是否超过特定小时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！