Welcome Guest, Not a member yet? Register   Sign In
CI 1.7:if dbprefix is set, "$this->db->select" is trying to add dbprefix to its one of mysql funtion parameter.
#1

[eluser]kirrie[/eluser]
(first, i'm not an english user. so, please be tolerant about my english. Wink)

i'm using CI 1.7.0 and making a bulletin board system with it.
i set dbprefix "m_" in "system/application/config/database.php" and wrote some codes using Active Record Class to get a list of articles.
codes are like this:

Code:
$this->db->select("A.id, A.virtualnumber, A.author, A.subject, COUNT(CM.id) AS commentnumbering, A.count, DATE(A.created) AS created");
$this->db->from("board_article AS A");
$this->db->join("board_config AS C", "A.boardid = C.id");
$this->db->join("board_comment AS CM", "A.id = CM.articleid", "LEFT");
$this->db->where("C.identifier", $boardIdentifier);
$this->db->group_by("A.id");
$this->db->order_by("A.created", "DESC");
$this->db->limit($offset, $limit);
$ret = $this->db->get();

and, i expected query like this:

Code:
SELECT A.id, A.virtualnumber, A.author, A.subject, COUNT(CM.id) AS commentnumbering, A.count, DATE(A.created) AS created
FROM m_board_article AS A
JOIN m_board_config AS C ON A.boardid = C.id
LEFT JOIN m_board_comment AS CM ON A.id = CM.articleid
WHERE C.identifier = "notice"
GROUP BY A.id
ORDER BY A.created DESC
LIMIT 1, 10;

but i couldn't get any results from that. so i debugged it using Profiler Class.
Profiler said:

Code:
SELECT `A`.`id`, `A`.`virtualnumber`, `A`.`author`, `A`.`subject`, `m_COUNT(CM`.`id)` AS commentnumbering, `A`.`count`, `m_DATE(A`.`created)` AS created
FROM (`m_board_article` AS A)
JOIN `m_board_config` AS C ON `A`.`boardid` = `C`.`id`
LEFT JOIN `m_board_comment` AS CM ON `A`.`id` = `CM`.`articleid`
WHERE `C`.`identifier` = 'notice'
GROUP BY `A`.`id`
ORDER BY `A`.`created` DESC
LIMIT 1, 10

you can find what is wrong with this query. see SELECT clause. CI db class added dbprefix to mysql internal function. "m_COUNT(CM.id) AS commentnumbering" and "m_DATE(A.created) AS created" is it.

is it a bug or not?
am i missing something while i'm using this Active Record Class?

thanks.
#2

[eluser]Pascal Kriete[/eluser]
What you need here is the second parameter set to FALSE:
Quote:$this->db->select() accepts an optional second parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks. This is useful if you need a compound select statement.
#3

[eluser]kirrie[/eluser]
[quote author="inparo" date="1225575013"]What you need here is the second parameter set to FALSE:
Quote:$this->db->select() accepts an optional second parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks. This is useful if you need a compound select statement.
[/quote]

i know $this->db->select() has second parameter. i know there are some alternative ways to avoid what i have experienced. but, $this->db->select() is not supposed to add dbprefix to mysql internal functions that are used as its parameters, is it? and, if i could not use these Active Record Class to protect fields by auto escaping or something like that, what is that for?

plus, that solution didn't work. it still adds dbprefix.
#4

[eluser]TheGman[/eluser]
[quote author="Pascal Kriete" date="1225575013"]What you need here is the second parameter set to FALSE:
Quote:$this->db->select() accepts an optional second parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks. This is useful if you need a compound select statement.
[/quote]

Thanks...worked perfectly!




Theme © iAndrew 2016 - Forum software by © MyBB