Queue Table using PHP/MySQL/Ajax

Question

I have database table schedule with id, date, time, cabinet columns. Also I have table cabinets with id, cabinetNumber, title columns. I should generate table, but it should look like queue table.

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

if time is busy it will be +, if free -.

I need modify my mysql query. And should be possible to change date (usege datepicker and Ajax) and change table rows values depend of date. But if date does not exist, query will be empty and table will be without rows. How can i fixed it?

MySQL query :

$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 :

<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>

And 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>

I had solwe my problem. I added new query query2 from cabinets table.

<?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

The key is to keep the queries simple, use PHP to process and display the data. I will not go in to the altering of data with datapicker and ajax as you need to get the displaying correct first. Please take a look at this code and understand what it does and why it solves the problem of when there is no data for a date. You dont seem to have added the cabinet name in your table, so I kept it as an id. You can do a simple join in the query to get the cabinet name.

PHP code

<?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>