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-22-2010

[eluser]mddd[/eluser]
Super weird. Almost seems like a bug in count_all_results() ?
Are queries from count_all_results not stored for reference in last_query() ?
Do you get a result back from $res? That would mean the query is executed, but is not returned by last_query.

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

[eluser]smilie[/eluser]
mddd,

What do you mean by:
"Do you get a result back from $res?"

The function runs and finishes and $res returns empty. There is no error or such.
But result is always 0 (zero - integer). I guess that it messes up the query for some reason and that MySQL returns therefore 0 as result.

Regards,
Smilie

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

[eluser]mddd[/eluser]
What I mean to find out is if there is any query done or not.
If last_query() doesn't return anything, that could be because there really was not query, or there is something else going on.
If the function returns a number, we know that the query did run. So then it must be that count_all_results and last_query don't work nice together.

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

[eluser]smilie[/eluser]
mddd,

I stumbled upon something.

Code:

Code:
$this->db1 = $this->load->database('db',TRUE);

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

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

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

Produces:

Code:
SELECT COUNT(*) AS `numrows`

FROM (`table`)

WHERE created >= NOW() - INTERVAL 1 DAY

This is from the profiler. CodeIgniter returns 0 found rows.
Same query in the PHPMyAdmin returns 1 found row.

However I saw in another query that WHERE / AND statement is like this:

Code:
AND `id` = 'action_id'

Now, I do not know if this is profiler 'error' or my browser - or...
But, when I copy & paste query in PHPMyAdmin it also returns 0 results.
However, if I remove quotes from the query (particullary these -> `` quotes) - then it is working.

Maybe this helps some? :-)

Regards,
Smilie

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

[eluser]WanWizard[/eluser]
The profiler doesn't make errors, it simply lists the queries as they were executed by the database library.

If you see an extra where clause, check your code to see where it is defined. The database class only resets it's internal arrays after executing a query, so a $this->db->where() in your code is remembered by the database class until you execute the query.

I find it hard to believe that the exact same query produces different results when run from PHPMyAdmin. As mentioned before, output $this->db1->last_query() directly after you run the query, to make sure you're looking at the same query.

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

[eluser]smilie[/eluser]
WanWizard,

Query is ok (also all 'where' statements) - what 'seems' to be wrong are quotes;
At least quote `` (that's the one above TAB on keyboard) does not work in PHPMyAdmin or in console:

Original code as found in profiler:

Code:
mysql> SELECT COUNT(*) AS `numrows`

    -> FROM (`table1`, `table2`)

    -> WHERE `invalid` != '1'

    -> AND `status` = '0'

    -> AND `id` = 'action_id'

    -> AND `atempt` > '3';

+---------+

| numrows |

+---------+

|       0 | 

+---------+

1 row in set (0.00 sec)

When I remove those quotes:

Code:
mysql> SELECT COUNT(*) AS numrows FROM table1, table2 WHERE invalid != 1 AND status = 0 AND id = action_id AND atempt > 3;

+---------+

| numrows |

+---------+

|      29 | 

+---------+

1 row in set (0.03 sec)

But again - I do not know where those quotes come from. Maybe they are only to make page nicer, maybe query _really_ does go with those quotes to the MySQL.

Edit: I have placed last_query - but it is empty Sad

Regards,
Smilie

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

[eluser]WanWizard[/eluser]
The only issues I can see is that for some reason your numbers are quoted as strings. Which might cause unwanted behaviour for atempt > '3', depending on your column definition. And 'action_id' in the CI query is defined as a string literal, while in your modified version it's defined without quotes, so it's a column name.

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

[eluser]smilie[/eluser]
WanWizard,

Query is as I have found it in profiler. I do not know if query as such is being sent to the MySQL from CI. If yes - that would explain why I do not see any results back.

If not - then I am still troubled why it does not work.

I have also tried to add TRUE as third parameter to the $this->db1->where('action_id','3',TRUE) - but result is same.

Regards,
Smilie

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

[eluser]mddd[/eluser]
Smilie, if you compare the two queries, the quoted table names and column names are not the problem.

Code:
select * from `table`

does the same as

Code:
select * from table

But there is a real difference between

Code:
id = 'action_id'

and

Code:
id = action_id

The first means id's value is "action_id". The second means id's value is the same as action_id's value!

WanWizard mentioned this before, but I had the feeling you missed this bit.

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

[eluser]smilie[/eluser]
Hi mddd,

Yes, I got that one - just do not know how to achieve it with active records class Sad

But however I create:
$this->db1->where($where);
statement, CI automatically puts quotes around it giving me no choice further.

So, it seems you can not compare two columns with the active record?

Regards,
Smilie