MYSQL select date format query

[eluser]learning_php[/eluser]
Hi,

I have a database with a table called news which contains a id, date, news colums i can get the database to print to the screen witht the following:

<?PHP
sql = "select news, date FROM news";
$Q = $this->db->query($sql);
?>

<?PHP foreach ($Q->result() as $row):?>
<h1>&lt;?=$row->date?&gt;</h1>
<h1>&lt;?=$row->news?&gt;</h1>
<hr />
&lt;?PHP endforeach;?&gt;

but the date is in the format YYYYMMDD can anyone help me rewite the select statement to read DDMMYYYY?

Thanks

[eluser]rogierb[/eluser]
try

Code:

sql = “select news, DATE_FORMAT(date, '%d%m%Y') FROM news”;

[eluser]learning_php[/eluser]
Hi,

I get this error:


A Database Error Occurred

Error Number: 1582

Incorrect parameter count in the call to native function 'date_format'

select news, date_format('%d%m%y') FROM news


and my CSS breaks

[eluser]Dam1an[/eluser]
You need to use PHP Date codes instead of CI date codes in the SQL

[eluser]rogierb[/eluser]
ehm ctrl-c then ctrl-v ;-), you didnt copy the thing correctly
use DATE_FORMAT(date, '%d%m%Y')

[eluser]learning_php[/eluser]
I copyed can pasted it again but get another error:

A PHP Error was encountered

Severity: Notice

Message: Undefined property: stdClass::$date

Filename: views/homepage_view.php

Line Number: 70


Here is what I have and line 70 is <h1>&lt;?=$row->date?&gt;</h1>


&lt;?PHP
$sql="select news, DATE_FORMAT(date, '%d%m%y') FROM news";
$Q = $this->db->query($sql);
?&gt;

&lt;?PHP foreach ($Q->result() as $row):?&gt;
<h1>&lt;?=$row->date?&gt;</h1>
<h1>&lt;?=$row->news?&gt;</h1>
<hr />
&lt;?PHP endforeach;?&gt;

[eluser]Armchair Samurai[/eluser]
You need an alias in your SQL

Code:

$sql = "SELECT news, DATE_FORMAT(date, ‘%d%m%y’) AS date
        FROM news";

[eluser]learning_php[/eluser]
All working now Thanks