mySQL date conversion syntax

[BarneyJoe]

This is something I've been meaning to figure out once and for all.

I have a field in a mySQL table 'Date_Added', of timestamp format, and is set to add in athe current time when a new record is added.

This natively outputs as

2006-12-06 08:35:15

So I'm trying to convert it to either 06-12-2006 or 6th December 2006.

My current query looks like

[code]"SELECT * FROM Keywords INNER JOIN (Photos INNER JOIN PhotoKeywords ON Photos.Photo_ID = PhotoKeywords.Photo_ID) ON Keywords.Keyword_ID = PhotoKeywords.Keyword_ID WHERE Photos.Photo_ID = %s
ORDER BY Keyword ASC"[/code]

Where Date_Added is a field in the table 'Photos'

So I'm trying to add in something like :

[CODE]date_format(Date_Added, '%a %D %b %Y') as formatted_date from Photos;[/code] to the query

and change the echo from

[code]<?php echo $row_PhotoSimple['Date_Added']; ?>[/code]

to

[code]<?php echo ['formatted_date']; ?>[/code]

or similar.

I've tried just about every permutation with these bits of codes, but can't quite seem to get the syntax correct.

Can anyone let me know where I'm going wrong?

Cheers,
Iain

[marcus]

[code]
<?php
$date = "2006-12-06";
$test = explode("-",$date);
$day = $test[2];
$month = $test[1];
$year = $test[0];

switch($day){
case 1:
$day = 1st;
break;
case 2:
$day = 2nd;
break;
case 3:
$day = 3rd;
break;
case 4:
$day = 4th;
break;
case 5:
$day = 5th;
break;
case 6:
$day = 6th;
//etc
}

switch($month){
case 1:
$month = January;
break;
case 2:
$month = Febuary;
break;
case 3:
$month = March;
case 12:
$month = Decemeber;
//etc
}

switch($year){
case 2006:
$year = 2006;
break;
case 2007:
$year = 2007;
break;
//etc
}

echo "The recorded time of this entry was on $day $month, $year";
?>
[/code]

non-tested, could give it a try.

[trq]

If you want to try and fomrat the date within your query you would need to use mysql's date functions not php, to do it with php, leave your query as is and use date_format() when you echo. eg;

[code=php:0]
<?php echo date_format($row_PhotoSimple['Date_Added'], '%a %D %b %Y'); ?>
[/code]

[BarneyJoe]

I must have been along the right lines, but had the syntax in the query wrong.

I just added a new query to the same page :

[code]mysql_select_db($database_Photolibrary, $Photolibrary);
$query_DateFormat = "SELECT date_format(Date_Added, '%D %b %Y') as formatted_date FROM photos";
$DateFormat = mysql_query($query_DateFormat, $Photolibrary) or die(mysql_error());
$row_DateFormat = mysql_fetch_assoc($DateFormat);
$totalRows_DateFormat = mysql_num_rows($DateFormat);
?>[/code]

And it's working fine.

But for future reference, what would the syntax be to fit it into the existing query :

[code]mysql_select_db($database_Photolibrary, $Photolibrary);
$query_PhotoSimple = sprintf("SELECT * FROM Photos WHERE Photo_ID = %s", $colname_PhotoSimple);
$PhotoSimple = mysql_query($query_PhotoSimple, $Photolibrary) or die(mysql_error());
$row_PhotoSimple = mysql_fetch_assoc($PhotoSimple);
$totalRows_PhotoSimple = mysql_num_rows($PhotoSimple);[/code]

[BarneyJoe]

[quote author=thorpe link=topic=117730.msg480525#msg480525 date=1165505063]
If you want to try and fomrat the date within your query you would need to use mysql's date functions not php, to do it with php, leave your query as is and use date_format() when you echo. eg;

[code=php:0]
<?php echo date_format($row_PhotoSimple['Date_Added'], '%a %D %b %Y'); ?>
[/code]
[/quote]

That one's returning the error message :

Fatal error: Call to undefined function date_format() in C:\wamp\www\Photolibrary\photoDetails.php on line 219

[trq]

Hehe... funnilly enough, you are using mysql's date_format function, it just happens to be exactly the same as php's.

[quote]But for future reference, what would the syntax be to fit it into the existing query[/quote]

It couldn't really, your using a wildcard *, which by th eway is a bad idea. You should really name your fields explicitly within your queries.

[trq]

[quote]That one's returning the error message[/quote]

Sorry, php's [url=http://php.net/date_format]date_format[/url] function is php5 >

[BarneyJoe]

[quote author=thorpe link=topic=117730.msg480533#msg480533 date=1165505507]
Hehe... funnilly enough, you are using mysql's date_format function, it just happens to be exactly the same as php's.

[quote]But for future reference, what would the syntax be to fit it into the existing query[/quote]

It couldn't really, your using a wildcard *, which by th eway is a bad idea. You should really name your fields explicitly within your queries.
[/quote]

I thought SELECT * was common usage for SELECT all fields? I didn't realise it was bad practice.

Iain

[trq]

Its slightly more work for the database, makes your code that little bit more difficult to read, and if you happen to be using numeric indecies instead of associative can muck up the whole order of your data.

I suppose I shouldn't say its a bad idea, just bad practice. That extra bit of time spent while developing just makes things easier in the future.

[roopurt18]

In order to use MySQL's DATE_FORMAT with the asterisk, you'd do something like:
[code]
SELECT *, DATE_FORMAT(col, 'format') AS some_name FROM table WHERE 1
[/code]

But as Thorpe said, it's better to name your columns.

[BarneyJoe]

Thanks guys - that's one of the reasons I love forums like this - their conversational nature means it's a good way of picking up new stuff all the time. So is the basic syntax just :

SELECT field1, field3, field3 etc FROM table ?

[BarneyJoe]

Actually, the date isn't working I've noticed.

I added a sample batch of records on 23 Nov, and they all display 23rd Nov 2006 quite happily.

But when I add in any new records, they're also displaying as 23rd Nov 2006, but are showing with today's date in mySQL.

My code is :

[code]
<?php require_once('Connections/Photolibrary.php'); ?>
<?php
$colname_PhotoSimple = "1";
if (isset($_GET['Photo_ID'])) {
  $colname_PhotoSimple = (get_magic_quotes_gpc()) ? $_GET['Photo_ID'] : addslashes($_GET['Photo_ID']);
}
mysql_select_db($database_Photolibrary, $Photolibrary);
$query_PhotoSimple = sprintf("SELECT * FROM Photos WHERE Photo_ID = %s", $colname_PhotoSimple);
$PhotoSimple = mysql_query($query_PhotoSimple, $Photolibrary) or die(mysql_error());
$row_PhotoSimple = mysql_fetch_assoc($PhotoSimple);
$totalRows_PhotoSimple = mysql_num_rows($PhotoSimple);

$colname_PhotoComplex = "1";
if (isset($_GET['Photo_ID'])) {
  $colname_PhotoComplex = (get_magic_quotes_gpc()) ? $_GET['Photo_ID'] : addslashes($_GET['Photo_ID']);
}
mysql_select_db($database_Photolibrary, $Photolibrary);
$query_PhotoComplex = sprintf("SELECT * FROM Keywords INNER JOIN (Photos INNER JOIN PhotoKeywords ON Photos.Photo_ID = PhotoKeywords.Photo_ID) ON Keywords.Keyword_ID = PhotoKeywords.Keyword_ID WHERE Photos.Photo_ID = %s ORDER BY Keyword ASC", $colname_PhotoComplex);
$PhotoComplex = mysql_query($query_PhotoComplex, $Photolibrary) or die(mysql_error());
$row_PhotoComplex = mysql_fetch_assoc($PhotoComplex);
$totalRows_PhotoComplex = mysql_num_rows($PhotoComplex);

mysql_select_db($database_Photolibrary, $Photolibrary);
$query_DateFormat = "SELECT date_format(Date_Added, '%D %b %Y') as formatted_date FROM photos";
$DateFormat = mysql_query($query_DateFormat, $Photolibrary) or die(mysql_error());
$row_DateFormat = mysql_fetch_assoc($DateFormat);
$totalRows_DateFormat = mysql_num_rows($DateFormat);
?>[/code]

and

[code]<?php echo $row_DateFormat['formatted_date']; ?>[/code]

To display the date.

Any ideas what's happening here?