php - 使用 PHP/MySQL/Ajax 的队列表

Question

我有数据库表schedule，其中包含id、date、time、cabinet 列。我还有表 cabinets ，其中包含 id、cabinetNumber、title 列。我应该生成表，但它应该看起来像队列表。

cabinet 8,9,10,11,12,13,14,15,16
  102   - - -  +  -   -  +  - - 
  103   - + -  -  -   -  +  - - 

如果时间繁忙则为+，如果空闲则为-。

我需要修改我的 mysql 查询。并且应该可以更改日期(使用日期选择器和 Ajax)并根据日期更改表行值。但如果日期不存在，查询将为空，表将没有行。我该如何修复它？

MySQL 查询:

$q = $_GET['date'];
$query = mysql_query("SELECT date,cabinet,
   SUM(IF(ROUND(`time`)=8,1,0)) as h8,
   SUM(IF(ROUND(`time`)=9,1,0)) as h9,
   SUM(IF(ROUND(`time`)=10,1,0)) as h10,
   SUM(IF(ROUND(`time`)=11,1,0)) as h11,
   SUM(IF(ROUND(`time`)=12,1,0)) as h12,
   SUM(IF(ROUND(`time`)=13,1,0)) as h13,
   SUM(IF(ROUND(`time`)=14,1,0)) as h14,
   SUM(IF(ROUND(`time`)=15,1,0)) as h15
FROM `schedule` WHERE date = '".$q."'
GROUP BY cabinet") or die(mysql_error());
?>

表:

<table class="table table-hover">
    <tr class=".info"><td>Cab</td><td >8:00</td><td >9:00</td><td >10:00</td><td >11:00</td><td >12:00</td><td >13:00</td><td >14:00</td><td >15:00</td></tr>
    <!--  -->
    <?php while($data=mysql_fetch_array($query)) :?>
        <tr>
            <?php  $cabinet = $data['cabinet']; ?>
            <td><?=$cabinet ?></td>
            <?php  for($j=8; $j<=15; $j++)  : ?>
                <?php
                $busy = $data['h'.$j];
                ?>
                <?php if($busy>0 && $data['date']===$q ): ?>
                    <td class="busy"></td>
                <?php else: ?>
                    <td class="free">
                        <form action="1.php" method="post">
                            <input type="hidden" name="time"  value="<?= $j;?>" />
                            <input type="hidden" name="cabinet"  value="<?= $cabinet;?>" />
                            <input type="submit" style="free" value="" name="sub"/>
                        </form>
                    </td>
                <?php endif?>

            <?php  endfor ?>
        </tr>
    <?php endwhile ?>
</table>

和 jQuery/Ajax:

<script>
    $(function() {
        $( "#datepicker" ).datepicker({ dateFormat: 'yy-mm-dd' });
    });
    $( ".date" ).on('change', function(){
        var date = $('#datepicker').val();
        $.ajax({
            type:'get',
            url:'table.php',
            data : {
                'date' : date
            },
            success: function(data) {
                $('#result').html(data);
            }
        });
    });
</script>

我已经解决了我的问题。我从橱柜表中添加了新查询query2。

<?php $query2 = mysql_query("select cabinetNum from cabinets") ?>
<table class="table table-hover">
    <tr class=".info"><td>Cab</td><td >8:00</td><td >9:00</td><td >10:00</td><td >11:00</td><td >12:00</td><td >13:00</td><td >14:00</td><td >15:00</td></tr>
    <!--  -->
    <?php while($data2=mysql_fetch_array($query2)): ?>
    <?php $data=mysql_fetch_array($query) ?>
        <tr>
            <?php  $cabinet = $data2['cabinetNum']; ?>
            <td><?=$cabinet ?></td>
            <?php  for($j=8; $j<=15; $j++)  : ?>
                <?php
                $busy = $data['h'.$j];
                ?>
                <?php if($busy>0  ): ?>
                    <td class="busy"></td>
                <?php else: ?>
                    <td class="free">
                        <form action="1.php" method="post">
                            <input type="hidden" name="time"  value="<?=$j?>" />
                            <input type="hidden" name="cabinet" value="<?=$cabinet?>" />
                            <input type="hidden" name="date"  value="<?=$q?>" />
                            <input type="submit" class="free" value="" name="sub"/>
                        </form>
                    </td>
                <?php endif?>
            <?php  endfor ?>
        </tr>
    <?php endwhile ?>
</table>

Answer 1

关键是保持查询简单，使用 PHP 处理和显示数据。我不会讨论使用 datapicker 和 ajax 更改数据，因为您需要首先正确显示。请看一下这段代码，了解它的作用以及为什么它解决了没有日期数据的问题。您似乎没有在表中添加柜子名称，所以我将其保留为 id。您可以在查询中进行简单的联接来获取内阁名称。

PHP 代码

<?php

//Using the object orientated style from this page
//http://php.net/manual/en/mysqli-stmt.bind-param.php

//Connect to database
$link = new mysqli("localhost", "root", "", "scheduler");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

//Use todays date unless specified
$date = date("Y-m-d");
if (isset($_GET['date'])) {
    $date = $_GET['date'];
}

$cabinet_activity = array();

//First do a query to get all distinct cabinets, whether they have activity for the date or not
if ($cabinets = $link->prepare("SELECT DISTINCT cabinet FROM schedule")) {
    $cabinets->execute();
    $result = $cabinets->get_result();
    while ($this_row = $result->fetch_assoc()) {
        $cabinet_activity[$this_row['cabinet']] = array();  //Initialise the busy array for the next query
    }
}

if ($stmt = $link->prepare("SELECT id, HOUR(time) as hour, cabinet FROM schedule WHERE date = ?")) {

    $stmt->bind_param('s', $date);

    $stmt->execute();

    $result = $stmt->get_result();

    while ($this_row = $result->fetch_assoc()) {
        $id = $this_row['id'];
        $hour = $this_row['hour'];
        $cabinet_id = $this_row['cabinet'];

        //Add to cabinet to cabinet activity list (should not be necessary now)
        /*
        if (!array_key_exists($cabinet_id, $cabinet_activity)) {
            $cabinet_activity[$cabinet_id] = array();
        }
        */

        //Add the activity time to the busy hours
        if (!in_array($hour, $cabinet_activity[$cabinet_id])) {
            $cabinet_activity[$cabinet_id][] = $hour;
        }

    }
}

$min_hours = 8;
$max_hours = 16;

//Display information:
?>
<table border="1">
    <thead>
        <tr>
            <td>Cabinet</td>
            <?php
            for($hour = $min_hours; $hour <= $max_hours; $hour++) {
                ?><td><?php print $hour; ?></td><?php
            }
            ?>
        </tr>
    </thead>
    <tbody>
    <?php
    //For each cabinet
    foreach($cabinet_activity as $cabinet_id => $busy_hours) {
        ?><tr>
            <td><?php print $cabinet_id; ?></td>
            <?php
            for($hour = $min_hours; $hour <= $max_hours; $hour++) {
                if (in_array($hour, $busy_hours)) {
                    ?><td>+</td><?php
                } else {
                    ?><td>-</td><?php
                }
            }
            ?>
        </tr><?php
    } ?>
    </tbody>
</table>