使用PHP/MySQL创建队列表

Question

我有包含id、date、time、cabinet列的数据库表。

我应该生成来自数据库的值的表，但它应该看起来像队列表。

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

如果时间繁忙，它将是+，如果是空闲的-。所以我试着用下面的代码来做。

我应该改变循环..一些想法？谢谢!

我也有id，cabinetNumber，title列的桌柜。我需要修改我的mysql查询。应该可以更改日期(使用datepicker和Ajax)和更改表行值。

  $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"><?=$busy?></td>
                <?php else: ?>
                    <td class="free"><?=$cabinet; ?>
                        <form action="1.php" method="post">
                            <input type="hidden" name="time"  value="<?= $j;?>" /><?=$data['date']?>
                            <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>

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

Answer 1

我想你不需要$i的循环

<table class="table table-hover">
    <tr class=".info"><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 for($j=8; $j<=15; $j++)  : ?>
           <?php $time = $data['time'];
                 $time = round($time);
                 $cabinet = $data['cabinet'];
           ?>
           <?php if($j == $time): ?>
               <td class="busy"></td>
           <?php else: ?>
           <td class="free">
               <form action="1.php" method="post"><?=$cabinet; ?>
                   <input type="hidden" name="time"  value="<?= $j; ?>" />
                   <input type="submit" style="free" value="" name="sub"/>
               </form>
           </td>
           <?php endif?>
        <?php  endfor ?>
    </tr>
<?php endwhile ?>

</table>

UPDATE将您的查询更改为以下查询：

http://sqlfiddle.com/#!9/84bae/3

SELECT cab,
   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 `dayshedule`
GROUP BY cab

并将您的php代码更改为：

<?php while($data=mysql_fetch_array($query)) :?>
    <tr>
       <?php for($j=8; $j<=15; $j++)  : ?>
           <?php 
                 $busy = $data['h'.$j];
                 $cabinet = $data['cab'];
           ?>
           <?php if($busy>0): ?>
               <td class="busy"></td>
           <?php else: ?>
           <td class="free">
               <form action="1.php" method="post"><?=$cabinet; ?>
                   <input type="hidden" name="time"  value="<?= $j; ?>" />
                   <input type="submit" style="free" value="" name="sub"/>
               </form>
           </td>
           <?php endif?>
        <?php  endfor ?>
    </tr>
<?php endwhile ?>

更新在此处更新您的代码：

<tr class=".info"><td>Cab #</td><td >8:00</td>...

还有这里：

<tr>
   <?php  $cabinet = $data['cab']; ?>
   <td><?=$cabinet ?></td>
   <?php  for($j=8; $j<=15; $j++)  : ?>
       <?php 
             $busy = $data['h'.$j];

       ?>