[SOLVED] Extracting from DATETIME in mysql

[andrewgarn]

Currently I have every time a user logs in the datetime saved into the database.

 

I want to extract this datatime and echo it in a friendly format. This is currently saving the current datetime to the lastlogin field

 

This is how I am loading the datetime from the database:

 

$logincheck = "SELECT * FROM guest WHERE username = '$activeusername' AND password = '$activepassword'";
//echo $query;
$result = mysql_query($logincheck);

while($r=mysql_fetch_array($result)) 
{ 
$admin=$r["admin"];
$lastlogin=$r["lastlogin"];
$name=$r["name"];
}

 

and setting current datetime back to database:

 

$date = date("m-d-y H:i:s");
$sql = "UPDATE guest SET lastlogin='$date' WHERE username='$activeusername'";
echo $sql;
$updatelogin = mysql_query($sql) or die(mysql_error());
if($admin == Yes) {

 

echo "Your last login was: ".$lastlogin;

 

gives: Your last login was: 2005-11-08 19:14:55 <-- why is this date wrong?

 

How would I extract parts from date time and echo something like:

 

Your last login was on 11th May 2008 at 19:14

 

 

[andrewgarn]

Ok I fixed the setting the correct date to the database by changing the $date variable

 

$date = date("y-m-d H:i:s");

[marcusfaye87]

I din't think it would work with a simple date function !

I made it really hard on myself I guess :s

 

<?php

function split_date ($dateTime)
{
$splitTime ['year'] = substr ($dateTime,0,4);
$splitTime ['month'] = substr ($dateTime,5,2);
$splitTime ['day'] = substr ($dateTime,8,2);

$dateTimeLength = strlen ($dateTime);

if ($dateTimeLength == 19)
{
	$splitTime ['hour'] = substr ($dateTime,11,2);
	$splitTime ['minute'] = substr ($dateTime,14,2);
	$splitTime ['second'] = substr ($dateTime,17,2);	
}

return $splitTime;
}

function format_date ($dateTime, $request)
{
$splitTime = split_date($dateTime);
$postTime['date'] = $splitTime ['day'] . "/" . $splitTime ['month'] . "/" . $splitTime ['year'];
$postTime['time'] = $splitTime ['hour'] . ":" . $splitTime ['minute'];

switch ($request)
{
	case 'date':
	return $postTime['date'];
	break;

	case 'time':
	return $postTime['time'];
	break;

	default:
	return false;
	break;	
}
}
?>

 

This simply converts the DateTime to a presentable date and time, so you never actually have to alter the timestamp

[andrewgarn]

How do i use that?

[andrewgarn]

It wont let me edit posts so:

 

This is what I tried:

 

$var = format_date($lastlogin) or die("Error");

 

 

Error:

 

Warning: Missing argument 2 for format_date(), called in C:\Server\XAMPP\htdocs\info2011\home.php on line 68 and defined in C:\Server\XAMPP\htdocs\info2011\home.php on line 47

Error

[andrewgarn]

This echo's the values but unformatted. Should the function be able to echo it all in once formatted?

 

and how could I print 5 instead of 05 for the day?

and how could I get it to print the month in words instead of numbers?

 

$date = split_date($lastlogin);

echo $date['day'].' '.$date['month'].' '.$date['year'].' '.$date['hour'].' '.$date['minute'];

 

= 12 05 2008 01 43

[DarkWater]

Uhh, why not just do this:

 

$logincheck = "SELECT *, DATE_FORMAT("%m-%d-%Y %h:%i:%S", lastlogin) AS lastlog FROM guest WHERE username = '$activeusername' AND password = '$activepassword'";

//echo $query;

$result = mysql_query($logincheck);

 

while($r=mysql_fetch_array($result))

{

$admin=$r["admin"];

$lastlogin=$r["lastlog"];

$name=$r["name"];

}

[andrewgarn]

Gave me a syntax error. But wouldnt that just print the date / time unformatted anyway?

 

I'm after something that will take the datetime stored on mySQL and convert it into this:

 

 

Last logged in: May 12th 2008 at 2:46

[DarkWater]

$logincheck = "SELECT *, DATE_FORMAT(lastlogin, '%M-%D-%Y %l:%i:%S') AS lastlog FROM guest WHERE username = '$activeusername' AND password = '$activepassword'";

 

 

=/

Then do

$lastlogin = $r['lastlog'];

 

And then just echo $lastlogin. It'll be formatted.

 

EDIT: Changed format to your specification.

[andrewgarn]

Thank you!

 

your one line did the trick

 

$loginchk = "SELECT *, DATE_FORMAT(lastlogin, '%M %D %Y at %l:%i:%S') AS lastlog FROM guest WHERE username = 	'$activeusername'";

 

Thanks

[DarkWater]

Any time. =)