[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
[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);
[eluser]smilie[/eluser]
Hi gigas10,
No, I have tried that as well, but also no results
Regards,
Smilie
[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.
[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
[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.
[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
[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.
[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
Code: $this->db->where('created >= NOW() - INTERVAL 1 DAY', '', false);
And that should work fine.
[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:
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
|