MySQL date formatting

[jmueller0823]

I just finished a php MySQL query. Everything is great, except
the date column doesn't format correctly.

The date output looks like this: 1175114705

The date column prints from: echo $row;

Ideas? Thanks.

[somedude3488]

thats the time. hence "createdTime". thats what the php time() function looks like.

[jmueller0823]

Okay... but isn't there a way to 'convert' that into a standard, readable date format?

[ryan_vietnow]

$query="Select MINUTE(date),DAY(date),HOUR(date),
MONTH(date),YEAR(date),WEEK(date),
SECOND(date) from table"

the "date" is the date field of your table.
Try this.thanks.

then combine whatever field of time you want in the output.

[jmueller0823]

(new php coder here)

So, if I have something like this

@mysql_query('SELECT DAY(createdTime),MONTH(createdTime),YEAR(createdTime),title FROM...

How would I identify each column?
Normally I would use this: echo $row;
But not sure what to do with the date functions.

Thanks!

[jmueller0823]

$result = @mysql_query('SELECT createdTime FROM artman_article ');
if (!$result) {
  exit('<p>Error performing query: ' . mysql_error() . '</p>');
}

// Display the text of each rec in a paragraph
while ($row = mysql_fetch_array($result)) {
  echo $row[date('Y m d',strtotime($createdTime))];  
}

After some searching here, it looks like the date() and strtotime()
functions might do the trick.

See above.

But-- Apparently the syntax is incorrect (?). There are no results.
Thanks again.

[Rhyan]

I just finished a php MySQL query. Everything is great, except
the date column doesn't format correctly.

The date output looks like this: 1175114705

The date column prints from: echo $row;

Ideas? Thanks.

It seems that your timestamp is in unix format. In order to convert it to a humanly readble one there is a function in php called unixtojd(unixdate);

So if it is a unix date, then this code should do the trick:

$select = 'select timestamp from mydb.mytable';
$rslt = @mysql_query($select);
if (!$rslt)
  {
  echo 'Ooooppss:'.mysql_error();
  }
else {
$unixdate = mysql_fetch_array($rslt);
$normaldate = unixtojd($unixdate['timestamp']);
echo $normaldate;
}

Tell us if it works...Note php should be 4 and above

[jmueller0823]

Thanks.
(PHP Version 4.4.7)
Okay. Tried this:

<?php
  
$result = @mysql_query('SELECT createdTime FROM artman_article');
if (!$result) {
  exit('<p>Error performing query: ' . mysql_error() . '</p>');
}

// Display each rec
$unixdate= mysql_fetch_array($result);
$normaldate = unixtojd($unixdate['createdTime']);
echo $normaldate;
   
}

?>

No results. No errors. Ideas?
(The basic code structure is okay i.e. using different columns in Select,
the code will produce results. The above is just an excerpt from the script.)

[Rhyan]

Thanks.
(PHP Version 4.4.7)
Okay. Tried this:

<?php
  
$result = @mysql_query('SELECT createdTime FROM artman_article');
if (!$result) {
  exit('<p>Error performing query: ' . mysql_error() . '</p>');
}

// Display each rec
$unixdate= mysql_fetch_array($result);
$normaldate = unixtojd($unixdate['createdTime']);
echo $normaldate;
   
}

?>

No results. No errors. Ideas?
(The basic code structure is okay i.e. using different columns in Select,
the code will produce results. The above is just an excerpt from the script.)

Is that the complete code you have entered? If it is so, I don't see your loging into mysql. I don't need to see that, however, first login into mysql, then perform the query.

it should be like this:

@$connect=mysql_connect('host', 'user', 'pass');
if (!$connect){echo 'Login failed'.mysql_error();}
@$select = 'SELECT createdTime FROM artman_article';
@$result = mysql_query($select, $connect); /*$select is the query statement, and $connect is the connection identifier, so the mysql_query will use the correct connection.*/ 
if (!$result) {
  die('<p>Error performing query: '. mysql_error().'</p>');
}

// Display each rec
$unixdate= mysql_fetch_array($result);
$normaldate = unixtojd($unixdate['createdTime']); /* or try $normaldate = unixtojd($unixdate[0]); in case the mysql_fetch_array has returned a number indexed array. */
echo $normaldate;

I think it should be like this

[jmueller0823]

Nope. Tried $normaldate = unixtojd($unixdate[0]); also.
Returning a blank screen.

This is what I have. The Select and Display portions should
be the same as your example.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Featured Article Candidates</title>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
</head>
<body>
<font
<?php

// Connect to the db server
$dbcnx = @mysql_connect('localhost', 'user', 'password');
if (!$dbcnx) {
exit('<p>Unable to connect to the ' .
'database server at this time.</p>');
}

// Select the database
if (!@mysql_select_db('grow')) {
exit('<p>Unable to locate the ' .
'database at this time.</p>');
}

?>
<p>Featured Article Candidates</p>
<blockquote>
<?php

$result = @mysql_query('SELECT createdTime FROM artman_article');
if (!$result) {
exit('<p>Error performing query: ' . mysql_error() . '</p>');
}

// Display text
$unixdate= mysql_fetch_array($result);
$normaldate = unixtojd($unixdate[0]); /* or try $normaldate = unixtojd($unixdate[0]); in case the mysql_fetch_array has returned a number indexed array. */
echo $normaldate;

}

?>
</blockquote>
</font>
</body>
</html>

Again, this script does work with different columns. Thanks again for the assist.

[jmueller0823]

SOLVED

Simply: FROM_UNIXTIME()