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

Question

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;
    }
}

CONCAT_WS is a MYSQL function--not something PHP will recognize. You'll want to treat it as such — rjz
– rjz, Commented Mar 8, 2012 at 1:52
Have you tried $this->db->select("CONCAT_WS(' ', users.first_name, users.last_name) AS name", FALSE);? — danneth
– danneth, Commented Mar 8, 2012 at 8:09

Michael Berkowski · Accepted Answer · 2012-03-08 02:23:02Z

5

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')");
//------------------------------------------------------^^^^

edited Mar 8, 2012 at 2:23

answered Mar 8, 2012 at 1:52

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Jeff Davidson Over a year ago

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 'FROM (users_personal_messages AS pm) JOIN users ON users.user_id = pm.' at line 2 SELECT pm.id, pm.subject, CONCAT_WS(' ', users.first_name, users.last_name) AS name, pm.message_read FROM (users_personal_messages AS pm) JOIN users ON users.user_id = pm.sender_id WHERE recipient_id = '10000'

Michael Berkowski Over a year ago

@user1244239 The error is a missing ) on DATE_FORMAT() see above.

Michael Berkowski Over a year ago

You can tell, because the error points to the start of the FROM clause as the problem area.

Jeff Davidson Over a year ago

Updated code. WOW this is frustrating. I don't know why but now its saying 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 'FROM (users_personal_messages AS pm) JOIN users ON users.user_id = pm.' at line 2 SELECT pm.id, pm.subject, CONCAT_WS(' ', users.first_name, users.last_name) AS name, DATE_FORMAT('pm.date_sent', '%M %D, %Y'), pm.message_read FROM (users_personal_messages AS pm) JOIN users ON users.user_id = pm.sender_id WHERE recipient_id = '10000'

Michael Berkowski Over a year ago

@user1244239 Oops - didn't see the quotes around pm.date_sent in the DATE_FORMAT() call. Remove them. Never quote column or table names with single quotes.

|

Chintan Mathukiya · Accepted Answer · 2015-01-23 05:39:13Z

0

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

answered Jan 23, 2015 at 5:39

Chintan Mathukiya

3935 silver badges26 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Update

12 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Update

12 Comments

Comments

Linked

Related