Jump to content


Photo

Can't get date to format properly


  • Please log in to reply
13 replies to this topic

#1 87dave87

87dave87
  • Members
  • PipPipPip
  • Advanced Member
  • 86 posts

Posted 20 September 2006 - 12:30 PM

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: -

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

'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: -

<?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";    
} 
?>


#2 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 20 September 2006 - 01:01 PM

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

I'd change the syntax slightly to this:

$sql = "SELECT DATE_FORMAT(updated, '%d %b %Y') AS updated FROM windows_atari2600";

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

<?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";
}

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#3 87dave87

87dave87
  • Members
  • PipPipPip
  • Advanced Member
  • 86 posts

Posted 20 September 2006 - 01:28 PM

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

<?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";    
} 
?>

???

#4 samshel

samshel
  • Members
  • PipPipPip
  • Advanced Member
  • 837 posts

Posted 20 September 2006 - 01:38 PM

<?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";    
	} 
?>

Cheers,
SamShel
-----------------------------------------------
--: FUTURE IS LINUX :--
-----------------------------------------------

#5 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 20 September 2006 - 01:45 PM

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

<?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";    
	} 
?>

Ray

#6 87dave87

87dave87
  • Members
  • PipPipPip
  • Advanced Member
  • 86 posts

Posted 20 September 2006 - 01:46 PM

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?

#7 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 20 September 2006 - 01:56 PM

ok I got ya.

<?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";
	} 
?>

Ray


#8 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 20 September 2006 - 02:01 PM

or, you can avoid it all and use PHP to do it as well:
<?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";
?>

good luck
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#9 87dave87

87dave87
  • Members
  • PipPipPip
  • Advanced Member
  • 86 posts

Posted 20 September 2006 - 02:07 PM

ok I got ya.

<?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";
	} 
?>

Ray


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

#10 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 20 September 2006 - 02:13 PM

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

#11 87dave87

87dave87
  • Members
  • PipPipPip
  • Advanced Member
  • 86 posts

Posted 20 September 2006 - 02:20 PM

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?

#12 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 20 September 2006 - 02:24 PM

if you're wanting to simply format the date yet get all the data, just do this:
<?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";
?>

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
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#13 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 20 September 2006 - 02:34 PM

<?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";
	}
?>

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

Ray

#14 87dave87

87dave87
  • Members
  • PipPipPip
  • Advanced Member
  • 86 posts

Posted 20 September 2006 - 02:37 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users