Queue Table using PHP/MySQL

Question

I have database table with id, date, time, cabinet columns.

I should generate table with values from database, but it should look like queue table.

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

if time is busy it will be +, if free -. So I try do it using this code.

I should change loops.. some ideas? Thank You!

Also I have table cabinets with id, cabinetNumber, title columns. I need modify my mysql query. It should be possible to change date(usege datepicker and Ajax) and change table rows values.

  $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);
            }
        });
    });

Please, stop using mysql_* functions. They are no longer maintained and are officially deprecated. Learn about prepared statements instead, and consider using PDO. — Jay Blanchard
– Jay Blanchard, Commented May 6, 2015 at 15:05

Alex · Accepted Answer · 2015-05-06 18:53:48Z

1

You don't need loop by $i I guess

<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 Change your query to this one:

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

and change your php code then to:

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

UPDATE Update your code here:

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

and here:

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

       ?>

edited May 6, 2015 at 18:53

answered May 6, 2015 at 16:30

Alex

17.3k1 gold badge31 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

ladomiryak Over a year ago

CREATE TABLE IF NOT EXISTS schedule ( id int(5) NOT NULL,   date date NOT NULL,   time time(6) NOT NULL,   cabinet varchar(25) NOT NULL ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

Alex Over a year ago

really? but does it matter that I spend my time to debug your code? maybe you can try to help?

Alex Over a year ago

No I am not, I just do simple things step by step.

ladomiryak Over a year ago

Thank you!! How to make a first column with cabinets value?

ladomiryak Over a year ago

I need modify my mysql query. It should be possible to change date(use datepicker and Ajax) and change table rows.

|

Collectives™ on Stack Overflow

Queue Table using PHP/MySQL

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related