latest data from table

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
g3ckO
Forum Contributor
Posts: 117
Joined: Mon Jul 12, 2004 2:57 am
Location: Malaysia
Contact:

latest data from table

Post by g3ckO »

I have a table something like below:

Code: Select all

user1          green          approved
user2          black           approved
user1          black           pending
user1          green          approved  <------- this row
user2          black           pending
How should I write the query to call the data on that row. In other word is to call the user1 latest data (row) on that table.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

is there any other data to sort by?
User avatar
g3ckO
Forum Contributor
Posts: 117
Joined: Mon Jul 12, 2004 2:57 am
Location: Malaysia
Contact:

Post by g3ckO »

feyd wrote:is there any other data to sort by?
what do you mean?
There is other fields like userID, name, unit and so on but all the data are same for each user everytime the data is store into database.
User avatar
m3mn0n
PHP Evangelist
Posts: 3548
Joined: Tue Aug 13, 2002 3:35 pm
Location: Calgary, Canada

Post by m3mn0n »

This db layout doesn't seem very organized. Either that or your explaination is a bit off. Either that or I need some sleep. :x
User avatar
g3ckO
Forum Contributor
Posts: 117
Joined: Mon Jul 12, 2004 2:57 am
Location: Malaysia
Contact:

Post by g3ckO »

Ok. I think this is more easy to understand.

If we have duplicate data in one table, how can we write the query to call the latest one.
User avatar
m3mn0n
PHP Evangelist
Posts: 3548
Joined: Tue Aug 13, 2002 3:35 pm
Location: Calgary, Canada

Post by m3mn0n »

Timestamp the entires. Or go by highest userID/entryID or whatever.
User avatar
g3ckO
Forum Contributor
Posts: 117
Joined: Mon Jul 12, 2004 2:57 am
Location: Malaysia
Contact:

Post by g3ckO »

Sami wrote:Timestamp the entires.
How to use timestamp?

I ask the user to enter the date and it will be store in db in this format
yyyy-mm-dd

can I use this info to call the latest data. How the query will look like?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

You should not allow the user to input the timestamp.
If you are using MySQL use NOW() instead.

This way if you SELECT the rows, you can ORDER them BY timestamp DESC and LIMIT the resultset to 1 row
jason
Site Admin
Posts: 1767
Joined: Thu Apr 18, 2002 3:14 pm
Location: Montreal, CA
Contact:

Post by jason »

User avatar
g3ckO
Forum Contributor
Posts: 117
Joined: Mon Jul 12, 2004 2:57 am
Location: Malaysia
Contact:

Post by g3ckO »

That means, I just need to add another field for timestamp. But how can I insert the value into that field?? Never use timestamp before..
User avatar
m3mn0n
PHP Evangelist
Posts: 3548
Joined: Tue Aug 13, 2002 3:35 pm
Location: Calgary, Canada

Post by m3mn0n »

Well when in doubt about something, Google it.

It would lead you to a page like the one jason linked to, or this one: [php_man]time[/php_man]()
User avatar
g3ckO
Forum Contributor
Posts: 117
Joined: Mon Jul 12, 2004 2:57 am
Location: Malaysia
Contact:

Post by g3ckO »

Sami wrote:Well when in doubt about something, Google it.
I have read and google it Sami :wink:
But I still cannot understand it. Most of the explaination are too confusing :? It doesn't show any simple example for me to understand.

I need to know how the timestamp will be inserted to the field that I created and how to call the latest data in my query.
Can you show me the example of the query or just suggest another link for me to google it :)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

try here: [mysql_man]date time function[/mysql_man]you'll potentially want [mysql_man]UNIX_TIMESTAMP[/mysql_man] and [mysql_man]NOW[/mysql_man] to insert the data.. and simple ORDER BY field_name DESC to get the newest ones first.
Draco_03
Forum Regular
Posts: 577
Joined: Fri Aug 15, 2003 12:25 pm
Location: Montreal, Canada

Post by Draco_03 »

I would suggest you to create a primary key auto increment (id) AND a timestamp.
Post Reply