Jump to content

Archived

This topic is now archived and is closed to further replies.

87dave87

Can't get date to format properly

Recommended Posts

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]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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]

???

Share this post


Link to post
Share on other sites
[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]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
[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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.