A PHP Calendar Question

[goatshideout]

I have a PHP/mysql calendar with individual table columns for year, month, date and description and so on. Can somebody help me make an SQL query that will find the closest future event to whatever day it happens to be? Would love some help. Take a look at the site im talking about if necessary (www.sheptonband.org) Thanks.

-Stuart Montgomery

[Jubba]

well I could, but i don't know how your database is set up. could you give me a clue on how you have that set?

[goatshideout]

Heres the database layout. For lack of a better way of showing you heres a couple screen caps (below).

-Stuart

Screenshot (http://www.goatshideout.com/ebay/db_screen.jpg)

[λ]

SELECT * FROM tablename ORDER BY YEAR, MONTH, DAY

that should work :)

EDIT: oh wait, that just puts them in order... you'd probably have to find out the year and month of the date you're specifying:



//mysql connection above:
$month = $_POST['month'];
$year = $_POST['year'];
$nearResults = mysql_query("SELECT * FROM tablename WHERE MONTH = '$month' AND YEAR = '$year' ORDER BY YEAR,MONTH,DAY", $dbConn);


that should work if you only want to select things that are in the same month as the date typed in.

[goatshideout]

Well i know how to do a query on the exact day specified but i am trying to make a query that will grab the closest event to whatever day the script is executed on, not just for any day the user specifies. Anybody know how to do this?

[Jubba]

something like this might work...



$thisDay = date("d");
$thisMonth = date("m");
$thisYear = date("y");

$query = "SELECT * FROM tablename WHERE year >= '$thisYear' AND month >= '$thisMonth' AND day >= '$thisDay' ORDER BY year, month, day LIMIT 1";
$result = mysql_query($query);