Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Match emails and Ips in a query

Posted on 2011-02-26
9
Medium Priority
?
296 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses

963 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