[thelist] mysql query help

Mark Joslyn Mark.Joslyn at SolimarSystems.com
Fri Aug 17 12:53:35 CDT 2007


I have a table that displays a list of usernames. Then on an onclick event,
I reveal a row to show the list of items a particular user has accessed.

I get the list of users with this query:

"SELECT username, COUNT(username) AS uCount, item, COUNT(item) AS iCount,
date, MAX(date) AS myDate FROM myTable GROUP BY username ORDER BY username"

This gives me the following structure:

Username
  - item (item)
  - number of times accessed (iCount)
  - last date accessed (myDate)

Since a user can access multiple items, I need to return item, iCount,
myDate for each item - not just one.

So it would look like this:

Username
  - item #1
  - number of times accessed #1
  - last date accessed # 1

  - item #2
  - number of times accessed #2
  - last date accessed #2

  - etc...

Is there a way to write the above query differently to achieve this? I would
like to not have to POST back to myself to capture what row the user clicked
then have to execute another query. It seems like it should be possible -
just cannot wrap my head around it.

Your help is greatly appreciated.

Thanks,

markJ





More information about the thelist mailing list