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

All web design discussion, including Ultimate Quiz MOD support.

Moderator: CricketMX Forum Moderators

Post Reply
User avatar
quahappy
Happy, not Quappy
Happy, not Quappy
Posts: 2266
Joined: Fri Oct 10, 2008 8:56 pm
Location: South Yorkshire, UK

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. :D
If you don't ask...
User avatar
battye
Site Admin
Site Admin
Posts: 14391
Joined: Sun Jan 11, 2004 8:26 am
Location: Australia
Contact:

I think you will need to use a GROUP BY statement: http://www.w3schools.com/sql/sql_groupby.asp :)
CricketMX.com in 2022: Still the home of bat's, rat's and other farmyard animals!

"OK, life [as you chose to define it] repeats until there are no more lessons to be learned." - nrnoble (June 12, 2005)
"the new forum looks awesome, it's getting bigger & better" - p2p-sharing-rules (11 Jan, 2008)
"Looks like CMX is not only getting bigger...but, also getting better!!" - moongirl (14 Dec, 2007)
User avatar
quahappy
Happy, not Quappy
Happy, not Quappy
Posts: 2266
Joined: Fri Oct 10, 2008 8:56 pm
Location: South Yorkshire, UK

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
:D
If you don't ask...
User avatar
nesman
Miracle Worker
Miracle Worker
Posts: 1453
Joined: Thu Jan 15, 2004 8:17 am
Contact:

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
Post Reply