UBB.Developers Forums UBB.threads 7 Development How Do I? SQL query

I'd like to find out the SQL commands to get the following information:

a) Login name
b) User name (as shown on forums)
c) Registered on
d) Total Posts
e) Last On
f) Real email

on the specific groups.

Like i have a reg members group, admin group, guest group. etc.

Could I get all this info (a-f) on 1 command for each group? If so, what would the syntax be. Also is there any online reference for SQL query syntax that I could look up so i don't have to bother you poor folks?

thanks!

Info on MySQL queries can be found at www.mysql.com

First you need to know the number of the group you want to access the info for.

You can find the # of each group with this query:

SELECT * FROM w3t_Groups


Then, let's say you want to grab that info for group #5.

This query should do it:

SELECT U_LoginName,U_Username,U_Registered,U_Totalposts,U_LastOn, U_Email
FROM w3t_Users
WHERE U_Groups LIKE '%-5-%'

you rock. thanks.

Plus you can also add this to the end of the query...

ORDER BY U_Username

...to list the names alphabetically.

To reverse the default sorting order (ASCending), add DESC to that line as follows...

ORDER BY U_Username DESC

Reversing is more ideally suited to numeric values however, like total posts :-)

i notice that u_laston and u_registered show up as long number strings ie. (last on)1091097145 and (registered)983415311... easy way to convert these to actual readable dates?

Well, I can tell you that they are unix timestamps, so if you find something to convert those, then you should be all set.

awesome, thanks!