phpcodeigniteractiverecordescapingsql-function

CodeIgniter's active record using CONCAT_WS() in select() renders an invalid query


I am definitely not seeing the issue as to why this isn't working. Any ideas on possibilities?

$this->db->select(CONCAT_WS(' ', 'users.first_name', 'users.last_name') 'AS name');

EDIT:

I updated with the suggested line but for some reason I'm still getting an error.

function getAllMessages($user_id)
{
    $this->db->select('pm.id');
    $this->db->select('pm.subject');
    $this->db->select("CONCAT_WS(' ', users.first_name, users.last_name) AS name");
    $this->db->select("DATE_FORMAT('pm.date_sent', '%M %D, %Y'");
    $this->db->select('pm.message_read');
    $this->db->from('users_personal_messages AS pm');
    $this->db->join('users', 'users.user_id = pm.sender_id');
    $this->db->where('recipient_id', $user_id);
    $query = $this->db->get();
    if ($query->num_rows() > 0)
    {
        return $query->result();;
    }
    else
    {
        return 0;
    }
}

UPDATE:

function getAllMessages($user_id)
{
    $this->db->select('pm.id');
    $this->db->select('pm.subject');
    $this->db->select("CONCAT_WS(' ', users.first_name, users.last_name) AS name");
    $this->db->select("DATE_FORMAT('pm.date_sent', '%M %D, %Y')");
    $this->db->select('pm.message_read');
    $this->db->from('users_personal_messages AS pm');
    $this->db->join('users', 'users.user_id = pm.sender_id');
    $this->db->where('recipient_id', $user_id);
    $query = $this->db->get();
    if ($query->num_rows() > 0)
    {
        return $query->result();;
    }
    else
    {
        return 0;
    }
}

SECOND UPDATE:

function getAllMessages($user_id)
{
    $this->db->select('pm.id');
    $this->db->select('pm.subject');
    $this->db->select("CONCAT_WS(' ', users.first_name, users.last_name) AS name");
    $this->db->select(DATE_FORMAT(pm.date_sent, '%M %D, %Y'));
    $this->db->select('pm.message_read');
    $this->db->from('users_personal_messages AS pm');
    $this->db->join('users', 'users.user_id = pm.sender_id');
    $this->db->where('recipient_id', $user_id);
    $query = $this->db->get();
    if ($query->num_rows() > 0)
    {
        return $query->result();;
    }
    else
    {
        return 0;
    }
}

Solution

  • You need to quote the string properly. Column names should not be quoted, however the whole string parameter to select() must be quoted.

    $this->db->select("CONCAT_WS(' ', users.first_name, users.last_name) AS name");
    

    See the CodeIgniter select() docs for lots of examples...

    Update

    Your error is a missing parenthesis on the DATE_FORMAT() line:

    $this->db->select("DATE_FORMAT(pm.date_sent, '%M %D, %Y')");
    //------------------------------------------------------^^^^