I've been making an events page for a community website I'm creating. It allows them to create new SQL entries for new events.
What I want is to only display dates ahead of the current date
Currently I have:
SELECT * FROM eventsDB ORDER BY eventdate ASC LIMIT 30";
But I suppose I have to add something like:
WHERE eventdate > NOW()
For the record the above doesnt work ↑
note: ($eventdate = date of event)
CRONTAB:
<?php
class simpleCMS {
var $host;
var $username;
var $password;
var $table;
public function display_public() {
$q = "SELECT *
FROM eventsDB
WHERE eventdate > UNIX_TIMESTAMP()
ORDER BY eventdate ASC
LIMIT 30";
$r = mysql_query($q);
if ( $r !== false && mysql_num_rows($r) > 0 ) {
while ( $a = mysql_fetch_assoc($r) ) {
$title = stripslashes($a['title']);
$author = stripslashes($a['author']);
$bodytext = stripslashes($a['bodytext']);
$eventdate = stripslashes($a['eventdate']);
$created = stripslashes($a['created']);
$entry_display .= <<<ENTRY_DISPLAY
<div class="post">
<table class="eventstable" cellspacing="0" cellpadding="0">
<tr>
<td><img src="media/icons/icon_calendar.gif"/> <b>$title </b></td>
<td class="right">$eventdate </td>
</tr>
<tr>
<td colspan="2" class="small">$bodytext <i>by $author</i></td>
</tr>
</table>
</div>
ENTRY_DISPLAY;
}
} else {
$entry_display = <<<ENTRY_DISPLAY
<h2> Your brand new Events Page! </h2>
<p>
No entries have been made yet.
Follow my instructions to make a new event!
</p>
ENTRY_DISPLAY;
}
$entry_display .= <<<ADMIN_OPTION
<p class="admin_link">
<a href="{$_SERVER['PHP_SELF']}?admin=97538642"></a>
</p>
ADMIN_OPTION;
return $entry_display;
}
public function display_admin() {
return <<<ADMIN_FORM
<form action="{$_SERVER['PHP_SELF']}" method="post">
<label for="title">Title:</label><br />
<input name="title" id="title" type="text" maxlength="150" />
<div class="clear"></div>
<label for="bodytext">Body Text:</label><br />
<textarea name="bodytext" id="bodytext"></textarea>
<div class="clear"></div>
<label for="author">Author:</label><br />
<input name="author" id="author" type="text" maxlength="100" />
<div class="clear"></div>
<label for="eventdate">Date (DD/MM/YY):</label><br />
<input name="eventdate" id="eventdate" type="text" maxlength="100" />
<div class="clear"></div>
<input type="submit" value="Create This Event!" />
</form>
<br />
<a href="../events.php">Back to Events</a>
ADMIN_FORM;
}
public function write($p) {
if ( $_POST['title'] )
$title = mysql_real_escape_string($_POST['title']);
if ( $_POST['bodytext'])
$bodytext = mysql_real_escape_string($_POST['bodytext']);
if ( $_POST['author'])
$author = mysql_real_escape_string($_POST['author']);
if ( $_POST['eventdate'])
$eventdate = mysql_real_escape_string($_POST['eventdate']);
if ( $title && $bodytext && $author ) {
$created = time();
$sql = "INSERT INTO eventsDB VALUES('$title','$bodytext','$created','$author','$eventdate')";
return mysql_query($sql);
} else {
return false;
}
}
public function connect() {
mysql_connect($this->host,$this->username,$this->password) or die("Could not connect. " . mysql_error());
mysql_select_db($this->table) or die("Could not select database. " . mysql_error());
return $this->buildDB();
}
private function buildDB() {
$sql = <<<MySQL_QUERY
CREATE TABLE IF NOT EXISTS eventsDB (
title VARCHAR(150),
bodytext TEXT,
created VARCHAR(100),
author VARCHAR(100),
eventdate VARCHAR(100),
)
MySQL_QUERY;
return mysql_query($sql);
}
}
?>
Based on our little discussion above, it seems like the easiest thing to do is make your eventdate field an INT data type. That way, when you take the user input as a string ("15/03/2011" for example), you can run that input through the PHP function strtotime() and get a UNIX timestamp from that.
<?php
$eventts = strtotime($_POST["eventdate"]);
$q = "UPDATE eventsDB SET eventdate = ".$eventts." WHERE keyfield = whatever";
$r = mysql_query($q);
?>
Note that strtotime() returns an INT (or boolean FALSE) so we're not setting you up for a SQL injection attack above. To query the database, you could then do this:
<?php
$q = "SELECT *
FROM eventsDB
WHERE eventdate > UNIX_TIMESTAMP()
ORDER BY eventdate ASC
LIMIT 30";
$r = mysql_query($q);
?>
To answer your question in the comments to this answer:
if ( $_POST['eventdate'])
$eventdate = mysql_real_escape_string($_POST['eventdate']);
Would be replaced with
if ( $_POST['eventdate'])
$eventdate = strtotime($_POST['eventdate']);
Note that for a production system, I really wouldn't recommend putting the admin code in the same page as the display code, and you should use the isset() function to check whether a $_POST array variable has been set (lest you cause all sorts of warnings or notices in your web server logs).
To display the date, you'd use the PHP date() function, specifically:
$entry_display = date("d/m/Y", $eventdate);
Where $eventdate is the UNIX timestamp you retrieved from the database.
The database is used or storing data. PHP (or whatever you chose to use) is used for interacting with the user.
If you want to store a date, you should use a date datatype.
How you want to display the date, is up to your PHP code.
Probably you want to show the date in different formats depending on the visitor, or possible in ISO format so all of the world can read it: YYYY-MM-DD
So, your query is pretty much correct:
SELECT *
FROM eventsDB
WHERE eventdate > NOW()
ORDER BY eventdate ASC
LIMIT 30
Note that using 'SELECT *' is not recommended for production code. Only select the columns you need - it can mean better use of indices and less data transferred between your database and application server.
You can store eventdate field as UNIX timestamp and then compare it easily in your queries.
SELECT ... WHERE `eventdate` > NOW() ...
and you can format it with php date function :
date('DD/MM/YY', $eventdate);
here is the manual : PHP Date Function