unix_timestamp and echo

zevacronet

All i want to do is grab the timestamp from my db and echo it as a last login. This is what i have:

dbconnect();
$username = "guest";
$query = "SELECT UNIX_TIMESTAMP(timestamp '%M %D, %Y') as time FROM login WHERE user='$username'";
$result = mysql_query($query);
echo "Last login: $time";

😕

thanks,
gabe

nnichols

If the date/time is stored in a TIMESTAMP field then you can use -

dbconnect();
$username = "guest";
$query = "SELECT DATE_FORMAT(timestamp, '%M %D, %Y') AS time FROM login WHERE user='$username'";
$result = mysql_query($query);
echo "Last login: $time";

If the date/time is stored as a unix timestamp in an INT or CHAR field you will need to use -

dbconnect();
$username = "guest";
$query = "SELECT DATE_FORMAT(FROM_UNIXTIME(timestamp), '%M %D, %Y') AS time FROM login WHERE user='$username'";
$result = mysql_query($query);
echo "Last login: $time";

Hope this helps 😉

zevacronet

Thanks for the code! I have 1 more question. I've been trying to use functions and including them into my files so it's more organized but it seems like whenever i try to do a function for the database connection, it's off and on, meaning, sometimes it'll work sometimes it wont. I dont really understand this. This is what mine looks like right now and for the above code, it wasn't connecting.

this is my dbconnect() function

function dbconnect(){

// Database connection info
$location = "blah";
$database = "blah";
$username = "blah";
$pass = "blah blah";

$conn = @mysql_connect($location, $username, $pass);
if (!$result)
   echo "Couldn't connect to server";
$db = mysql_select_db($database);
if (!$db)
   echo "Couldn't select databse";
}

this should be right? I dont know if you have to write it different when it's in a function. Maybe someone can enlighten me. THANKS!!

nnichols

I have a similar function that I use for my db connection but it returns the link identifier so I can use it in my queries. Here's the function I use -

function dbconnect(){

// Database connection info
$host = 'localhost';
$database = 'db';
$user = 'user';
$pass = 'pass';

$conn = @mysql_pconnect($host, $user, $pass)
	or die('Could not connect to db server.<br>Error: ' . mysql_error());

@mysql_select_db($database)
	or die('Could not select db.<br>Error: ' . mysql_error());

return $conn;
}

Then when running queries I do something like -

$link = dbconnect();
$query = "SELECT DATE_FORMAT(timestamp, '%M %D, %Y') AS time FROM login WHERE user='$username'";
$res = mysql_query($query, $link)
	or die('MySQL error: ' . mysql_error() . '<br>Query: ' . $query);

Using code like this gives useful error messages when things go "tits up".

Hope this helps 😉

zevacronet

hahha thx nnichols, i'll try that code out. thanks for all the help

zevacronet

OK, so i entered all the code in. Fixed the database connection and thats working cause im using it to do something else on the page but I can't get the timestamp thing to work. I am using the timestamp format. It's also not giving me any error. 😕

Anyone??

thanks

nnichols

Post the code you have now that isn't working

zevacronet

 
$link = dbconnect();
//$username1 = "jen";
$query = 'SELECT DATE_FORMAT(timestamp, \'%M %D, %Y\') AS time FROM 
login WHERE user="jen" LIMIT 0,30';
$result = mysql_query($query, $link)
              or die('MySQL error: ' . mysql_error() . '<br>Query: ' . 
$query);
echo "Last login: $time, $query";

printed out the query as:
SELECT DATE_FORMAT(timestamp, '%M %D, %Y') AS time FROM login WHERE user="jen" LIMIT 0,30

nnichols

You hadn't retrieved the data from the result set -

$link = dbconnect();
//$username1 = "jen";
$query = 'SELECT DATE_FORMAT(timestamp, '%M %D, %Y') AS time FROM login WHERE user="jen" LIMIT 0,30';
$result = mysql_query($query, $link)
	or die('MySQL error: ' . mysql_error() . '<br>Query: ' . $query);
while ($row = mysql_fetch_array($result)) {
	echo 'Last login: ' . $row['time'];
	}