Problem with DATE_FORMAT and db prefix

[eluser]grolle[/eluser]
Hi,

I have a problem with MySql DATE_FORMAT and the dbprefix with the Active Record. I use this function:

Code:

function getAllProjects(){
            $this->db->select('DATE_FORMAT(deadline,"%d.%m.%Y") as deadline,id,name,number',FALSE);
            $this->db->order_by("name", "asc");
            $query=$this->db->get('projects');
            if($query->num_rows()>0){
                return $query->result_array();
            }
        }

But the Dates in the result array are displayed like: 08.sds_04.2010 (sds_ is the prefix). Why?

Best regards ...

[eluser]Bart v B[/eluser]
[quote author="grolle" date="1270567176"]Hi,

I have a problem with MySql DATE_FORMAT and the dbprefix with the Active Record. I use this function:

Code:

function getAllProjects(){
            $this->db->select('DATE_FORMAT(deadline,"%d.%m.%Y") as deadline,id,name,number',FALSE);
            $this->db->order_by("name", "asc");
            $query=$this->db->get('projects');
            if($query->num_rows()>0){
                return $query->result_array();
            }
        }

But the Dates in the result array are displayed like: 08.sds_04.2010 (sds_ is the prefix). Why?
Best regards ...[/quote]

Hmm.. don't know why there is an prefix.
But what i would do something like this:

Code:

<?php
function getAllProjects()
{

           $query = $this->db->query("DATE_FORMAT(deadline, '%d-%m-%Y') AS deadline_FRM,
                                      id,
                                      name,
                                      number
                                      FROM
                                      projects
                                      ORDER BY
                                      name
                                      ASC
                                   ");
            
            if($query-> num_rows() >0 )
            {
              foreach($query->result() as $row)
              {
                $data[] = $row;
              }
              return $data;    
             }
            
}
?>

Never use the same column name as an alias.
Hope it helps you

[eluser]cahva[/eluser]
Hmm.. That is indeed a reported bug and seems to be atleast from CI 1.7.1. I tried that also and it adds table prefix after %d. for some reason. Without the table prefix it works as it should.

@Bart v B:
I think the whole point is to use db_prefix, and that doesnt work on normal queries, only with AR. Ofcourse this problem can be avoided by adding the tableprefix manually using $this->db->dbprefix to the normal query which turns the query to this:

Code:

$query = $this->db->query('SELECT DATE_FORMAT(deadline,"%d.%m.%Y") deadline,id,name,number FROM '.$this->db->dbprefix.'projects ORDER BY name ASC');

[eluser]Bart v B[/eluser]
@cahva,

I never understood why in the world you should use a prefix.
It is not making anything saver, and it's only pain in the back when something is wrong.
In other words, why making live difficult when it's not needed.
So why use it.

[eluser]grolle[/eluser]
Hi,

thanks for your answers!

@Bart v B
with a prefix you can easily use a database for more than one application.

Best regards ....