How do I make this scheduler in PHP and MySQL?

This is my scheduler

And I want to this in this way

The X button means Delete and it is existing in the database and the CHECK button is to add data in the database

This is my code in the first picture

<table class="table table-bordered table-hoverable table-striped" id="tblClassScheduler">
                    <thead>
                       <tr>
                        <?php
                            $days=array("Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday");
                            for ($i=0;$i<COUNT($days);$i++) { 
                                echo "<th>".$days[$i]."</th>";
                            }
                         ?>
                       </tr>
                    </thead>
                    <tbody>
                        <?php
                            $timer_schedule=create_time_range("08:00","22:00","30 mins","12");
                            foreach ($timer_schedule as $value) {
                                echo "<tr>";
                                for ($a=0;$a<COUNT($days);$a++) { 
                                    $get_class_schedule=getData("SELECT * FROM acadsoc_class_schedule WHERE username=?",array($_SESSION['username']));
                                ?>
                                <td>
                                	<form method="POST">
                                        <input type="text" name="class_time" value="<?php echo $value; ?>" hidden><br>
                                        <label><?php echo $value; ?></label>
                                        <input type="text" name="class_day" value="<?php echo $days[$a] ?>" hidden>
                                        <button class="btn btn-sm btn-primary" style='padding: 1px 6px;'  name="save_multiple_schedule" data-toggle="tooltip" data-placement="top" data-original-title="Add Schedule" title="Add Schedule"><span class="fas fa-check"></span></button>
                                        <button class="btn btn-sm btn-danger" style='padding: 1px 6px;'  name="delete_schedule" ata-toggle="tooltip" data-placement="top" data-original-title="Delete Schedule" title="Delete Schedule"><span class="fas fa-close"></span></button>
                                    </form>
                                </td>
                                <?php
                            	}
                                echo "</tr>";
                            }
                        ?>
                    </tbody>
                </table>

OK, how far have you got with it?

I’m guessing the problem here is that you have both a tick and a cross in every cell, where you actually want a tick or a cross, depending on whether the timeslot is selected. Is that it?
I think you need to check the data from your database, and have a condition to display accordingly.
Since I dont know your table/database, I cant give specific advice on exactly how to do that.
But I do have some observations on your existing code, in how things could improve, and possibly make your task simpler.

First, try to separate your logic from your HTML more, things such as setting arrays for days and times can be done previously to creating a table.

for ($i=0;$i<COUNT($days);$i++) { 

What’s this? This is a typical use case for foreach, the for is an overcomplication here.

$get_class_schedule=getData("SELECT * FROM acadsoc_class_schedule WHERE username=?",array($_SESSION['username']));

I’m guessing that the getData() function performs a prepare, then an execute. Such functions can be a convienient way to use prepared statements, I use one myself in an extended PDO class, and it’s nice for one-off queries and saves lines of code.
But, in the context of a loop, or in this case a loop within a loop, it is quite inneffcient. One of the great things about prepared statements is, you can make the database more efficient by preparing a query just once, then executing several times with different data for the placeholder.
So, prepare only once, outside of the loop. Then execute that same statement multiple times, within the loop.
However, in this case, the data (username column) does not change within the loop. So you only need execute the query once, not for every cell in the table.
Again, this is a case for removing the logic from the HTML, to some place previous. You both clean up the code, and save resources by only calling the database once, as required.

Now looking at your HTML, you seem to have multiple forms within your table, one for every cell. That’s a lot of forms, with nothing to identify them apart.
What I believe you should have is one form, then each table cell has a checkbox, instead of two buttons, where the values make up an array. There should be no need for the hidden inputs and buttons here.
The checkboxes should be checked or not, according to selection.
The bit that puzzles me is, you seem to have no means get data for the check being on or off. Where is this data?

This is the code for getData() function

function getData($queryString , $whereValues = "" , $getSingleData = False) {
    global $pdo;
    $stmt = $pdo->prepare($queryString);
    if($whereValues == "") {
        $rowData = $stmt->execute();
        }
    else {
        $rowData = $stmt->execute($whereValues);
        }
        if($getSingleData == True) {
          $rows = $stmt->fetch();
        }
        else {
          $rows = $stmt->fetchAll();
        }
    return $rows;
}

That’s the kind of thing I thought it would be, and that’s OK for one-off queries. But as I say, if it’s in a loop, prepare once outside the loop, execute in the loop (ony if the data for the placeholder chenges).

But there are other questions to aswer before we decide how to run the query.

Your user interface is not very intuitive. You are (want) displaying a red x for things that are currently selected. For a schedule, where you are picking from the available choices, the display should indicate, in a positive way, the things that are selected, and provide a way of toggling the selected state for each item. This is where a standard checkbox comes in handy. It’s a single display item that is either checked or it’s not, to indicate the current state, and for an ‘edit’ page, can be clicked on to change the selected state.

The most straight-forward way of using the existing selected data when you produce the output is to run one query that gets the data that you need, then when you fetch the data index/pivot it using a composite index consisting of the day of week number and the time value, separated by a unique delimiter, such as a pipe | character. When you are looping to produce the output, you would build the current index being displayed, using the same format as for the index for the fetched data, then test if there’s an element (isset) in the array of fetched data. If there is, you would output whatever you want to indicate the selected state. If not, output whatever you want to indicate the non-selected state.

Any data related to a user, should use the user’s id, not the username, to relate it back to the user it belongs to. This will use the least amount of storage space, result in the fastest queries, and allow the username to be edited without needing to update all the related data.

1 Like