Formatting date stored in mysql table

I have a fixture list stored in a table, date, venue, opposition

table consists of: id, date, venue, opposition.

date is stored as a 'date' yyyy-mm-dd, what i want to do is ouput the table

date, venue, opposition with the date formatted as dd-mm or dd-mm-yyyy

any help would be appreciated.
Thanks

re: Formatting date stored in mysql table

Use mysql's DATE_FORMAT() function.
Change your column name from date to some other, date is a mysql keyword.

re: Formatting date stored in mysql table

i don't quite understand how you mean.

I have renamed the 'date' field to 'matchdate'.

here is my original code, could you show me what you mean?
Thanks.

re: Formatting date stored in mysql table

Open the link he posted and read it.
P.S. It's not a bad idea to bookmark the MySQL refMal as well.

re: Formatting date stored in mysql table

Thanks I've read that and have amended my code, all is showing as expected apart from the column in which the date should be shown, which is showing up empty.

Any ideas?

Thanks

Here is the amended code:

re: Formatting date stored in mysql table

Use this code

[PHP]<?php


// no direct access
defined( '_VALID_MOS' ) or die( 'Restricted access' );

// Get data records from table.

$result=mysql_query("SELECT id, DATE_FORMAT(matchdate,'%d-%m-%Y') AS matchnight, venue, opposition, competition, faceoff, goalsfor, goalsagainst FROM war_fixtures");

?>

<table width="600" class="roster_border" cellpadding="0" cellspacing="1" >
<tr bgcolor="#78171e" class="table_header">
<td valign="top"><strong>Date</strong></td>
<td valign="top"><strong>Venue</strong></td>
<td valign="top"><strong>Opposition</strong></td>
<td valign="top"><strong>Competition</strong></td>
<td valign="top"><strong>Face Off</strong></td>
<td valign="top"><strong>F</strong></td>
<td valign="top"><strong>A</strong></td>
</tr>

<?
// Make a variable "$num" with a value "0".
$num=0;

// Do while loop for out put records.
while($row=mysql_fetch_assoc($result)){


?>
<tr class="table_content" bgcolor="<?=(++$num%2==0 ? "#E6D78E" : "#dcc55d")?>">
<td><? echo $row['matchnight']; ?></td>
<td><? echo $row['venue']; ?></td>
<td><? echo $row['opposition']; ?></td>
<td><? echo $row['competition']; ?></td>
<td><? echo $row['faceoff']; ?></td>
<td><? echo $row['goalsfor']; ?></td>
<td><? echo $row['goalsagainst']; ?></td>
</tr>

<?
// End while loop.
}

// Close database connection.
mysql_close();
?>
<? echo '</table>'; ?>[/PHP]

There is a difference between single quote ( ' ) and back tick ( ` ).

You were using single quotes around column names in DATE_FORMAT(). Use back tick or leave it altogether.
[PHP]$result=mysql_query("SELECT id, DATE_FORMAT('matchdate','%d-%m-%Y') AS 'matchnight', venue, opposition, competition, faceoff, goalsfor, goalsagainst FROM war_fixtures");[/PHP]

re: Formatting date stored in mysql table

Works perfectly, thanks for your help and patience with a mysql virgin.

re: Formatting date stored in mysql table

Welcome. You can post here whenever you face any problem.