Neverside

Handling dates with php & mysql

okay so i've got this website where a user adds an event into db

event(
event_id int auto_increment
event_date text
event_title text
event_info mediumtext
);

i was wondering how you would be able to display the next coming event...something like

select from event where event_date>= current_date desc limit 1

Code:

SELECT * FROM event WHERE event_date >= NOW() ORDER BY event_date DESC LIMIT 1

I'm having a bit of a problem with this, it's only counting the first number to order them would i have to restructure my db table?

Yes, NOW() won't work on a text column.

You'll have to change it's type to datetime for it to work, but the way I would do it is change it to an int type. Use php to generate your timestamps. And then query with:

$ts = time();

$query = mysql_query("SELECT * FROM event WHERE event_date >= '$ts' ORDER BY event_date DESC LIMIT 1");

that should do it, hope that helps

You can also use the MySQL function UNIX_TIMESTAMP() to get the time as an int

Ah, I didn't read carefully enough. You should change it to a datetime field.

Use the mysql date and time field types or else you lose the ability to use many of mysql's built-in date and time functions.

___________________

Site | Work | School | Microterra Networks

can have the datetime stamp as a user inputted value?

Okay so it all works with adding the dates and everything but i'm having a problem editing the date

i formated it in the form field to go dd/mm/yy and then format back to yyyy-mm-dd

problem being it doesn't seem to update the date field

Code:

if($_POST['submit']) {

$event_info = nl2br($_POST['event_info']);
$event_date = $_POST['event_date'];
$event_date2 = date("Y-m-d", mktime($event_date));
$event_venue = $_POST['event_venue'];

mysql_query("UPDATE events SET
event_info = '$event_info' WHERE event_id = '$event_id '")
or die(mysql_error());
mysql_query("UPDATE events SET
event_date = '$event_date2' WHERE event_id = '$event_id'")
or die(mysql_error());
mysql_query("UPDATE events SET
event_venue = '$event_venue' WHERE event_id = '$event_id'")
or die(mysql_error());

no error is being displayed it just seems to not be updating

I am not sure if you are using multiple update statment for debugging uses, but if not, you can set all the columns in one update statement.

Also, try removing the single quotes from around your $event_id in your where clause; Your event_id field is an int, but you are comparing it to a string.

___________________

Site | Work | School | Microterra Networks