How to display time and date

jo3

In my sql queries I've used NOW() to display the date user registered like below:

$query = "INSERT INTO user (name, email, username, password, date_registered) VALUES ('$name', '$email', '$username', MD5('$pass'), NOW())";

But it will display only the month and year registered. What is the command to display the time, date (dd/mm/yyyy) format?

mikawhat

i can help you ony with the date format part...

SELECT DATE_FORMAT(date_registered, '%d/%m/%Y') as date FROM db

this will return the date in european style

bradgrafelman

Perhaps use this instead of NOW() ?

DATE_FORMAT(NOW(), '%d/%m/%Y')

More information about MySQL's date/time functions can be found on this manual page: Date and Time Functions.

jo3

Ok. I have changed the date format. Here's the code:

<?php
include("mysql_connect.php");

$query = "SELECT name, email, DATE_FORMAT(date_registered, '%d/%m/%y') AS date FROM user ORDER BY name";

$result = @mysql_query($query);

if ($result)
{
 echo "<table><tr><td><strong>Name</strong></td><td><strong>Email</strong></td><td><strong>Date Registered</strong></td></tr>";

 while ($row = mysql_fetch_array($result))
 {
  echo "<tr><td>$row[name]</td><td>$row[email]</td><td>$row[date_registered]</td></tr>";
 }

 echo "</table>";
 mysql_free_result($result);
}
else
echo "The user cannot be displayed due to system error";

mysql_close();

?>

But there other problem. At:

  echo "<tr><td>$row[name]</td><td>$row[email]</td><td>$row[date_registered]</td></tr>";

if I put $row[date_registered], it wont display the date. But if I put $row[1], it will display the date. Infact row 1 in the table is the name column, while the date_registered is the 5th column. When I put $row[5] (which should display the date), it didn't. Why is this?

bradgrafelman

Uh... few things.

Array indeces in your case should be strings. $row[name] does not have a string as an index, but a constant. PHP will detect this (assuming there actually isn't a constant named 'last') and throw an E_WARNING and then assume it is a string. So, use quotes: $row['name'].
Arrays, more specifically items in arrays referenced by indeces, should not simply appear inside a double-quoted string. If they do, they should be wrapped in braces ( {} ). Instead, I usually escape out of the string and concatenate the array in the middle, like so:
```
echo 'Your name is: ' . $row['name'] . '! Hello there.';
```
In your SQL query, you gave an alias to the DATE_FORMAT() function, but then you're attempting to access the array item using a different index. In your query, the DATE_FORMAT() function is the 3rd item, so it should be in $row[2], though I do not know why it is in 1. Though fixing the alias name will resolve this, you can always use [man]print_r/man on arrays in the future to debug them.