php - Date format with codeigniter

Question

Any ideas onto why I'm getting a syntax error with this?

$this->db->select("DATE_FORMAT(".$this->news_articles_table."'.date_posted', '%M %D, %Y'");

UPDATE:

$this->db->select("DATE_FORMAT(".$this->news_articles_table."'.date_posted', '%M %D, %Y')");

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 'FROM (news_articles) JOIN news_categories ON news_articles.`news_categorie' at line 2

SELECT news_articles.id, news_articles.article_title, news_categories.category_name, CONCAT(first_name, ' ', last_name) AS author, DATE_FORMAT(news_articles'.date_posted', '%M %D, %Y'), statuses.status_name FROM (news_articles) JOIN news_categories ON news_articles.news_categories_id =news_categories.id JOIN users ON news_articles.author_id =users.user_id JOIN statuses ON news_articles.status_id =statuses.id

Filename: /home/xtremer/public_html/kowmanager/modules/news/models/news_model.php

Line Number: 74

Answer 1

The problem is hidden in the error message. Take a look at your SQL query syntax:

DATE_FORMAT(news_articles'.date_posted', `'%M` %D, `%Y')`

That doesn't look right, does it?

Because CI is trying to auto-protect your column names. So, to fix this, you need to pass FALSE to the second parameter of $this->db->select(), which will stop CI from trying to auto-protect these names.

This should work:

$this->db->select("DATE_FORMAT(".$this->news_articles_table.".date_posted, '%M %D, %Y')", FALSE);