Page 1 of 1

Getting an entry from SQL past a date

Posted: Thu Dec 14, 2006 9:19 pm
by ayfine
I wrote a homework system to keep track of my homework, but the problem is, when I enter something due in 2007 (just as a test) it doesn't show up, because I made it not show homework that has been due. Any ideas? Here's the sql code that I use to grab homework.

Code: Select all

function view_due($uid, $year, $month, $day) { // same as view but only views assignments that haven't been due already
echo "<a href='hw.php?type=all'>view all homework, even those already due</a><br>";
               $sql = mysql_query("SELECT * from homework where uid = '$uid' AND due_y >= $year AND due_m >= $month AND due_d >= $day ORDER by due_d ASC") or die(mysql_error());
                while($a = mysql_fetch_array($sql)) {
                        echo "<div style='border-bottom:2px solid #444'><b>{$a['title']}</b> for <u>{$a['class']}</u> due on {$a['due_m']}/{$a['due_d']}/{$a['due_y']}<br><font color='#eeeeee'>Added: {$a['as_m']}/{$a['as_d']}/{$a['as_y']}</font></div>";
                        echo "<blockquote>Description:<br><i>{$a['desc']}</i><br>&nbsp;&nbsp;<a href='delete.php?id={$a['id']}'>delete this</a></blockquote>";
                }
        }
and to open it I use this

Code: Select all

<?php
$month = date('n');
$day = date('j');
$year = date('y');
$hw->view_due($_SESSION['uid'], $year, $month, $day);
?>
Any idea to why assignments in '07 don't show?

Posted: Thu Dec 14, 2006 9:35 pm
by Burrito
what data types are your columns?

it would help to see some sample data too.

Posted: Thu Dec 14, 2006 11:43 pm
by Kieran Huggins
I would use a UNIX timestamp for the date in PHP, then dates are nothing more than 10 digit numbers. You will find them very easy to work with in ranges, as they're just simple 10 digit numbers.

If you want to do any fancy sorting by month, week number, phase of the moon, etc... you would likely find it easiest to store that information alongside the timestamp in the database.

Your columns could look like this:

assignment (string), class (string), dueDate (timestamp), dueMonth (string), dueWeekday (string)

That way you could get all the due homework between this week's "Lost" and next week's "House" like this:

Code: Select all

$this_weeks_lost = strtotime('this Wednesday, 9 pm');
$next_weeks_house = strtotime('next Tuesday, 9 pm');
$result = MySQL_query("SELECT * FROM homework WHERE dueDate>$this_weeks_lost AND dueDate<$next_weeks_house");
...or you could find all the homework due on a Thursday like this:

Code: Select all

$result = MySQL_query("SELECT * FROM homework WHERE dueWeekday='Thursday'");
I know the information is technically duplicated (or more) in the database, but what's a few bytes when it makes your code cleaner and your execution time faster?

Cheers,
Kieran

Posted: Sun Dec 17, 2006 1:05 am
by ayfine
The way I organized the tables, I somewhat regret now but it's what I was thinking when I wrote it originally and I don't really feel like changing it for now. Here's the fields in the table
id
uid (id of the user who posted it)
title
class
due_m (the month in which its due)
due_d (the day its due)
due_y (the year in which its due)
as_m (the month it was assigned)
as_d (day it was assigned)
as_y (year it was assigned)
desc (description)

hope that helps

Posted: Sun Dec 17, 2006 4:05 am
by Kieran Huggins
ayfine wrote:...and I don't really feel like changing it for now....
I'm not exactly sure what you're asking for... you seem to have reached an impasse.

Cheers,
Kieran

Posted: Sun Dec 17, 2006 7:22 am
by timvw
Nothing keeps you from building your own DATE(TIME) with the data you have in SQL... For MySQL that is something as:

Code: Select all

SELECT STR_TO_DATE(CONCAT(due_d, '/', due_m, '/', due_y), '%d/%m/%Y') AS realdate FROM mytable
And now you can use all the nice date and time functions mysql offers you... (And yes, if you were using a unixtimestamp you would still have to perform a conversion first before you can use those functions).