check non validated in 49h span..

[techker]

Hey guys im making a script for a school and i need to check the database for non validated absences in the last 48hours?

 

 

would it be something like

 

SELECT *

  FROM YOUR_TABLE t

WHERE t.datetime_column < DATE_SUB(NOW(), INTERVAL 48 HOUR)

 

how do i add in colum validated if it says no?(yes when validated)

[awjudd]

We need more information and it all depends on your table structure.

 

Please do not try to be generic in your posts.  It just ends up biting you in the ass because nobody can help without any information.

 

~juddster

[techker]

Sorry..

 

So every time a student calls in sick they put it in the filling and it needs to be valid whiten the 48h fallowing the log..

 

So basicly i set up a script for al students.then i have a table called sick

 

ID-student id-date called-valid.

 

so buy default the valide is no when it is validated they press validated.so it goes to yes.

 

 

[awjudd]

Join your all students against your sick students?

 

Please provide table definitions.  It'll make it a lot clearer.

 

~juddster

[techker]

table is not done yet..figuring out what would be the best way to do this.

 

student DB

Sick DB

 

2 diffrent Db for shure.

 

 

[awjudd]

By two different DB for sure, do you mean two separate tables?

 

Something like this should do:

 

Student ( StudentID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), ... )
StudentSick ( StudentID INT, SickDay DATETIME, IsValidated BOOL )

 

SELECT s.FirstName, s.LastName, CASE ss.IsValidated WHEN 1 THEN 'Yes' ELSE 'No' END AS Validated
FROM Student s
JOIN StudentSick ss ON s.StudentID = ss.StudentID
WHERE ss.SickDay >= DATE_SUB(NOW(), INTERVAL 48 HOUR)

 

~juddster

[techker]

By two different DB for sure, do you mean two separate tables?

 

Something like this should do:

 

Student ( StudentID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), ... )
StudentSick ( StudentID INT, SickDay DATETIME, IsValidated BOOL )

 

SELECT s.FirstName, s.LastName, CASE ss.IsValidated WHEN 1 THEN 'Yes' ELSE 'No' END AS Validated
FROM Student s
JOIN StudentSick ss ON s.StudentID = ss.StudentID
WHERE ss.SickDay >= DATE_SUB(NOW(), INTERVAL 48 HOUR)

 

~juddster

 

ok that looks like what i need.

 

whats IsValidated BOOL

and the echo would be what?
$q3=SELECT s.FirstName, s.LastName, CASE ss.IsValidated WHEN 1 THEN 'Yes' ELSE 'No' END AS Validated
FROM Student s
JOIN StudentSick ss ON s.StudentID = ss.StudentID
WHERE ss.SickDay >= DATE_SUB(NOW(), INTERVAL 48 HOUR);
$res3 = mysql_query($q3);
$row3 = mysql_fetch_assoc($res3);

<?php echo $row3['Validated'] ?>

[awjudd]

It is a data type: http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

 

~juddster