| Author |
 Topic  |
|
|
NorwegianViking
Junior Member
Denmark
119 Posts |
 Posted - 09 February 2004 : 05:17:29
|
In fighting misbehaveour I have found these queries useful.
Sorted by name, they will list all IP adresses used by each user.
Sorted by IP, they will list all IP adresses and which users that have posted on this.
Note 1: It is only tested with Access, but should also work with SQL server. (and MySql)
Note 2: The code is for use directly in the DB's Query tool.
SELECT DISTINCT Ip, M_id, Name
FROM
(
SELECT DISTINCT FR.R_IP as Ip, FR.R_AUTHOR as M_id, FM.M_NAME as Name
FROM FORUM_MEMBERS FM INNER JOIN FORUM_REPLY FR ON FM.MEMBER_ID = FR.R_AUTHOR
UNION ALL
SELECT DISTINCT FT.T_IP, FT.T_AUTHOR, FM.M_NAME
FROM FORUM_MEMBERS FM INNER JOIN FORUM_TOPICS FT ON FM.MEMBER_ID = FT.T_AUTHOR
UNION ALL
SELECT M_LAST_IP as Ip, MEMBER_ID as M_id, M_NAME as Name
FROM FORUM_MEMBERS
UNION ALL
SELECT M_IP as Ip, MEMBER_ID as M_id, M_NAME as Name
FROM FORUM_MEMBERS
)
ORDER BY Name
Use ORDER BY Name to list all IP adresses used by each user.
Use ORDER BY IP, to list all IP adresses and which users that have posted on this.
|
kind regards
NorwegianViking
Mod installation for beginners | User registration problems?
|
Edited by - NorwegianViking on 09 February 2004 05:19:38 |
|
|
sr_erick
Senior Member
USA
1318 Posts |
Posted - 09 February 2004 : 11:48:46
|
| Is this refering to my post in non-forum code? |
Erick
Snowmobile Fanatics
|
 |
|
|
redbrad0
Advanced Member
USA
3725 Posts |
|
|
Weed
Starting Member
18 Posts |
Posted - 09 February 2004 : 12:33:56
|
| This would make an excellent mod to have. I'm sure I am not the only person who has users that sign up over and over. |
Edited by - Weed on 09 February 2004 17:54:04 |
|
|
|
NorwegianViking
Junior Member
Denmark
119 Posts |
Posted - 10 February 2004 : 08:39:41
|
sr_eric,
Absolutely. I posted a link to this topic in the one you started.
I just didn't want to clutter that topic with this.
Except that I skip the Reply_ID, as I see no use for it.
I don't know enough ASP (yet) to make this a MOD.
I could always get it together into a sqlString, but I havn't studied how to show the records with ASP yet.
|
kind regards
NorwegianViking
Mod installation for beginners | User registration problems?
|
|
|
|
Carefree
Advanced Member
Philippines
4207 Posts |
|
|
Weed
Starting Member
18 Posts |
Posted - 15 February 2004 : 00:24:33
|
| Well done Carefree. Works like a charm. Maybe I shoud have explained myself a bit better though. What I was thinking of is a mod that could advise you of members who have signed up 2 or 3 times with different logins by looking for duplicate ip's. |
|
|
|
Carefree
Advanced Member
Philippines
4207 Posts |
Posted - 15 February 2004 : 10:27:30
|
I'll write that one next (unless someone beats me to it).
As written, the current modification is for Snitz v 3.4.04 and uses a single change to pop_profile.asp (adds a link for moderators and admins) which will give you a quick report of all IPs used by a particular user.
This will come in handy if you ever intend to ban someone.
Get a copy here: http://CarefreeComputing.net/forum/mods/UniqueIPs.zip |
|
|
|
Weed
Starting Member
18 Posts |
Posted - 15 February 2004 : 14:58:43
|
By the way I tried registering on your forum but I get:
quote:
Microsoft JET Database Engine error '80040e14'
The INSERT INTO statement contains the following unknown field name: 'M_AVATAR'. Make sure you have typed the name correctly, and try the operation again.
/forum/register.asp, line 602
I did not choose an avatar but was forced to choose a size.
|
|
|
|
miperez
Junior Member
Spain
243 Posts |
Posted - 16 February 2004 : 12:36:19
|
I downloaded it, and it works great!
Just one comment: I really don't understand why you call it "User's unique IP MOD", it might give users a false impression about what it is going to do, like forcing them to use only one address or something; I'd call it "User's IP list MOD", or similar.
Just as a suggestion for a really good MOD, anyway  |
Best Regards
Mikel Perez
"Hell is the place where everything test perfectly, and nothing works"
|
Edited by - miperez on 16 February 2004 12:40:22 |
|
|
|
Carefree
Advanced Member
Philippines
4207 Posts |
Posted - 16 February 2004 : 15:23:04
|
Norwegian Viking's original script works fine incorporated into an ASP page with a link; however, it displays a list of all IPs used and doesn't filter out the unique ones. It does show you (by means of multiple names with the IPs used) any duplicates. I'm trying to write a subscript which will remove any IPs used only once but so far I've not had a great deal of success LOL.
If anyone wants to take a stab at writing the filtering subscript, please feel free. The file which needs it is pop_dupeIP.asp.
If you use a WHERE script like this one, then you can't rename the IP, name, ID info above it and the whole thing will have to be redone.
strSql = strSql & " WHERE ((M_IP=FT.T_IP AND FT.T_AUTHOR<>MEMBER_ID) OR (FT.T_AUTHOR<>MEMBER_ID AND M_LAST_IP=FT.T_IP) OR (FR.R_IP=M_LAST_IP AND MEMBER_ID<>FR.R_AUTHOR) OR (FT.T_AUTHOR<>MEMBER_ID AND FR.R_IP=FT.T_IP))"
http://carefreecomputing.net/forum/mods/MemberIPSearch.zip
This zip contains both routines and a readme which shows how to add links to both in pop_profile.asp. |
Edited by - Carefree on 16 February 2004 15:44:39 |
|
|
| |
Topic |
|
Topic Locked
