upgraded to 1.6.1 and Active Record is eating one of my quotes

[eluser]a_z_[/eluser]
Hello all,

I just upgraded to 1.6.1 and an active record query
that was previously working is now failing in a strange
way:

$this->db->select("*,
DATE_FORMAT(endtime, '%b %d, %Y %l:%i %p') as prettyend,
DATE_FORMAT(starttime, '%b %d, %Y %l:%i %p') as prettystart");
$query = $this->db->get('assignments');

produces:

SELECT *, DATE_FORMAT(`endtime`, '%b %d, %Y %l:%i %p') as prettyend, DATE_FORMAT(`starttime`, %Y %l:%i %p') as prettystart FROM (`assignments`)

Note that the first DATE_FORMAT turned out fine, but the second one is
missing the quote before the format string. This causes mysql to choke.

Adding FALSE as the second parameter to the select function doesn't help.

If I remove the comma from the format string the problem goes away.
It also seems to go away if the two format strings are different. Even
if I just add a leading space to one of them!

Any ideas?

thanks,
az

[eluser]Derek Allard[/eluser]
You'll need to turn off backticks for that query by adding "FALSE" as the second argument.

Code:

$this->db->select("*,
DATE_FORMAT(endtime, ‘%b %d, %Y %l:%i %p’) as prettyend,
DATE_FORMAT(starttime, ‘%b %d, %Y %l:%i %p’) as prettystart", FALSE);

[eluser]a_z_[/eluser]
Turning off backticks has no effect on this problem.

Any other ideas?

thanks!
az

[eluser]Derek Allard[/eluser]
What does that produce? Use $this->db->last_query(); If you run that query directly in phpmyadmin does it work?

[eluser]a_z_[/eluser]
Turning off backticks with this code:

$this->db->select("*,
DATE_FORMAT(endtime, '%b %d, %Y %l:%i %p') as prettyend,
DATE_FORMAT(starttime, '%b %d, %Y %l:%i %p') as prettystart", FALSE);
$query = $this->db->get('assignments');

gives me:

SELECT *, DATE_FORMAT(endtime, '%b %d, %Y %l:%i %p') as prettyend, DATE_FORMAT(starttime, %Y %l:%i %p') as prettystart FROM (`assignments`)

Note that the field names have not been escaped with backticks,
but the second DATE_FORMAT is still missing the quote before
the format string.

If I try this query in phpmyadmin it chokes. If I add the missing
quote, it works.

thanks for your help,
az

[eluser]Derek Allard[/eluser]
[edit]
Sorry, its been a long day. I realize that you've already figured out the workarounds on your own, and I just re-typed them. I'm a goof, in any event, if you could file the bug report still it'll be helpful and we'll do our best to get to it.
[/edit]

[strike]

Hm, something in the regex is messing that up. I'm not sure where at the moment. Could you file a bug report for this. In the meantime, here are 2 workarounds.

1) if you change anything about the format string (I removed a ",") it works
2)

Code:

$query = $this->db->query('SELECT *, DATE_FORMAT(starttime,"%b %d, %Y %l:%i %p") as prettystart, DATE_FORMAT(endtime, "%b %d,  %Y %l:%i %p") as prettyend FROM assignments');

[/strike]

[eluser]alxjvr[/eluser]
i tried checking the active record library and i found some interesting things.

I have this select in my code:

Code:

$this->db->select('reportsqueue.reportID, reportsqueue.status, reportsqueue.repType, ' .
                'lpad(reportID,7,"0") as repCode, ' .
                'date_format(startDate, "%b %e, %Y") as dateStart, ' .
                'date_format(endDate, "%b %e, %Y") as dateEnd, ' .
                'date_format(doneStamp, "%b %e, %Y") as dateDone, ' .
                'date_format(createdStamp, "%b %e, %Y") as dateAdded ');

in system/database/DB_active_rec.php, function _compile_select, if i comment out the line:

Code:

$this->_merge_cache();

$this->ar_select becomes complete and everything goes fine:

Code:

Array
(
    [0] => reportsqueue.reportID
    [1] => reportsqueue.status
    [2] => reportsqueue.repType
    [3] => lpad(reportID
    [4] => 7
    [5] => "0") as repCode
    [6] => date_format(startDate
    [7] => "%b %e
    [8] => %Y") as dateStart
    [9] => date_format(endDate
    [10] => "%b %e
    [11] => %Y") as dateEnd
    [12] => date_format(doneStamp
    [13] => "%b %e
    [14] => %Y") as dateDone
    [15] => date_format(createdStamp
    [16] => "%b %e
    [17] => %Y") as dateAdded
)

$this->ar_select loses some of the elements due to the array_unique call in function _merge_cache,
notice that elements 10, 13, 16 are lost:

Code:

Array
(
    [0] => reportsqueue.reportID
    [1] => reportsqueue.status
    [2] => reportsqueue.repType
    [3] => lpad(reportID
    [4] => 7
    [5] => "0") as repCode
    [6] => date_format(startDate
    [7] => "%b %e
    [8] => %Y") as dateStart
    [9] => date_format(endDate
    [11] => %Y") as dateEnd
    [12] => date_format(doneStamp
    [14] => %Y") as dateDone
    [15] => date_format(createdStamp
    [17] => %Y") as dateAdded
)

the select statement becomes malformed when _compile_select appends them all back...somehow... i think


..

[eluser]alxjvr[/eluser]
hmm, seems to be working, what i did was to remove 'select' from the $ar_items array in line# 1645 of function _merge_cache(),

from:

Code:

$ar_items = array('select', 'from', 'join', 'where', 'like', 'groupby', 'having', 'orderby', 'set');

into:

Code:

$ar_items = array('from', 'join', 'where', 'like', 'groupby', 'having', 'orderby', 'set');

..

[eluser]Seppo[/eluser]
alxjvr, instead of disabling select cache you can use this workaround

Code:

$this->db->select(array(
                'reportsqueue.reportID',
                'reportsqueue.status',
                'reportsqueue.repType',
                'lpad(reportID,7,"0") as repCode',
                'date_format(startDate, "%b %e, %Y") as dateStart',
                'date_format(endDate, "%b %e, %Y") as dateEnd',
                'date_format(doneStamp, "%b %e, %Y") as dateDone',
                'date_format(createdStamp, "%b %e, %Y") as dateAdded'
), FALSE);

[eluser]alxjvr[/eluser]
Seppo, thanks a lot man! That worked!