Can't get date to format properly

[87dave87]

Hi,

I have a date field in my MySQL database tables.  It outputs as 2006-09-20.  I am trying to change this to 20 Sep 2006, I can query this through phpmyadmin for the result using DATE_FORMAT, but I don't know how to use the query on my site (php page).

Phpmyadmin gives me this php code for it: -

[code]$sql = 'SELECT DATE_FORMAT(updated, \'%d %b %Y\')'
        . ' FROM windows_atari2600';[/code]

'updated' is the date field, and 'windows_atari2600' is the table.

I have tried adding the above code to the following but it doesn't work: -

[code]<?php 
$database="mydatabase"; 
mysql_connect ("localhost", "myuser", "mypass"); 
@mysql_select_db($database) or die( "Unable to select database");
$sql = 'SELECT DATE_FORMAT(updated, \'%d %b %Y\')'
        . ' FROM windows_atari2600';
$result = mysql_query( "SELECT * FROM windows_atari2600" )
or die("SELECT Error: ".mysql_error());
while ($get_info = mysql_fetch_row($result)){ 
print "<tr>"; 
foreach ($get_info as $field) 
print "<td>$field</td>\n";
print "</tr>\n";   
}
?>[/code]

[HuggieBear]

You're almost there, you have the sql sorted, it's just the php...

I'd change the syntax slightly to this:

[code=php:0]$sql = "SELECT DATE_FORMAT(updated, '%d %b %Y') AS updated FROM windows_atari2600";
[/code]

There's no need to single quote and then escape.  And notice I've used a column alias (AS updated) this is to refer to the column when you fetch the array.

Then in the php to use that value you need to execute the sql and then use the column name to reference it

[code]
<?php
$sql = "SELECT DATE_FORMAT(updated, '%d %b %Y') AS updated FROM windows_atari2600";
$result = mysql_query($sql) or die("Unable to execute statement: " .mysql_error());
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
  echo "$row['updated']<br>\n";
}
[/code]

Regards
Huggie

[87dave87]

im not 100% sure where to put that code within this: -

[code]
<?php 
$database="mydatabase"; 
mysql_connect ("localhost", "myuser", "mypass"); 
@mysql_select_db($database) or die( "Unable to select database");
[b]HERE???[/b]
$result = mysql_query( "SELECT * FROM windows_atari2600" )
or die("SELECT Error: ".mysql_error());
while ($get_info = mysql_fetch_row($result)){ 
print "<tr>"; 
foreach ($get_info as $field) 
print "<td>$field</td>\n";
print "</tr>\n";   
}
?>
[/code]

???

[samshel]

[code]
<?php 
$database="mydatabase"; 
mysql_connect ("localhost", "myuser", "mypass"); 
@mysql_select_db($database) or die( "Unable to select database");
$sql = "SELECT DATE_FORMAT(updated, '%d %b %Y') AS updated FROM windows_atari2600";
$result = mysql_query($sql) or die("SELECT Error: ".mysql_error());
while ($get_info = mysql_fetch_row($result)){ 
print "<tr>"; 
foreach ($get_info as $field) 
print "<td>$field</td>\n";
print "</tr>\n";   
}
?>
[/code]

[craygo]

With a while statement use mysql_fetch_array. You won't need to do a double loop with a foreach function.

[code]<?php 
$database="mydatabase"; 
mysql_connect ("localhost", "myuser", "mypass"); 
@mysql_select_db($database) or die( "Unable to select database");
$sql = "SELECT DATE_FORMAT(updated, '%d %b %Y') AS updated FROM windows_atari2600";
$result = mysql_query($sql) or die("SELECT Error: ".mysql_error());
while ($get_info = mysql_fetch_array($result)){ 
print "<tr>"; 
print "<td>".$get_info['field']."</td>\n";
print "</tr>\n";   
}
?>[/code]

Ray

[87dave87]

Thanks that works, but now its not displaying all my other fields as the: -

'$result = mysql_query( "SELECT * FROM windows_atari2600" )'

is now gone, this was in the $result part, but now thats linking to the $sql select date format?

How can I add both?

[craygo]

ok I got ya.

[code]<?php 
$database="mydatabase"; 
mysql_connect ("localhost", "myuser", "mypass"); 
@mysql_select_db($database) or die( "Unable to select database");
$sql = "SELECT DATE_FORMAT(updated, '%d %b %Y') AS updated FROM windows_atari2600";
$result = mysql_query($sql) or die("SELECT Error: ".mysql_error());
while ($get_info = mysql_fetch_array($result)){ 
print "<tr>";
foreach($get_info as $field => $value){ 
print "<td>$value</td>\n";
}
print "</tr>\n";
}
?>[/code]

Ray

[obsidian]

or, you can avoid it all and use PHP to do it as well:
[code]
<?php
$sql = mysql_query("SELECT updated FROM windows_atari2600";
while ($row = mysql_fetch_array($sql)) echo date('j F Y', strtotime($row['updated'])) . "<br />\n";
?>
[/code]

good luck

[87dave87]

[quote author=craygo link=topic=108771.msg437940#msg437940 date=1158760609]
ok I got ya.

[code]<?php 
$database="mydatabase"; 
mysql_connect ("localhost", "myuser", "mypass"); 
@mysql_select_db($database) or die( "Unable to select database");
$sql = "SELECT DATE_FORMAT(updated, '%d %b %Y') AS updated FROM windows_atari2600";
$result = mysql_query($sql) or die("SELECT Error: ".mysql_error());
while ($get_info = mysql_fetch_array($result)){ 
print "<tr>";
foreach($get_info as $field => $value){ 
print "<td>$value</td>\n";
}
print "</tr>\n";
}
?>[/code]

Ray

[/quote]

No, thats displaying the dates in 2 columns now instead of the column data.

[craygo]

Well you are not going to get any other fields because you did not ask for them from the query. Only thing you called for was the date field. to get them all you are better off letting php format your date as obsidian said.

What information do you want to output anyway??

Ray

[87dave87]

I have a table with: -

name
version
updated (this is the date field)
rating
screenshot
download

I just need to get the date to be in the format of '20 Sep 2006' on the date field and display all the information above in a row which is repeated.

Everything is fine except the date.

If i used obsidian's code, where would that go in my original code?

[obsidian]

if you're wanting to simply format the date yet get all the data, just do this:
[code]
<?php
// after your connection to the database:
$sql = mysql_query("SELECT name, version, DATE_FORMAT(updated, '%d %b %Y') AS updated, rating, screenshot, download FROM windows_atari2600");
while ($get_info = mysql_fetch_row($sql)) { 
  echo "<tr>"; 
  foreach ($get_info as $field) echo "<td>$field</td>\n";
  echo "</tr>\n";
?>
[/code]

notice how you simply assign your date_format right within your query. it simply defines how you want the data returned for the updated column.

good luck

[craygo]

[code]<?php 
$database="mydatabase"; 
mysql_connect ("localhost", "myuser", "mypass"); 
@mysql_select_db($database) or die( "Unable to select database");
$sql = "SELECT * FROM windows_atari2600";
$result = mysql_query($sql) or die("SELECT Error: ".mysql_error());
while ($get_info = mysql_fetch_assoc($result)){ 
print "<tr>";
foreach($get_info as $field => $value){
if($field == 'updated'){
print "<td width=100>".date("j M Y", strtotime($value))."</td>\n";
} else {
print "<td>$value</td>";
}
}
print "</tr>\n";
}
?>[/code]

EDITED: This will also work if you have alot of fields and you don't want to list them all in the query

Ray

[87dave87]

Thanks... works fine now.  Quite a bit of hassle though just to change a date!