[solved]date_format sql in query

[eluser]swgj19[/eluser]
I have read several articles on this and went to the source
http://dev.mysql.com/doc/refman/5.1/en/d...ate-format

I am trying to accomplish a similar query in ci in my model

mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
-> 'Sunday October 2009'

There is a table 'posts' and column 'pub_date'.

Here is what I have so far:

Code:

function format_date(){

    $data = array();
    $this->db->select date_format('pub_date', '%W %M %Y');
    $Q = $this->db->get('posts');
         if ($Q->num_rows() > 0){
           foreach ($Q->result_array() as $row){
             $data[] = $row;
           }
        }
        $Q->free_result();  
        return $data;

[eluser]john_j[/eluser]
So, what is the problem that you are facing?

[eluser]swgj19[/eluser]
I am trying to convert 2012-09-17 to a clean date format September 17th, 2012 by sql in my query. Then I need to include this function in my foreach loop in my view. I need to convert this with sql format and not php.

I am reading that this can be done by sql using DATE_FORMAT.

I just do not know how to accomplish this in my codeigniter query in model and pass to view

Code:

<?php
if (count($posts)){
foreach ($posts as $key => $list){
  echo "<div class='posts'>";
  echo "<h2>".$list['title']."</h2>\n";
  echo "<h5>".$list['pub_date']."</h5>\n";
  echo auto_typography(word_limiter($list['body'], 100));
  echo anchor('blog/post/'.$list['id'],'read more');
  echo '</div>';
}
echo "<br/><br/>";
}

[eluser]CroNiX[/eluser]
Did you try

Code:

$db->select("DATE_FORMAT(YOUR_DATE_COLUMN, '%W %M %Y') AS formatted_date", FALSE);

[eluser]swgj19[/eluser]
Here is what I tried:

posts model

Code:

function getLivePosts($limit){


     $data = array();

     $this->db->limit($limit);
     $this->db->select("DATE_FORMAT(pub_date, '%W %M %Y') AS formatted_date", FALSE);
     $this->db->where('status', 'published');
     $this->db->order_by('pub_date','desc');
     $this->db->get('posts');                  
     $Q = $this->db->get('posts');
     if ($Q->num_rows() > 0){
       foreach ($Q->result_array() as $row){
         $data[] = $row;
       }
    }
    $Q->free_result();  
    return $data;
}

view

Code:

<?php
if (count($posts)){
foreach ($posts as $key => $list){
  echo "<div class='posts'>";
  echo "<h2>".$list['title']."</h2>\n";
  echo "<h5>".$list['pub_date']."</h5>\n";
  echo auto_typography(word_limiter($list['body'], 100));
  echo anchor('blog/post/'.$list['id'],'read more');
  echo '</div>';
}
echo "<br/><br/>";
}

[eluser]CroNiX[/eluser]
And? What happens?

You took off the "AS formatted_date" alias, so how are you going to access this new column in your results? It's not called pub_date anymore (that's the original value) since you ran a function on it (DATE_FORMAT()).

Your view should be

Code:

echo "<h5>".$list['formatted_date']."</h5>\n";

if you use the exact query I showed with the alias.

You are also running db::get('posts') 2x.

The second time wipes out the first and resets the query, which would cause it to get ALL results for that table.

[eluser]swgj19[/eluser]
I changed the new function in my view and deleted the second db->get('posts') as show below. Now I am getting an error undefined index for the title, body, and id.

Code:

function getLivePosts($limit){


     $data = array();

     $this->db->limit($limit);
     $this->db->select("DATE_FORMAT(pub_date, '%W %M %Y') AS formatted_date", FALSE);
     $this->db->where('status', 'published');
     $this->db->order_by('pub_date','desc');                
     $Q = $this->db->get('posts');
     if ($Q->num_rows() > 0){
       foreach ($Q->result_array() as $row){
         $data[] = $row;
       }
    }
    $Q->free_result();  
    return $data;
}

view

Code:

<?php
if (count($posts)){
foreach ($posts as $key => $list){
  echo "<div class='posts'>";
  echo "<h2>".$list['title']."</h2>\n";
  echo "<h5>".$list['formatted_date']."</h5>\n";
  echo auto_typography(word_limiter($list['body'], 100));
  echo anchor('blog/post/'.$list['id'],'read more');
  echo '</div>';
}
echo "<br/><br/>";
}

[eluser]CroNiX[/eluser]
Well, you're not selecting them in your query. Your select is only getting the date.
Write a second select using only those "normal" columns. You have to write the DATE_FORMAT() statement as a single select due to it's complexity because you don't want the identifiers escaped on that one as CI will mess it up (that's why there is a FALSE as the 2nd parameter there)

Code:

$this->db->select('id, body, title');
$this->db->select("DATE_FORMAT(pub_date, '%W %M %Y') AS formatted_date", FALSE);

You'll now have:
id
body
title
formatted_date

[eluser]swgj19[/eluser]
Thanks CroNix, the date is displaying Monday September 2012. It is working.

I learned something new. I knew that I could not just loop through the raw column 'pub_date', I had to have a new function. I did not know that AS formatted_date could be declared in the sql query.

Can you further explain

because you don’t want the identifiers escaped on that one (that’s why there is a FALSE as the 2nd parameter there)

[eluser]CroNiX[/eluser]
Active Record places tickmarks (`) around table and field names to kind of escape them and separate them from MYSQL reserved words. Lets say you had a table or column named 'update'. That would cause problems if it didn't have `update` if you aren't performing an actual UPDATE.

CI does this for you automatically, although I wish it wouldn't as it only protects dumb people from naming columns the same as reserved words, which you obviously should never do. However, it doesn't do it very well with complex statements and it puts the tickmarks around the wrong things. The tickmarks vary from database to database. They don't all use (`) as the escape character.

Do an echo $this->db->last_query() to see the actual query being executed and you will see them.
Remove the FALSE from the DATE_FORMAT() query and run db::last_query() again. The query will produce an error and when you look at the raw sql generated you will see it's wrong for that part of the select.

So, in general, when using MySQL functions in queries, put them as their own select statement and use FALSE as the 2nd parameter.

Here's some more reading:
http://stackoverflow.com/questions/34375...-tickmarks