How to export list of users and IP's? (using SQL)

[quahappy]

Firstly, I will point out that I am fully aware that members IP's that are logged in phpBB3 does not give clear indication that this is the real IP. They could be using proxies, dynamic IP's, and so on. I'm just experimenting with exporting SQL data.

I'm trying to export a list (as PDF) of usernames and all the IP's these users have posted from. However, when I run the SQL query below, it's showing all the post IP's but not every username. In fact, out of the 254 registered members, it's only showing around 5 usernames.

Code: Select all

SELECT `poster_id` , `poster_ip` , `post_username`
FROM `quahappy_phpb2`.`phpbb_posts`

I can run this SQL query and of course it'll display a list of usernames and IP's they registered with:

Code: Select all

SELECT `user_id` , `user_ip` , `username`
FROM `quahappy_phpb2`.`phpbb_users`

So what query do I need to run so it includes the username and every IP the user has posted from?

Many thanks in advance for any assistance.

[battye]

I think you will need to use a GROUP BY statement: http://www.w3schools.com/sql/sql_groupby.asp

[quahappy]

Yay! The guide in the link you provided helped a great deal. Thanks battye.

Got the results I needed by running the following:

SELECT user_ip, username, poster_ip
FROM phpbb_users, phpbb_posts
WHERE user_id = poster_id
GROUP BY username, poster_ip

[nesman]

I've never actually crafted an SQL query, even though I've edited one or two. Is the syntax similar between MySQL and MS SQL?

I ask because there's a query that I'd like to create which would ease part of my job. We use security tokens for the vpn at work, and the management is unfriendly and very slow. I can generate a report of expiring tokens, but the format is ugly when you have several hundred entries. At best, I've been able to reformat it (via ugly hackery) into simple: date, lastname, firstname

I have the ability to generate custom reports, and I've figured out that it's based on some sort of SQL. What I'd like to do is create an sql query that will pull this information directly from the database and attach the Organizational Unit the person belongs to, so I can tell at a glance if the user belongs to me or the admin in Weinheim or Hong Kong, etc.

If you're curious, this is an example of the format and hackery:
http://groups.google.com/group/nlug-tal ... c60590d4e5