Active record and date in where statement

Active record and date in where statement - 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 and date in where statement (/showthread.php?tid=32370)

Pages: 1 2 3

Active record and date in where statement - El Forum - 07-21-2010

[eluser]smilie[/eluser]
Hi all,

Following code:

Code:
$query = $this->db1->query("SELECT count(*) FROM table WHERE created >= '".date("Y-m-d H:i:s",strtotime("-1 day"))."'");

$res = $query->num_rows();

is working.

However, with active record code:

Code:
$this->db1->where('created >=',date("Y-m-d H:i:s",strtotime("-1 day")));

I receive no results from the query... Why is this?
I have tried to escape it - but no prevail.

Thanks!
Smilie

Active record and date in where statement - El Forum - 07-21-2010

[eluser]gigas10[/eluser]
Does this work?

Code:
$date_stamp = date("Y-m-d H:i:s",strtotime("-1 day"));

$this->db1->where('created >=',$date_stamp);

Active record and date in where statement - El Forum - 07-21-2010

[eluser]smilie[/eluser]
Hi gigas10,

No, I have tried that as well, but also no results Sad

Regards,
Smilie

Active record and date in where statement - El Forum - 07-21-2010

[eluser]mddd[/eluser]
To prevent the wrong values from being escaped by CI, use 'false' as the third parameter in where().

Code:
$this->db->where('created >= '.$date, '', false);

By the way, if you want to find stuff newer than 1 day ago, why not do this:

Code:
$this->db->where('created >= NOW() - INTERVAL 1 DAY', '', false);

I often see people doing things in php that could be done in Mysql with less hassle.

Active record and date in where statement - El Forum - 07-21-2010

[eluser]smilie[/eluser]
mddd,

Still not working - not even with the MySQL NOW() function.

:-(

My best guess is that active record - for some reason - can not cope with the date in the where query...?

I am profiling the query and if I copy & paste it to the PHPMyAdmin = I get result back.

Maybe this would help, at the end I am doing:
$res = $this->db1->count_all_results();

Back to old script :-)

Thanks!
Smilie

Active record and date in where statement - El Forum - 07-21-2010

[eluser]mddd[/eluser]
The problem is your use of count_all_results. Check the CI manual. It is not meant to be called AFTER the query like num_rows, but INSTEAD OF the db->get() method.

Active record and date in where statement - El Forum - 07-21-2010

[eluser]smilie[/eluser]
Hi mddd,

I am only using count_all_results. I am at home now so I can post code itself tommorow. But I have 6 separate queries and only those with date function do not work.

From the top of the head, full code is:
$this->db1->where('sth',$date);
$this->db1->from('table');
$do = $this->db1->count_all_results();
return $do;

Without date expression it is working.

Sorry for any mistakes I am typing from mobile.

Regards,
Smilie

Active record and date in where statement - El Forum - 07-21-2010

[eluser]KingSkippus[/eluser]
[quote author="smilie" date="1279743258"]
However, with active record code:

Code:
$this->db1->where('created >=',date("Y-m-d H:i:s",strtotime("-1 day")));

I receive no results from the query... Why is this?
I have tried to escape it - but no prevail.
[/quote]

If you put this line after your query executes, what do you get?

Code:
echo '<pre>'.htmlentities($this->db1->last_query()).'</pre>';

Maybe we can find out what it is munging up to produce no results.

Active record and date in where statement - El Forum - 07-22-2010

[eluser]mddd[/eluser]
Smilie, the code is ok. The question is: what is in $date.

CI's db->where() is mostly suited for comparing EQUAL things. If you say where($a, $b) it will basically find records where field $a has the value $b.
When things get a bit more complicated (like using functions in the Mysql code, or comparing 'larger than' etc. its works better to write the code yourself and put it in a single string. Compare that to "nothing" (empty string) and make sure that escaping is off (third parameter : false).

Then you get (like I wrote before Smile

Code:
$this->db->where('created >= NOW() - INTERVAL 1 DAY', '', false);

And that should work fine.

Active record and date in where statement - El Forum - 07-22-2010

[eluser]smilie[/eluser]
Hi,

Here is complete function that does the query. It is located in models/reports_model.php

Code:
function objects_today()

    {

        $this->db1 = $this->load->database('dbname',TRUE);

        $this->db1->where('created >= NOW() - INTERVAL 1 DAY','',FALSE);

        $this->db1->from('test_table');

        $res = $this->db1->count_all_results();

        echo 'Result: <pre>'.htmlentities($this->db1->last_query()).'</pre>';

        return $res;

    }

Echoing last_query gives nothing back:

Code:
Result: <pre></pre>

I have tried following - all without success:

Code:
$date = date("Y-m-d H:i:s",strtotime("-1 day");

$this->db1->where('created >= $date','',FALSE);

and

Code:
$this->db1->where('created >= ',date("Y-m-d H:i:s",strtotime("-1 day"));

All without success. If I do not use active record, but do it 'manually' - it works.

Here is the portion of the database that I am querying (I have left only 2 rows which are relevant and the rest I have deleted from here).

Code:
CREATE TABLE IF NOT EXISTS `test_table` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `created` datetime DEFAULT NULL,

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

As I have found work around, this is not such a issue - but things bother me when I can not explain them :-)

Regards,
Smilie