MySQL Function and CodeIgniter Active Record

Question

CodeIgniter Active Record is easy, well documented and powerful. But when I try to insert MySQL built in functions CONCAT, NOW, GROUP_CONCAT, DATEDIFF, TRIM etc or my custom functions it is giving errors. The following code works fine...

$result = $this->db->select('p.first_name, p.last_name, p.mobile_number, p.email_address')->from('profile p')->get()->result();

But When I want to contact first_name and last_name and use MySQL CONCAT function like this...

$result = $this->db->select('CONCAT(p.first_name, " ", p.last_name) fullname, p.mobile_number, p.email_address')->from('profile p')->get()->result();

It is showing database errors

A Database Error Occurred

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '", `p`.`last_name)` fullname, `p`.`mobile_number`, `p`.`email_address` FROM (`pr' at line 1

SELECT CONCAT(p.first_name, `"` ", `p`.`last_name)` fullname, `p`.`mobile_number`, `p`.`email_address` FROM (`profile` p)

Filename: D:\xampp\htdocs\example\system\database\DB_driver.php

Line Number: 330

Is there a any way to insert MySQL Functions inside CodeIgniter Active Record? Hope I am clear. Thanks in advance.

Answer 1

Since I don't know your exact error:

From user_guide:

$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.

$result = $this->db->select('CONCAT(p.first_name, " ", p.last_name) fullname, p.mobile_number, p.email_address', FALSE)->from('profile p')->get()->result();

Answer 2

Place you "select " in array $this->db->select(array('CONCAT(p.first_name, " ", p.last_name) fullname, p.mobile_number, p.email_address'))

by.

Answer 3

For the case of update using active records you can use

$this->db->set("date_read", "NOW()", FALSE);

Will output something like this

`SET `date_read` = NOW(),

Answer 4

• FIXED THE ISSUE

adding the field names in active record array can fix this problem. inside array you can use any mysql functions. the fix of above.

MySQL Table :

fullname             mobile_number  email_address                 
-------------------  -------------  ------------------------------
sitaramaiah javvadi  9989403339     [email protected]  
raja kumar guthula   9949526012     [email protected]     
chandra sekhar k.l   9912144556     [email protected]       
khadar  basha        98884884584    [email protected]        
super administrator  9841866445     [email protected]

CodeIgniter Active Record :

$result = $this->db->select(array('CONCAT(p.first_name, " ", p.last_name) `fullname`', 'p.mobile_number', 'p.email_address'))->from('profile `p`')->get()->result();
echo '<pre>';
print_r($result);

Output :

Array
(
    [0] => stdClass Object
        (
            [fullname] => sitaramaiah javvadi
            [mobile_number] => 9989403339
            [email_address] => [email protected]
        )

    [1] => stdClass Object
        (
            [fullname] => raja kumar guthula
            [mobile_number] => 9949526012
            [email_address] => [email protected]
        )

    [2] => stdClass Object
        (
            [fullname] => chandra sekhar k.l
            [mobile_number] => 9912144556
            [email_address] => [email protected]
        )

    [3] => stdClass Object
        (
            [fullname] => khadar  basha
            [mobile_number] => 98884884584
            [email_address] => [email protected]
        )

    [4] => stdClass Object
        (
            [fullname] => super administrator
            [mobile_number] => 9841866445
            [email_address] => [email protected]
        )

)