[S]Can I use date/time in a table to view the latest update?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Braindead)1
Forum Newbie
Posts: 8
Joined: Sat Jan 31, 2004 6:44 am

[S]Can I use date/time in a table to view the latest update?

Post by Braindead)1 »

I have a mySQL database with in it:
usernames
email-addresses &
date/time

The date/time is altered whenever a user changes his email-adress.
Now I want to use this time/date to make an output to the screen like:

The email-list is <'time/date'> updated.

How can I retrieve, at all times, the latest update date/time??
This date/time needs to change whenever a (random) user alters his email-adress.

Extra info:
The field in MySQL for date/time is called "lastupdate".

Thanx in advance.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

SELECT MAX(lastupdate)
FROM emailadresses
GROUP BY lastupdate


?
Braindead)1
Forum Newbie
Posts: 8
Joined: Sat Jan 31, 2004 6:44 am

Post by Braindead)1 »

Well, it's official now.
I am a newbe, because I can't get the command to work.

If I only use the command, it will give no output.
I have tried to work with variables, but it just doesn't give the requested output.

Can anyone please help me?
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

Try :
SELECT * FROM foo ORDER BY lastupdate DESC LIMIT 1;

Replace * with whatever fields you want to retrieve and foo with your table name.
Braindead)1
Forum Newbie
Posts: 8
Joined: Sat Jan 31, 2004 6:44 am

Post by Braindead)1 »

Nope, still no result.

I tried:
SELECT lastupdate FROM usermail ORDER BY lastupdate DESC LIMIT 1

inhere, lastupdate is the fieldname and usermail is the tablename.

When I try:
$update = 'SELECT lastupdate FROM usermail ORDER BY lastupdate DESC LIMIT 1';
echo $update;

the result is that the exact text between '' is produced on the screen, not the value from the table.

I get the same result if I put the entire SELECT-command in "".

?????????

I'm puzzled now, because I was tought that information between "" would result into a variable, while between '' the output would be exactly what you put between them.
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

Er...yeah...
$update = 'SELECT lastupdate FROM usermail ORDER BY lastupdate DESC LIMIT 1'; won't actually run a query for you, it's just a string..

You want something along the lines of..

Code: Select all

$db = mysql_connect('localhost', 'user', 'password') or die(mysql_error());
mysql_select_db('thedbname') or die(mysql_error());
$query = 'SELECT lastupdate FROM usermail ORDER BY lastupdate DESC LIMIT 1';
$result = mysql_query($query) or die(mysql_error());
if(mysql_num_rows($result) > 0){
     $row = mysql_fetch_assoc($result);
     echo $row['lastupdate'];
} else {
    echo 'No rows returned!';
}
Braindead)1
Forum Newbie
Posts: 8
Joined: Sat Jan 31, 2004 6:44 am

Post by Braindead)1 »

Works fantastic!!!

Thanks a lot.
vijayanand16
Forum Newbie
Posts: 12
Joined: Fri Jan 30, 2004 4:57 pm

Post by vijayanand16 »

Use timestamp d type
Post Reply