Solved

Match emails and Ips in a query

Posted on 2011-02-26
9
286 Views
Last Modified: 2012-05-11
Hi;
I am trying to find users with the same emaul and IP also finding duplicate accounts with a query and already wrote this but I do not know if this works correctly

SELECT
t_user.USERID,
t_user.EMAILID,
t_user.DOB,
t_user.IP_REGISTER,
t_user.PLAY_CHIPS
FROM
t_user
WHERE EMAILID = EMAILID and IP_REGISTER = IP_REGISTER
ORDER BY EMAILID, PLAY_CHIPS
0
Comment
Question by:babak62
  • 5
  • 4
9 Comments
 
LVL 24

Expert Comment

by:jimyX
ID: 34986310
You can try something like this:

SELECT t_user.USERID,t_user.EMAILID,t_user.DOB,t_user.IP_REGISTER,t_user.PLAY_CHIPS FROM t_user GROUP BY EMAILID, IP_REGISTER HAVING (COUNT(EMAILID) > 1), (COUNT(IP_REGISTER) > 1) ORDER BY EMAILID, PLAY_CHIPS
0
 

Author Comment

by:babak62
ID: 34986320
thanks a lot   but this is what I got

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' (COUNT(IP_REGISTER) > 1) ORDER BY EMAILID, PLAY_CHIPS' at line 2
0
 
LVL 24

Expert Comment

by:jimyX
ID: 34986337
Updated:

SELECT t_user.USERID,t_user.EMAILID,t_user.DOB,t_user.IP_REGISTER,t_user.PLAY_CHIPS FROM t_user GROUP BY EMAILID, IP_REGISTER HAVING (COUNT(EMAILID) > 1) and (COUNT(IP_REGISTER) > 1) ORDER BY EMAILID, PLAY_CHIPS
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:babak62
ID: 34986357
Thanks for the answer and please look at the sample record I added here.  Some of the records follos the query but in the middle you see results like this and that I can not explain also IP are totally differnt so are the emails.  Is there anyway to eleminate these records and get exact matches?
bebemon	IFIcFlYeWDsZBlRAXClYBwFzC1UMAgEfUVwr	1969-12-31 19:00:00	121.54.29.102	10000
khakie	IFIdDFodWhQIHFdXUDJaAQpzC1UMAgEfUVwr	1969-12-31 19:00:00	112.204.100.235	2320
fastrack_07	IFIdEEETVw8yXgZySidbAQsdEVsJ	1969-12-31 19:00:00	203.87.192.206	165
luis3	IFIdEFIcUBAFC1dHQS9cGxdsAFUBLRdQWlwpHQ0LXg==	1969-12-31 19:00:00	112.204.248.8	6091
MarLahPendergat	IFIDEV8TRgcMAF5tUjZBBwhBHUcBLRdQWlwpHQ0LXg==	1969-12-31 19:00:00	202.8.232.144	1820
nicco	IFIEBUEWWwwCGlVdVBkDViRKE1wLAkBSXV5oQwY=	1969-12-31 19:00:00	119.94.97.125	3765
www.putagalpu.com	IFIfEVocWyQUA1BbX2hQAQk=	1969-12-31 19:00:00	112.198.78.122	6320
fuuutaaagalpuuu	IFIfEVocWyQUA1BbX2hQAQk=	1969-12-31 19:00:00	112.198.78.80	6480
flwn	IFIfO0QbWjtdX3FLUi5cAUpQHVk=	1969-12-31 19:00:00	151.16.54.170	9430

Open in new window

0
 
LVL 24

Expert Comment

by:jimyX
ID: 34986679
Let me tell you what results this query returns may be your expectation is different but the query returns what it meant to do.
The above query will return only the records that are having same email & IP more than one time.

e.g:
USERID EMAILID DOB IP_REGISTER PLAY_CHIPS
    a          b           c           d                    e
     a          n           c           d                    e
    a          o           c           d                    e
     a          b           c           k                    e
    a          b           c           d                    e
    a          o           c           d                    e

so the result will be:

USERID EMAILID DOB IP_REGISTER PLAY_CHIPS
    a          b           c           d                    e
    a          o           c           d                    e

Is this what you are after because the result you got up there could be correct.
0
 

Author Comment

by:babak62
ID: 34987658
when I see the results there should not be a single record there also if the email is the same and the ip is the same, also a duplicate account,  but in this results I see records which are unique and my question is what are they doing there.
The goal with the query is to find players with more than one account created and in this case the query when it finds the player should show both accounts then a single record does not mean anything in the resullts.
0
 
LVL 24

Expert Comment

by:jimyX
ID: 34989249
If what I mentioned in my post (34986679) is what you want then the query works fine as I tested it with some data and it worked. Probably there is something else. Can you test the query in a smaller DB for easy tracking or can you try with another sample?
0
 

Author Comment

by:babak62
ID: 34989538
This is an another sample and as you see in the middle of it there is one record which is irrelavant to the rest and this is what I am talking about.
akin_na_yan_1018	dwFdJEoTXAsCQFJdXg==	122.53.175.50	
sea330	          dwFdJEoTXAsCQFJdXg==	63.95.64.254	
holiday1489	dwFdJFsdQAkMB10cUCle	63.95.64.254
SEE_24	          dwFdUAYybSUlIX4ccAl+	110.93.80.137
CobraMan	          dwFdUAYyTQUFAV4cUCle	112.203.22.112
tarantado-ako	dwFdUAZEdB0MBl5dHSVcAw==	112.203.17.74
LordOfGamblers	dwFdUAZEdB0MBl5dHSVcAw==	124.104.131.133
pinsan	          dwFdUAZEdB0MBl5dHSVcAw==	126.203.179.101
maw-tay	          dwFdUAZEdB0MBl5dHSVcAw==	126.251.138.184
eowfuh	          dwFdUAZEdB0MBl5dHSVcAw==	210.4.121.218

Open in new window

0
 
LVL 24

Accepted Solution

by:
jimyX earned 500 total points
ID: 35008815
Sorry to be late. I am afraid I am not seeing what you are trying to say, can you show me your data (the sample) and the result you expect from the query?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
maybe no no httpd.conf 6 48
Php recording post 4 53
PHP Installer 5 25
Can't Setup WAMP. Keep Being Told I've Got the Wrong Directory... 3 20
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question