Displaying a Weekly Timetable of Workers in PHP and MySQL

In this article, we will explore how to create a weekly timetable of workers using PHP and MySQL. We will cover the following topics:

How to create a table with a dynamic number of rows
How to populate the table with data from a database
How to display a week’s worth of data in a horizontal format

Introduction

In this article, we will be using PHP and MySQL to create a weekly timetable of workers. The goal is to display each worker’s schedule for the upcoming week.

We have been provided with a basic structure for our code, but it is incomplete and needs modification to meet our requirements.

Creating the Table Structure

First, let’s create the table structure in our database using MySQL.

CREATE TABLE timesheet (
    idtime INT AUTO_INCREMENT PRIMARY KEY,
    namekanji VARCHAR(255),
    start_time TIME,
    end_time TIME
);

In this example, we have created a table called timesheet with three columns: idtime, namekanji, and start_time and end_time.

Modifying the PHP Code

Next, let’s modify our PHP code to fetch data from the database.

<?php
// Include config file
require_once "config.php";

// SQL for to fetch data from database
$time = mysqli_query($link, "SELECT * FROM timesheet WHERE date_time &lt;= CURDATE() + 6");

mysqli_close($link);
?>

In this example, we are using the mysqli_query() function to execute a SQL query that selects all rows from the timesheet table where the date_time column is less than or equal to one week ago.

Fetching and Displaying Data

To fetch and display data in our PHP code, we can use a while loop to iterate over each row returned by the database query.

<table class="table bordered">
    <thead>
        <tr>
            <th>Host</th>
            <th></th>
            <th></th>
            <th></th>
            <th></th>
            <th></th>
        </tr>
    </thead>
    <tbody>
        <?php while($empTime = mysqli_fetch_assoc($time)) { ?>
        <tr>
            <td><?php echo $empTime['namekanji']; ?></td>
            <td><?php echo date('l', strtotime($empTime['start_time'])); ?> - <?php echo date('h:i A', strtotime($empTime['end_time'])); ?></td>
        </tr>
        <?php } ?>
    </tbody>
</table>

In this example, we are using the mysqli_fetch_assoc() function to fetch each row from the database as an associative array. We then use a while loop to iterate over each row and display its values in our table.

Displaying Data Horizontally

To display data horizontally instead of vertically, we can modify our PHP code to add a CSS class to each row.

<table class="table bordered">
    <thead>
        <tr>
            <th>Host</th>
            <th></th>
            <th></th>
            <th></th>
            <th></th>
            <th></th>
        </tr>
    </thead>
    <tbody>
        <?php while($empTime = mysqli_fetch_assoc($time)) { ?>
        <tr class="row">
            <td><?php echo $empTime['namekanji']; ?></td>
            <td><?php echo date('l', strtotime($empTime['start_time'])); ?> - <?php echo date('h:i A', strtotime($empTime['end_time'])); ?></td>
        </tr>
        <?php } ?>
    </tbody>
</table>

<style>
    .row {
        display: inline-block;
    }
</style>

In this example, we have added a CSS class called row to each row in our table. We then use the display property of CSS to set each row’s width to inline-block, which allows us to display data horizontally.

Displaying Data for Each Day of the Week

To display data for each day of the week, we can modify our PHP code to add a new column for each day of the week.

<table class="table bordered">
    <thead>
        <tr>
            <th>Host</th>
            <th></th>
            <th></th>
            <th></th>
            <th></th>
            <th></th>
            <th></th>
        </tr>
    </thead>
    <tbody>
        <?php while($empTime = mysqli_fetch_assoc($time)) { ?>
        <tr class="row">
            <td><?php echo $empTime['namekanji']; ?></td>
            <td><?php echo date('l', strtotime($empTime['start_time'])); ?> - <?php echo date('h:i A', strtotime($empTime['end_time'])); ?></td>
            <td></td>
            <td></td>
            <td></td>
            <td></td>
            <td></td>
        </tr>
        <?php } ?>
    </tbody>
</table>

<style>
    .row {
        display: inline-block;
    }
</style>

In this example, we have added a new column for each day of the week. We can then use CSS to align these columns horizontally.

th {
    text-align: center;
}

td {
    width: 33.3%;
}

This code sets all table headers (th) to text-align centered and sets the width of all table data cells (td) to be one-third of the total width, which aligns them horizontally.

Conclusion

In this article, we have explored how to create a weekly timetable of workers using PHP and MySQL. We have covered the following topics:

Creating a table structure in our database
Modifying our PHP code to fetch data from the database
Fetching and displaying data in our PHP code
Displaying data horizontally instead of vertically
Displaying data for each day of the week

We have also covered how to add CSS classes to modify the appearance of our table. With this knowledge, you can create your own weekly timetable of workers using PHP and MySQL.

Example Use Cases:

Creating a schedule for employees or hosts
Tracking shifts or bookings in a business
Displaying data for different days of the week

Common Pitfalls:

Not properly closing database connections to avoid connection leaks.
Not checking for SQL errors or exceptions that may occur during database queries.

Best Practices:

Always use prepared statements when executing SQL queries to prevent SQL injection attacks.
Use try-catch blocks to handle any errors that may occur during database operations.

Last modified on 2024-01-24