echo timestamp from mysql into readable dates

[mlsbraves]

I've looked at the php manual about the date function(date.function ) And I've talked to a friend and I seem to be getting mixed ideas about how to disply timestamp from my mysql database.

One person told me that I needed to use the date function to do this. Such as:

echo $today = date("F j, Y, g:i a");

But I can't get it to use timestamps such as "1036034161"

When reading the notes on the page it suggested that I needed to use something like this:

SELECT date_format(lastaccesstime, '%M %e, %Y') FROM user WHERE userid = 'hecky';

I'm not excactly sure what I need to do here since I'm new to php. But first I would need to connect to the database. Then use the code.
Like:

<? 
$db = mysql_connect("localhost", "*", "*"); 
mysql_select_db("database", $db); 
SELECT date_format(lastaccesstime, '%M %e, %Y') FROM ib_topics WHERE tid = '1';

?>

Of course this doesnt work.
What do I need to do to echo a timestamp from the mysql database to come out in date format?

[Mr_Mako]

Ahh... one I think I can answer... 😃

I had this problem a while back. While I don't have my info immediately in front of me, the solution was fairly simple. You just need to make a variable call for the timestamp itself, and then add it to the date() function.

Like so:

// Variable created //
$time = [timestamp result after db query];

// Date code //
echo $today = date('$time', "F j, Y, g:i a");

I might have the syntax slightly off, but something similar should work for you.

Mako

[Mr_Mako]

Doh... sorry... I checked my code a second ago and saw I had given you wrong information. The correct way to do that would be:

// Timestamp Variable //
$time = [timestamp after db query];

// Date code //
echo format_date($time, "F j, Y, g:i a");

Hope that helps...

Mako

[mlsbraves]

Sorry I'm new at this but I tried:

<? 
// Timestamp Variable //
$time = [1043115788];

// Date code //
echo format_date($time, "F j, Y, g:i a");

?>

But that doesnt work. Do I have to connect to the database and then use this code?

[Mr_Mako]

Hmm... I was sure that's how I did it too. Obviously not.

Well, check out this page... it has the date functions parameters and a lot of examples that might help.

http://www.php.net/manual/en/function.date.php

[cgraz]

format_date isn't a php function. You're looking for date_format, which is a mysql function. Here's how to do what you need

<? 
mysql_connect("localhost", "*", "*") or die(mysql_error()); 
mysql_select_db("database") or die(mysql_error()); 

$query = mysql_query("SELECT date_format(lastaccesstime, '%Y %m %d') FROM ib_topics WHERE tid = '1'");

while($row = mysql_fetch_array($query)) {
   echo $row["lastaccesstime"] . "<br>";
}

?>

This assumes a few things

1. your username and password are * (needs to be changed
2. Your database name is database (needs to be changed if different)
3. that the timestamp field in your database is lastaccesstime
4. That the format you want is YYYY mm dd (different formating for date_format can be found here)
5. That your table name is ib_topics
6. That you have a field called tid and that there is a value of 1 somewhere in that field (or else no results will be displayed)

For learning purposes only, it may be better for you to start out with this:

mysql_connect("localhost", "*", "*") or die(mysql_error()); 
mysql_select_db("database") or die(mysql_error()); 

$query = mysql_query("SELECT date_format(lastaccesstime, '%Y %m %d') FROM ib_topics"); // took out the WHERE part

Then proceed to use the while($row. . .) code found above.

Cgraz

[mlsbraves]

I tried the following code but I get: Parse error: parse error on line 5

<? 
mysql_connect("localhost", "*", "*"); 
mysql_select_db("ib"); 

$query = mysql_query("SELECT date_format(start_date, '%Y %m %d') FROM ib_topics WHERE tid = '1'";

while($row = mysql_fetch_array($query)) {
   echo $row["start_date"] . "<br>";
}

?>

I then tried it where on the 5th line I took out the Where statement like below but get: Parse error: parse error, expecting T_STRING' orT_VARIABLE' or `T_NUM_STRING' on line 8

<? 
mysql_connect("localhost", "*", "*"); 
mysql_select_db("ib"); 

$query = mysql_query("SELECT date_format(start_date, '%Y %m %d') FROM ib_topics;

while($row = mysql_fetch_array($query)) {
   echo $row["start_date"] . "<br>";
}

?>

Can you tell me what I have done wronge now?

[cgraz]

it's your $query line. I forgot a closing parenthesis in my last post (now edited). Should be

$query = mysql_query("SELECT date_format(start_date, '%Y %m %d') FROM ib_topics WHERE tid = '1'");

Cgraz

[wired]

cgraz is a beast I tell you

[mlsbraves]

Well no more errors. Just a blank page. Do I need to echo something or is something wrong?

[cgraz]

your code should look like this:

<? 

mysql_connect("localhost", "*", "*"); 
mysql_select_db("ib"); 

$query = mysql_query("SELECT date_format(start_date, '%Y %m %d') FROM ib_topics WHERE tid = '1'") or die(mysql_error());

while($row = mysql_fetch_array($query)) {
   echo $row["start_date"] . "<br>";
}

?>

Make sure you set the server, username, and pass on mysql_connect()

Cgraz

[mlsbraves]

Still blank. The username and pass are correct.

[cgraz]

hmm. Try

SELECT date_format(start_date, '%Y %m %d') AS start_date FROM ib_topics WHERE tid = '1'

or when you access it, use

$row["0"]

Cgraz

[mlsbraves]

nothing. I doubled checked the table names and fields and everythings right. Just get a blank page.

[cgraz]

don't see why it wouldn't be working. You added in the mysql_error(), right?

Echo the count for the number of results.

echo "Count: " . mysql_num_rows($query);

You're sure that there is a field called start_date AND that it has a value where tid=1?

Cgraz

[xblue]

you didn't select something named start_date, so $row["start_date"] will not exist.

use an alias name in your select, that hopefully should solve the problem

<? 

mysql_connect("localhost", "*", "*"); 
mysql_select_db("ib"); 

$query = mysql_query("SELECT date_format(start_date, '%Y %m %d') AS formatted_date FROM ib_topics WHERE tid = '1'") or die(mysql_error());

while($row = mysql_fetch_array($query)) {
   echo $row["formatted_date"] . "<br>";
}

?>

[nnichols]

Can you clarify what you are storing in your db. In your original post you gave "1036034161" as
your example, which is a unix timestamp (representing "2002-10-31 03:16:01") not a MySQL TIMESTAMP
field. If it is a unix timestamp that you are storing you can use something like this -

<? 

mysql_connect('localhost', '*', '*'); 
mysql_select_db('ib'); 

$query = "SELECT lastaccesstime FROM ib_topics WHERE tid = '1'";
$res = mysql_query($query)
	or die('MySQL error: '.mysql_error());

while($row = mysql_fetch_array($res)) {
	echo date("F jS, Y", $row['lastaccesstime']) . '<br>';
}

?>

or you could use -

<? 

mysql_connect('localhost', '*', '*'); 
mysql_select_db('ib'); 

$query = "SELECT DATE_FORMAT(FROM_UNIXTIME(lastaccesstime), '%M %D, %Y') AS formatted_date FROM ib_topics WHERE tid = '1'";
$res = mysql_query($query)
	or die('MySQL error: '.mysql_error());

while($row = mysql_fetch_array($res)) {
	echo $row['formatted_date'] . '<br>';
}

?>

Hope this helps 😉