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

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

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: active record for [where date >= now()] (/showthread.php?tid=2380)

active record for [where date >= now()] - El Forum - 08-01-2007

[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

active record for [where date >= now()] - El Forum - 08-01-2007

[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();

active record for [where date >= now()] - El Forum - 08-01-2007

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

Any other way??

active record for [where date >= now()] - El Forum - 08-02-2007

[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())?

active record for [where date >= now()] - El Forum - 08-02-2007

[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

active record for [where date >= now()] - El Forum - 08-02-2007

[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...

active record for [where date >= now()] - El Forum - 08-03-2007

[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.

active record for [where date >= now()] - El Forum - 08-04-2007

[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

active record for [where date >= now()] - El Forum - 08-06-2007

[eluser]RaZoR LeGaCy[/eluser]
bump this

active record for [where date >= now()] - El Forum - 08-06-2007

[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.. Smile