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

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?

Answers


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>';
}

Need Your Help

powershell filter csv

powershell csv

Looking for help on best practice as I am Powershell newbie,

Change 'edit account' URL in Drupal

drupal user drupal-7

When going to edit account or edit profile in Drupal 7, the URL looks something like http://localhost/user/123/edit where 123 is the user id. Because of this, anyone can see how many users the sit...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.