Getting an entry from SQL past a date

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ayfine
Forum Newbie
Posts: 22
Joined: Mon Nov 27, 2006 4:52 pm

Getting an entry from SQL past a date

Post 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?
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

what data types are your columns?

it would help to see some sample data too.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post 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
ayfine
Forum Newbie
Posts: 22
Joined: Mon Nov 27, 2006 4:52 pm

Post 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
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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).
Post Reply