store and retrieve date for users logins

rupertbj · 2002-11-03T18:00:06+00:00

Hi, i got a problem with: retriving the datetime data stored in the database and displaying it in two separate columns. Please let me know where i am going...

store and retrieve date for users logins

rupertbj

Hi, i got a problem with:

retriving the datetime data stored in the database and displaying it in two separate columns.

Please let me know where i am going wrong!

Thanks for your help in advance

rupertbj

<?php
$db = "register";
$link = mysql_connect( "localhost:/users/rupertbj/domains/rupstar.co.uk/sql/mysql.sock", "root" );
if ( !$link ) die( "Couldn't connect to MySQL" );
mysql_select_db( $db, $link ) or die( "Couldn't open $db: ".mysql_error() );
$sql = "SELECT * FROM userTable ORDER BY date_time DESC";
$result = mysql_query( $sql, $link );
while ( $newArray = mysql_fetch_array($result) )
	{
	$email = $newArray['email'];
	$date_time = $newArray['date_time'];
	}
	mysql_close( $link );
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0//EN">
<html>
<head>
<meta name="keywords" content="rupert, bowen-jones, rupert bowen-jones, homepage, room, friend, photo, book, read, chat, message, italy, slovakia">
<meta name="description" content="">
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" href="format.css" type="text/css">
<title>List users on rupstar.co.uk</title>
</head>
<body bgcolor="#8080FF">
<p align=center><img src="/images/rupstar.gif"></p>
<table width="100%" cellpadding=5>
  <tr width="33%"> 
    <td bgcolor="#FF9922" width="20%" valign=top><a href="index.php"><< BACK</a> 
    <td bgcolor="#003399" valign=top align="center" class="title">List users <table width="80%" cellspacing="10" cellpadding="3" align="center">
        <tr> 
          <td bgcolor="#FF0000" valign="top"><span class="text">User:</span><br><?php echo "$email<br>"; ?>
            </td>
          <td bgcolor="#FF0000" valign="top"><span class="text">Last logged on:</span><br><?php echo "$date_time<br>"; ?>
          </td>
        </tr>
      </table></td>
  </tr>
</table>
<p class=mini>Designed by <a href="mailto:design@rupstar.co.uk"><b>rupstar</b></a><b> 
  &copy; 2002</b></p>
</body>
</html>[code=php]

RoadKingHD

When you select the dattime field from your table, format the data with one of the MySQL DATE functions:

$SQL = "SELECT users.userid, users.pw, DATE_FORMAT(users.date_joined, '%W%M%Y') AS joined
FROM users LIMIT 10;"

From the MySQL man found at :

http://www.mysql.com/doc/en/Date_and_time_functions.html

SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
-> 'Saturday October 1997'
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
-> '22:23:00'
SELECT DATE_FORMAT('1997-10-04 22:23:00',
'%D %y %a %d %m %b %j');
-> '4th 97 Sat 04 10 Oct 277'
SELECT DATE_FORMAT('1997-10-04 22:23:00',
'%H %k %I %r %T %S %w');
-> '22 22 10 10:23:00 PM 22:23:00 00 6'
SELECT DATE_FORMAT('1999-01-01', '%X %V');
-> '1998 52

Tons of ways to work with dates right out of the MySQL table!

Kevin