I have a quick and hopefully easy question. It's in regards to how to set up a query. I have one table (users) for "users" that contains a user_id (auto increment) and a user_name. I have another table (entries) that holds "entries" in a type of journal. Say this has entry_id (auto increment), user_id, entry_text, and entry_time (contains a unix timestamp). What I want to do is get a list of "recent" entries to display on my web site. However, I want each user_name to only show up once. So I'll do a select on the entries table, order it by entry_time, and limit it to say 5. I want it to display the last 5 entries by unique users, rather than the last 5 entries period (IE: if one user updates 5 times in a row, I want it to discard all but their most recent). Is it possible to form an SQL query that will do this?
Read the MySQL manual about the JOIN statement.
I'm not 100% sure, but you can probably do a query on both tables at the same time,group the results by author, order by date, and limit to 5.
Again, I'm not sure, and I'm too lazy to try it out :-)
select distinct user_name from table where user_id='id';
This will return only one instance of user_name that occurs more than once.
How about adding a field to the users table that shows the last date/time they updated? Then you can just select the 5 most recent date/times from that table then get the highest entry for that user? Added bonus is that later on you can easily identify those users that haven't logged in for a while rather than having to query two tables?
Hi all,
Thanks for the replies. I ended up creating a "recent" table that simply gets a line inserted (or 'replaced') when a user updates. It holds the user_id, entry_id, and a time. Thanks for the help I appreciate the suggestions.
I decided against adding a field to the users table because it is already in use by a few other sections of my site, and I didn't want to have to mess with any of those.
