Querying using a date in PHP and MySQL

Question

I am working on a script that will display the staff schedule using PHP and MySQL. I'm using php 5.3.13 and MySQL 5.5.24

What I am trying to do is to populate a table that will have the date and either the staff start time if the status is ACTV, or the status if the staff is either off or on leave.

The code is as follows:

<body>
<center>
<?php
include "../../scripts/php/dbcon/dbcon.php";
$schedule_table = "cc_schedule";

$date_query="SELECT DISTINCT DATE(schedule_date) FROM `$schedule_table`";
$date_result= mysql_query($date_query);
$datenum = mysql_numrows($date_result);

?>

<!-- Table Head -->

<center>
<table cellpadding="0" cellspacing="0">
    <tr>
        <th><center>No.</center></th>
        <th><center>Name</center></th>
        <th><center>Employee ID</center></th>
        <?php
        $d =0;
        while ($d < $datenum){

        $dt = mysql_result($date_result,$d,"DATE(schedule_date)");
        $date = date_create($dt);
        $dd = $date;

        echo "<th><center>". date_format($dd, 'd-M-Y')."</th><center>";
        $d++;
        }
?>  
    </tr>

<!-- Table Body -->


<?php

$names_query="SELECT StaffName, ID 
                from users 
                WHERE groupname = 'call center'";

$name_result = mysql_query($names_query);
$namenum = mysql_numrows($name_result);

$schedule_query="SELECT * 
                FROM `$schedule_table` 
                ORDER BY `schedule_date` ASC,`sstatus` ASC,`start_time` ASC,`employee_id` ASC";

$schedule_result=mysql_query($schedule_query);
$schedule_num = mysql_numrows($schedule_result);

$n = 1;
while ($n < $namenum){
    ?>
    <tr>
    <?php
    echo"<td><center>".($n)."</td></center>";
    echo"<td><center>".mysql_result($name_result,$n,"StaffName")."</td></center>";
    echo"<td><center>".mysql_result($name_result,$n,"ID")."</td></center>";

    $d=1;
    {
        $emp_id = mysql_result($name_result,$n,"ID");
        $dt = mysql_result($date_result,$d,"DATE(schedule_date)");
        $date = date_create($dt);
        $dd = date_format($date, 'Y-m-d');

        echo $emp_id;
        echo DATE($dd);

        while ($d < $datenum) {
            $schedule_query ="SELECT *  
                            from $schedule_table 
                            WHERE `employee_id` = $emp_id AND `schedule_date`='$dd'";
            $schedule_result = mysql_query($schedule_query);
            $schedule = mysql_result($schedule_result,0,"start_time");
            $status = mysql_result($schedule_result,0,"sstatus");



                if ($status =="ACTV"){
                '<td class="'.$status.'"><center>'.$schedule.'</td></center>';
                }
                else {
                echo '<td class="'.$status.'"><center>'.$status."</td></center>";
                }
                }

    $d++;       
    }

    ?>
    </tr>
<?php
$n++;
}
?>
</table>
</center>
</body>

The code does not return anything. It returns different errors everytime it runs. I suspect that something is wrong with the query. I just can't figure it out..

Any help is appreciated.

Thank You.

Mohamed.

[Please, don't use `mysql_*` functions in new code](http://stackoverflow.com/q/12859942). They are no longer maintained and the deprecation process has begun, see the [red box](http://php.net/mysql-connect). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli); [this article](http://php.net/mysqlinfo.api.choosing) will help you decide which. If you choose PDO, [here is a good tutorial](http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers). — Geek Num 88, Nov 12 '12 at 22:10
have you already debugged the output of $date_result and the other statements - e.g with var_dump - to see what you get from the database? If not, try to fix the SQL statement. Write it in plain SQL e.g. in phpmyadmin or on the mysql console ... And I strongly encourage you to separate your business and view code ... — awenkhh, Nov 12 '12 at 22:15

score 0 · Accepted Answer · answered Nov 14 '12 at 16:38

Jeremy Smyth , Geek Num 88, and awenkhh

Thanks for your comments.

I switched to PDO and I have obtained the result I was looking for.

The new code that fixes the issue above is:

<body>
<center>
<?php
include "../../scripts/php/dbcon/dbcon.php";
$schedule_table = "cc_schedule";
$dates= $db->query("SELECT DISTINCT DATE(schedule_date) FROM `$schedule_table`");
$dates_num = $dates->rowCount();
$ids = $db->query("SELECT DISTINCT employee_id FROM `$schedule_table` ORDER BY `employee_id`");
$staffcount = $ids->rowCount();
$records = $db->query("SELECT * FROM `$schedule_table`");
$record_num = $records->rowCount();
?>
<center>
<!--- Table Head -->

    <table cellpadding="0" cellspacing="0">
        <tr>
            <th><center>No.</center></th>
            <th><center>Employee Name</center></th>
            <th><center>Employee ID</center></th>
            <?php
                while($row = $dates->fetch()) {
            {
                $d =  $row['DATE(schedule_date)'];
            }
            ?>
                <th><center><?php echo date_format(date_create($d), 'd-M-Y'); ?></center></th>
            <?php
            }
            ?>
        </tr>

    <?php
        $i=1;
        while($row = $ids->fetch()){

            $employee_id =  $row['employee_id'];

                    $equery = $db->query("SELECT StaffName FROM users where ID=$employee_id");
                    while($row = $equery->fetch(PDO::FETCH_ASSOC)) {
                    $empname =  $row['StaffName'];
                    }

    ?>
<!--- Table Body --->

                    <tr class="class<?php echo ($i%2); ?>">
                        <td  width="20"><font face="Arial, Helvetica, sans-serif" size="1"><b><?php echo $i ; ?></b></font></td>
                        <td  width="200"><font face="Arial, Helvetica, sans-serif" size="1"><?php echo $empname ; ?></font></td>
                        <td  width="80"><font face="Arial, Helvetica, sans-serif" size="1"><center><?php echo $employee_id; ?></center></font></td>

                        <?PHP
                        $sschedule = $db-> query("SELECT start_time, sstatus FROM $schedule_table WHERE employee_id =$employee_id");

                        while($row = $sschedule->fetch()) {
                        {
                            $sstatus =  $row['sstatus'];
                            $start_time =  $row['start_time'];

                            if ($sstatus=="ACTV"){
                            $staffSchdule = date_format(date_create($start_time), 'h:i');
                            }
                            else{
                            $staffSchdule = $sstatus;
                            }

                        }
                            ?>
                            <td  width="20" class="<?php echo $staffSchdule; ?>"><font face="Arial, Helvetica, sans-serif" size="1"><center><?php echo $staffSchdule; ?></center></font></td>
                        <?php


                    }

                $i++;
                }
                    ?> 
                </tr>
<td colspan="<?php echo $dates_num + 3 ?>"><font face="Arial, Helvetica, sans-serif" size="1"><b><i>Found a total of <?php echo $record_num; ?> records matching your criteria.</i></b></font></td>
</tr>
    </table>
</center>
</body>

And the end result is a proper time table: enter image description here

Thank You.

Querying using a date in PHP and MySQL

1 Answers1