MYSQL and UNIX timestamps...

[eluser]mddd[/eluser]
You have to stop treating the date as a piece of text. Use the Date/time functions of Mysql. They are made for this.
http://dev.mysql.com/doc/mysql/en/date-a...tions.html

Examples:

Code:

// get all events in april 2010
SELECT * FROM event_list WHERE YEAR(date)=2010 AND MONTH(date)=4

// get all events on the first day of a month
SELECT * FROM event_list WHERE DAY(date)=1

// select all future events
SELECT * FROM event_list WHERE date>NOW()

To use functions in CodeIgniter, you should turn of the escaping of field names. Like so:

Code:

// this example does the same as the first query above here
$this->db->where('YEAR(date)', 2010, false);
$this->db->where('MONTH(date)', 4, false);
$query = $this->db->get('event_list');
// the 'false' turns off escaping. otherwise, CI will often try to treat a function name as a column name and that will not work.