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

a_z_

Posted: 07 May 2008 05:16 AM

[ Ignore ]

Summer Student

Total Posts: 3

Joined 2008-05-07

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

Profile

Derek Allard

Posted: 07 May 2008 07:26 AM

[ Ignore ] [ # 1 ]

Administrator

Total Posts: 6326

Joined 2006-03-23

You’ll need to turn off backticks for that query by adding “FALSE” as the second argument.

$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);

Signature

DerekAllard.com - CodeIgniter, ExpressionEngine, and the World of Web Design

Profile

a_z_

Posted: 07 May 2008 01:45 PM

[ Ignore ] [ # 2 ]

Summer Student

Total Posts: 3

Joined 2008-05-07

Turning off backticks has no effect on this problem.

Any other ideas?

thanks!
az

Profile

Derek Allard

Posted: 07 May 2008 01:46 PM

[ Ignore ] [ # 3 ]

Administrator

Total Posts: 6326

Joined 2006-03-23

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

Signature

DerekAllard.com - CodeIgniter, ExpressionEngine, and the World of Web Design

Profile

a_z_

Posted: 07 May 2008 01:56 PM

[ Ignore ] [ # 4 ]

Summer Student

Total Posts: 3

Joined 2008-05-07

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

Profile

Derek Allard

Posted: 07 May 2008 02:06 PM

[ Ignore ] [ # 5 ]

Administrator

Total Posts: 6326

Joined 2006-03-23

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

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)

$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');

Signature

DerekAllard.com - CodeIgniter, ExpressionEngine, and the World of Web Design

Profile

alxjvr

Posted: 07 May 2008 10:37 PM

[ Ignore ] [ # 6 ]

Summer Student

Total Posts: 14

Joined 2006-05-13

i tried checking the active record library and i found some interesting things.

I have this select in my 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:


$this->_merge_cache();

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

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:

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

Signature

will code for coffee

Profile

alxjvr

Posted: 08 May 2008 12:34 AM

[ Ignore ] [ # 7 ]

Summer Student

Total Posts: 14

Joined 2006-05-13

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:

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

into:

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

Signature

will code for coffee

Profile

Seppo

Posted: 08 May 2008 06:17 AM

[ Ignore ] [ # 8 ]

Research Assistant

Total Posts: 720

Joined 2006-10-18

alxjvr, instead of disabling select cache you can use this workaround

$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);

Signature

Once in a while I remember I use Twitter
I’m also in Linkster

Profile

alxjvr

Posted: 08 May 2008 09:15 AM

[ Ignore ] [ # 9 ]

Summer Student

Total Posts: 14

Joined 2006-05-13

Seppo, thanks a lot man! That worked!

Signature

will code for coffee

Profile

‹‹ CI 1.6.1 Postgre driver, cannot truncate a table referenced in a foreign key constraint anchor() helper UTF8 bug ››

Username		Remember Me?
Password		forgot password?

Post Marker Legend
	New posts	Hot Topic with new posts	New Poll	Moved Topic	Sticky topic
	No new posts	Hot Topic with no new posts	Old Poll	Closed Topic	Announcements

Visitor Statistics
The most visitors ever was 719, on June 06, 2008 10:16 AM
Total Registered Members:	59087	Total Logged-in Users:	18
Total Topics:	70111	Total Anonymous Users:	0
Total Replies:	377917	Total Guests:	268
Total Posts:	448028