Save MySQL Result into php multi-dimensional array (timetable)

Question

My tables:

timetable
+----+---------+-------+--------+---------+---------+------+
| id | user_id | s_day | s_hour | subject | teacher | room |
+----+---------+-------+--------+---------+---------+------+
|  1 |       1 |     1 |      1 | MATH    | SM      | 101  |
|  2 |       1 |     1 |      2 | MATH    | SM      | 101  |
|  3 |       1 |     1 |      3 | MATH    | SM      | 101  |
|  4 |       1 |     1 |      4 | MATH    | SM      | 101  |
|  5 |       1 |     1 |      5 | MATH    | SM      | 101  |
|  6 |       1 |     1 |      6 | MATH    | SM      | 101  |
|  7 |       1 |     2 |      1 | MATH    | SM      | 101  |
|  8 |       1 |     2 |      2 | MATH    | SM      | 101  |
| .. |     ... |   ... |    ... |     ... |     ... |  ... |

users
+---------+----------+----
| user_id | username | ...
+---------+----------+----
|       1 | User1    | ...
+---------+----------+----

Now I want to output this timetable into a html-table with inputfields.

HTML table

<tr> //this would be the row for the first hour
    <td align="middle" class="td_contentbar" style="white-space:nowrap">1</td>
    <td align="middle" class="td_contentbar"><input class="inputfeld sp_input" id="subject_1_1"><input class="inputfeld sp_input" id="teacher_1_1"><input class="inputfeld sp_input" id="room_1_1"></td>
    <td align="middle" class="td_contentbar"><input class="inputfeld sp_input" id="subject_2_1"><input class="inputfeld sp_input" id="teacher_2_1"><input class="inputfeld sp_input" id="room_2_1"></td>
    <td align="middle" class="td_contentbar"><input class="inputfeld sp_input" id="subject_3_1"><input class="inputfeld sp_input" id="teacher_3_1"><input class="inputfeld sp_input" id="room_3_1"></td>
    <td align="middle" class="td_contentbar"><input class="inputfeld sp_input" id="subject_4_1"><input class="inputfeld sp_input" id="teacher_4_1"><input class="inputfeld sp_input" id="room_4_1"></td>
    <td align="middle" class="td_contentbar"><input class="inputfeld sp_input" id="subject_5_1"><input class="inputfeld sp_input" id="teacher_5_1"><input class="inputfeld sp_input" id="room_5_1"></td>
</tr>
<tr> //this would be the row for the 2nd hour
    <td align="middle" class="td_contentbar" style="white-space:nowrap">2</td>
    <td align="middle" class="td_contentbar"><input class="inputfeld sp_input" id="subject_1_2"><input class="inputfeld sp_input" id="teacher_1_2"><input class="inputfeld sp_input" id="room_1_2"></td>
    <td align="middle" class="td_contentbar"><input class="inputfeld sp_input" id="subject_2_2"><input class="inputfeld sp_input" id="teacher_2_2"><input class="inputfeld sp_input" id="room_2_2"></td>
    <td align="middle" class="td_contentbar"><input class="inputfeld sp_input" id="subject_3_2"><input class="inputfeld sp_input" id="teacher_3_2"><input class="inputfeld sp_input" id="room_3_2"></td>
    <td align="middle" class="td_contentbar"><input class="inputfeld sp_input" id="subject_4_2"><input class="inputfeld sp_input" id="teacher_4_2"><input class="inputfeld sp_input" id="room_4_2"></td>
    <td align="middle" class="td_contentbar"><input class="inputfeld sp_input" id="subject_5_2"><input class="inputfeld sp_input" id="teacher_5_2"><input class="inputfeld sp_input" id="room_5_2"></td>
</tr>

syntax for the id's of the input's:

subject_5_2
subject/teacher/room _ day (5 -> friday) _ hour (2 -> 2nd)

I thought about putting the complete mysql result in a multidimensional array like this:

$timetable[day][hour]

But how can I do this or is this method stupid? Or should I redesign the system completly?

SELECT s_day, s_hour, subject, teacher, room FROM timetable WHERE user_id = ? (I use prepared statements, so the ? will be the id of the logged in user — Bernd
– Bernd, Commented Aug 21, 2015 at 19:34

Ben in CA · Accepted Answer · 2021-02-28 14:33:06Z

3

I didn't actually test this but theoretically it should get you pretty close. It also would handle the case where a record doesn't exist, but you need to define your days / hours in the arrays in the top lines for what you're expecting to output.

Since each of your rows are output across in an hour by hour then day by day format, I would recommend that you order it as such:

//create a list of days / hours to check
$days = array(1,2,3,4,5);
$hours = array(1,2,3,4,5,6,7,8,9,10,11);


$stmt = $pdo->prepare("SELECT * 
    FROM timetable 
        INNER JOIN users ON timetable.user_id = users.user_id 
    WHERE users.username=:username
    ORDER BY timetable.s_hour, timetable.s_day");

// bind the parameters so we only show User1's results
$stmt->bindValue(":username", 'User1');
//run the query
$stmt->execute();

$record = array();
$record['s_hour'] = 0;
$record['s_day'] = 0;
foreach ($hours as $hour) {
    echo '<tr>';
    echo '<td align="middle" class="td_contentbar" style="white-space:nowrap">' . $hour . '</td>';
    foreach ($days as $day) {                
        //if the current slot we're adding data to comes after the record we're currently holding, grab the next record
        if(isset($record['s_hour']) && isset($record['s_day']) 
           && $hour > $record['s_hour'] ||
           ($day > $record['s_day'] && $hour <= $record['s_hour']) {
            if($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $record = $row;
            }
        } 

        //if this record, matches the hour/day slot we're on, output the appropriate data, else output an empty row
        echo '<td align="middle" class="td_contentbar">';
        if($hour == $record['s_hour'] && $day == $record['s_day']) {
            echo '<input class="inputfeld sp_input" id="subject_' . $day . '_' . $hour . '" value="' . $record['subject'] . '">';
            echo '<input class="inputfeld sp_input" id="teacher_' . $day . '_' . $hour . '" value="' . $record['teacher'] . '">';
            echo '<input class="inputfeld sp_input" id="room_' . $day . '_' . $hour . '" value="' . $record['room'] . '">';
        } else {
            echo '<input class="inputfeld sp_input" id="subject_' . $day . '_' . $hour . '">';
            echo '<input class="inputfeld sp_input" id="teacher_' . $day . '_' . $hour . '">';
            echo '<input class="inputfeld sp_input" id="room_' . $day . '_' . $hour . '">';
        }

        echo '</td>';
    }
    echo '</tr>';
}

edited Feb 28, 2021 at 14:33

Ben in CA

89916 silver badges26 bronze badges

answered Aug 21, 2015 at 18:08

Joe Meyer

4,4452 gold badges23 silver badges28 bronze badges

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

7 Comments

Maximus2012 Over a year ago

I think this is what the OP is looking for. Could you possibly use LEFT JOIN in the query to cover for NULL values case ?

Bernd Over a year ago

This works perfectly. But I have still one question: How can I add a simple Error-Handling to this?

Joe Meyer Over a year ago

@maximus if one used a left join it would defeat the purpose of querying by username.

Joe Meyer Over a year ago

@bernd I'm not sure what you mean by simple Error-Handling Could you please elaborate?

Bernd Over a year ago

What happens if there is an error? I always did something like if ($stmt->execute()) { //no error } else { //error } or isn't this necessary?

|

Collectives™ on Stack Overflow

Save MySQL Result into php multi-dimensional array (timetable)

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related