active record for [where date >= now()]

[eluser]RaZoR LeGaCy[/eluser]
How can I set and active record for where date >= now()

I tried including helper date
I tried this

Code:

$this->db->select('title, dvd, date');
$this->db->from('hh_events');
$this->db->limit ('7');
$this->db->like ("(dvd LIKE '1' OR theater LIKE '1')");

$this->db->where ("(date >= now())");

$data['events'] = $this->db->get();

Everything works without the date part but I need the date part for my events system

[eluser]Michael Wales[/eluser]
The way you are doing it you are using MySQL's now() function - which returns a result formatted like '2006-04-12 13:47:36'. You can read more about the now() function on MySQL's Documentation.

If you'd like to use CI's now() function - which would take into account GMT/local settings - and return a Unix timestamp use the following code:

Code:

$this->load->helper('date');
$this->db->select('title, dvd, date');
$this->db->from('hh_events');
$this->db->limit('7');
$this->db->like("(dvd LIKE '1' OR theater LIKE '1')");
$this->db->where("(date >= " . now() . ")");
$data['events'] = $this->db->get();

[eluser]RaZoR LeGaCy[/eluser]
it returned 0 entries when there are in fact 17 entries

Any other way??

[eluser]Michael Wales[/eluser]
Is your date field a Unix timestamp? Are you sure >= is the direction your want to go (where date is in the future of now())?

[eluser]RaZoR LeGaCy[/eluser]
I used date format in the DB date field


If you have a better method please tell, but I need to keep the date format but if you know of a better query please do tell

[eluser]Michael Wales[/eluser]
Personally, I like to store everything as a Unix timestamp - that way I can utilize CI's now() function. If your database is stored using MySQL's date field type - then go with your original code... it should work.

Are you sure >= is the right operator? Have you tried <= and received the correct results - maybe your logic is incorrect.

Maybe switching the date field to a datetime type - you'd think MySQL is smart enough to run a comparison operator on a date and a datetime field and return the correct results - but who knows...

[eluser]smith[/eluser]
[quote author="RaZoR LeGaCy" date="1186040302"]it returned 0 entries when there are in fact 17 entries

Any other way??[/quote]

As walesmd already told you: you will always have 0 records for that query. You are looking for records that have date in the future?

I don't see how mysql can possibly return even one record with date field set in the future. Please explain which records do you want to pull from the database. If you want one hour old records you can use: date >= now()-3600 etc.

[eluser]RaZoR LeGaCy[/eluser]
Okay I'll explain better

This query is needed of the section in http://www.hellhorror.com where it says "Upcoming Horror Movies" in the upper right hand side.

So I had in my hand written code and it worked

Code:

mysql_select_db($database_HH, $HH);
$query_events = "SELECT * FROM hh_events WHERE  dvd = 1 AND `date` >= NOW() OR theater = 1 AND `date` >= NOW() ORDER BY `date` ASC LIMIT 0,6";
$events = mysql_query($query_events, $HH) or die(mysql_error());
$totalRows_events = mysql_num_rows($events);

But

I can't use that code in CI for some reason

MY DB structure Dump

Code:

--
-- Table structure for table `hh_events`
--

CREATE TABLE `hh_events` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(128) collate utf8_unicode_ci NOT NULL default '',
  `text` text collate utf8_unicode_ci NOT NULL,
  `date` date NOT NULL default '0000-00-00',
  `dvd` enum('1','0') collate utf8_unicode_ci NOT NULL default '0',
  `theater` enum('1','0') collate utf8_unicode_ci NOT NULL default '0',
  `game` enum('1','0') collate utf8_unicode_ci NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='release_dates' AUTO_INCREMENT=62 ;

--

Please someone try to replicate and see what may be a hack of some sort

Thanks ALL

[eluser]RaZoR LeGaCy[/eluser]
bump this

[eluser]Scott - Beyond Coding[/eluser]
[quote author="RaZoR LeGaCy" date="1186269580"]

Code:

mysql_select_db($database_HH, $HH);
$query_events = "SELECT * FROM hh_events WHERE  dvd = 1 AND `date` >= NOW() OR theater = 1 AND `date` >= NOW() ORDER BY `date` ASC LIMIT 0,6";
$events = mysql_query($query_events, $HH) or die(mysql_error());
$totalRows_events = mysql_num_rows($events);

[/quote]

If you just want to rewrite that manual SQL for AR, I'd try something along the lines of:

Code:

$this->db->select();
$this->db->from('hh_events');
$this->db->where('(dvd = 1 AND date >= NOW()) OR (theater = 1 AND date >= NOW())');
$this->db->limit('6');
$this->db->order_by('date ASC');

Haven't tested the code obviously but should be something along those lines..