Using Left in mysql querys problem accessing results

[eluser]jon29[/eluser]
hi i'm converting one of my sites from procedural php to codeigniter and having trouble when i use LEFT to restrict number of characters returned, query runs fine but as it returns an array key of LEFT(content, 220) i can't access it as normal in the views section again then same problem with the date,

could someone offer advice on the best way round this please?

Code:

function newspreview()
{
$sql = "SELECT id, title, LEFT(content, 20), DATE_FORMAT(date, '%d-%m-%Y') FROM newsletter ORDER BY date DESC LIMIT 3" ;
$q = $this->db->query($sql);
  if($q->num_rows() > 0) {
   foreach ($q->result_array() as $row) {
    $data[] = $row;
   }
   return $data;
  }  
  
}

returns

Array ( [0] => Array (
[id] => 4
[title] => Page
[LEFT(content, 20)] => What are pleased to a
[DATE_FORMAT(date, '%d-%m-%Y')] => 12-01-2012
)

any advice very much appreciated thanks

[eluser]Mauricio de Abreu Antunes[/eluser]
Use an alias.
Example:

Code:

select nickname, name, birthday_date as birthday
from users

Bye! :-)

[eluser]Mauricio de Abreu Antunes[/eluser]
Hey, just a tip:

Don't use database function in your queries.
Use left on php - substring.

[eluser]jon29[/eluser]
that's great thanks alot!

[eluser]Aken[/eluser]
You can use database functions just fine - just add an alias as mentioned so it actually has a column key to return.

[eluser]CroNiX[/eluser]
[quote author="Mauricio de Abreu Antunes" date="1329930637"]Hey, just a tip:

Don't use database function in your queries.
Use left on php - substring.[/quote]
That's not very good advice. Of course you can (and should) use mysql's built in powerful functions when possible. As pointed out, you just need to alias the column so it has a proper column name to return.

Code:

SELECT id, title, LEFT(content, 20) AS content, DATE_FORMAT(date, '%d-%m-%Y') AS the_date FROM newsletter ORDER BY the_date DESC LIMIT 3

result: id, title, content, the_date

[eluser]Mauricio de Abreu Antunes[/eluser]
IMHO, it's not good use SQL functions when you have a PHP as back-end technology.
Database are based on retrieve data, not format data.

[eluser]Aken[/eluser]
Profile it, see which one is actually more beneficial. Often times, the performance gains will be very minimal. You've gotta have a ton of traffic for it to be real important.