date() mysql php table join

panreach

Is there anyway to turn the date() into a variable and pass it to the mysql_query(Select....)? I have my calendar db filled with music events. I want to keep old events present in the db but not on the webpage. Is there anyway for the script to load on the page only showing future events? Can anyone help me with this? Thank You in advance for any possible clue/s!

<ul type=\"circle\">
<?

date(\"D. m/d/Y\");

@ $db = mysql_pconnect(\"localhost\", \"user\", \"pass\");
if (!db)
{
echo \"Unable to connect to that darn server. Go back and try again.\";
exit;
}
mysql_select_db(\"calendar\", $db);

$result = mysql_query(\"select venue.name, venue.street, venue.City, venue.directions, event.date, event.start, event.sm, event.end, event.em, DATE_FORMAT(event.date,\'%a. %m/%d/%y\') AS fmt_date from venue right join event on event.venueid = venue.venueid order by event.date\");

$num_results = mysql_num_rows($result);

for ($i=0; $i <$num_results; $i++)
{
$row = mysql_fetch_object($result);
echo \"<td>\";
echo stripslashes($row->fmt_date).\"\t\";
echo stripslashes($row->start);
echo stripslashes($row->sm).\"-\";
echo stripslashes($row->end);
echo stripslashes($row->em).\"\t\";
echo stripslashes($row->name).\" - \";
echo stripslashes($row->street).\" \t \";
echo stripslashes($row->City).\" - \";
echo stripslashes($row->directions).\"\t<br><br>\";
}
?>
</ul>

van__

I answered a similar one already today, but you also want a bit more info on playing with dates in SQL databases as well... so here you go.

//This will insert the current date and or time properly into a datetime time and date field.
$sql = "INSERT INTO tableName (dateColumn) VALUES(NOW())";

//we check for future dates by comparing millisecs....
$sql = "SELECT * FROM tableName WHERE UNIX_TIMESTAMP(dateColumn) > UNIX_TIMESTAMP(NOW())";

panreach

OK you confused me. I have a form that enters the dates for events. I've read something about Date Calculations. Is that what I'm supposed to do if I enter a date with a form? ie mktime() ... floor()... something along those lines?

van__

Sorry, forget about that first one, that just enters the date as today.

Use the second one to select all rows with a date/time that is greater than now.

It does the calculations for you.
the UNIX_TIMESTAMP() SQL function will convert the timestamp given to millisecs and then the comparison operator determines the rules for a positive match.

van__

Here, to make it simpler, I rewrote your query...

$stmt = "SELECT venue.name, venue.street, venue.City, venue.directions, ".
"event.date, event.start, event.sm, event.end, event.em, " .
"DATE_FORMAT(event.date,'%a. %m/%d/%y') " .
"AS fmt_date FROM venue,event WHERE event.venueid = venue.venueid ".
"AND (UNIX_TIMESTAMP(event.date)>UNIX_TIMESTAMP(NOW())) ".
"ORDER BY UNIX_TIMESTAMP(event.date)";

$result = mysql_query($stmt);

panreach

You rule!!! I started playing with it for hours trying to get to read today as well. I think I finally figured that out. Here's what I came up with for going two day's back to add to your brilliant mastery.

$stmt = "SELECT venue.name, venue.street, venue.City, venue.directions, ".
"event.date, event.start, event.sm, event.end, event.em,DATE_FORMAT(event.date,'%a. %m/%d/%y') " .
"AS fmt_date FROM venue,event WHERE event.venueid = venue.venueid ".
"AND (UNIX_TIMESTAMP(event.date)+486060 > UNIX_TIMESTAMP(NOW()))" .
"ORDER BY UNIX_TIMESTAMP(event.date)";

$result = mysql_query($stmt);

I've only been learning this stuff for a couple of weeks. Please tell me that it gets easier? Thanks a lot for your help. Now I want to figure out how to put heading for each month ie April 2002, May 2002, August 2002; but only where there is data for that month. I think that envolves more complicated arrays. The finale output I'm looking for is whats at this static page: http://www.afinkemachine.com/calendar/

Thanks for hookin` a newbie up!!!

van__

Glad to see you liked it.
You can have MySQL do some really nice stuff for you with it's SQL statement functions.
Read up on them over at http://www.mysql.org

And actually, it'll get harder before it get's easier, but that's mainly due to the fact that once you pick it up, you start getting more complex with it, but then you'll peak and think it's way too easy 🙂