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

All web design discussion, including Ultimate Quiz MOD support.

Moderator: CricketMX Forum Moderators

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

Postby quahappy » Sat May 15, 2010 9:33 am

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
quahappy
Happy, not Quappy
Happy, not Quappy
 
Posts: 2266
Joined: Fri Oct 10, 2008 8:56 pm
Location: South Yorkshire, UK

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

Postby battye » Sat May 15, 2010 9:46 am

I think you will need to use a GROUP BY statement: http://www.w3schools.com/sql/sql_groupby.asp :)
CricketMX.com.. home of bat's, rat's and other farmyard animals...
My articles: view all articles by battye

"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
battye
Site Admin
Site Admin
 
Posts: 14249
Joined: Sun Jan 11, 2004 8:26 am
Location: Victoria Falls, Zimbabwe

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

Postby quahappy » Sat May 15, 2010 11:03 am

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
quahappy
Happy, not Quappy
Happy, not Quappy
 
Posts: 2266
Joined: Fri Oct 10, 2008 8:56 pm
Location: South Yorkshire, UK

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

Postby nesman » Fri May 28, 2010 1:12 am

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
User avatar
nesman
Miracle Worker
Miracle Worker
 
Posts: 1371
Joined: Thu Jan 15, 2004 8:17 am

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

Postby clapcreative » Tue Nov 24, 2015 10:54 am

Read the complete instructions here https://cloud.google.com/sql/docs/import-export?hl=en

I hope it will be helpful for you.
clapcreative
Greenhorn
Greenhorn
 
Posts: 4
Joined: Tue Nov 24, 2015 10:48 am
Location: Los Angeles, CA

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

Postby stuartspindlow1 » Thu Apr 20, 2017 11:11 am

Hi,
You can take help by visiting this link about Importing and Exporting Data. https://cloud.google.com/sql/docs/mysql/import-export/
stuartspindlow1
Greenhorn
Greenhorn
 
Posts: 1
Joined: Thu Apr 20, 2017 11:04 am
Location: London


Return to Web Design

Who is online

Users browsing this forum: No registered users and 5 guests

cron