CODEWITHSUNDEEP
phpmysql
David
David

Reputation: 1191

List rows in table with date information

I am in the beginning of building a simple absence script for teachers to administrate students.

I need to populate the same list of students in 5 tables. One table for each day of the current week (monday to friday).

I have the following code at the moment

List students SQL query:

SELECT s.student_id, s.student_firstname, s.student_lastname, 
 a.student_absence_startdate, a.student_absence_enddate, a.student_absence_type
FROM students s 
LEFT JOIN studentabsence a ON a.student_id = s.student_id
WHERE a.student_absence_startdate 
 IS NULL OR 
  '2012-06-20' 
   BETWEEN 
a.student_absence_startdate AND a.student_absence_enddate

This query selects all students and joins another table wich is a linking table that holds the absence information for students.

I the populate the code 5 times like this:

<?php
$data = array();
    while ($row = mysql_fetch_array($list_students)) {
    $data[] = $row;
    }
?>

<table>
  <?php
  foreach($data as $row){
    $class = "";
    if ($row['student_absence_type'] == 2) {$class = " style='background:#f00;'";}
    else if ($row['student_absence_type'] == 3) {$class = " style='background:#8A2BE2;'";}

        echo "<tr><td$class>";
        echo "<a class='ajaxlink' href='#' rel='pages/ajaxAbsence.php?student_id=".$row['student_id']."'>".$row['student_firstname']." ".$row['student_lastname']."</a>";
        echo "</td></tr>\n";
   }
   ?>
</table> <!-- Repeat -->

My question is how I should manage the date functions.

As you can see in my SQL query I have hardcoded the dates. I need it to automatically select the date of wich table the students are listed.

for example:

table > Monday (2012-07-23)
    the user John Doe has absence information in this span, based on the above query. Let's change the background of <td>
table > Tuesday (2012-07-24)
    the user John Doe has no absence information in this span. Just list him.
etc...

I am not expecting you to write my code. I am just curious on how to think. I am fairly new to programming.

Upvotes: 2

Views: 181

Answers (1)

Jo&#227;o Dias
Jo&#227;o Dias

Reputation: 1108

The most flagrant error I find in your thoughts is that you don't need those five tables. You'll end up repeating and messing data.

You need different tables, from what I've read.

Students

ID, Name, etc..

Classes

class_id, name,

Teachers

Id_teacher, name, etc

TeachersInClass (so you can now which teachers gave a specific class)

id, id_teacher, id_class

Absences

id, id_student, id_class, date

This leaves you a more robust database, and you can play arroud with multiple associations like, which teachers had the most absences in a year... etc etc.

EDIT: Forgot to answer your "real" question.

You can use the PHP DateTime class to manipulate your dates. It's easy to use, and the php.net has a lot of good examples to give you a boost start

Upvotes: 1

Related Questions