Here is how my Sample data looks like
hwid: 1502e3c3-b49c-4907-92fd-2ffac2d50ace
ip: 68.197.140.109
character name: none
hwid: 1502e3c3-b49c-4907-92fd-2ffac2d50ace
ip: 219.100.37.236
character name: none
hwid: 1502e3c3-b49c-4907-92fd-2ffac2d50acd
ip: 68.197.140.109
character name: kalgame
Now say I'm logging in with this hwid 1502e3c3-b49c-4907-92fd-2ffac2d50ace and this ip address 219.100.37.236
which has a link to using a previous IP address of 68.197.140.109
and that IP address has a link to a 3rd hwid that has a character name : kalgame.
Thats what I need to do cross referencing with all IP's linked to all hwid's and then check if any of those hwid's have a character_name.
If they have a character_name associated to them return them in a SELECT DISTINCT COUNT(totalMatchesOfUniqueCharacterNames)
I also need to check if the current HWID or current IP you are using has 3 days trial period, if it does then also add it to the SELECT DISTINCT COUNT.. then I could easily check in PHP if SELECT DISTINCT COUNT is greater then 1, then abort giving out any more free trials to the specific user as he already logged in before with a character_name, and also maybe had a 3 day trial period before hand on either his PC hwid or his IP address, note I would also like to exclude blank character_name's as those players didn't login with a character yet.. so they don't count. I also have to match the locale which is which game are they playing if they are playing a different game then they also don't count.
Here is my attempt at cross referencing I been working at this for 6 hours and today is my first day using SQLite or any SQL in general so I have no clue what i'm doing
Here is my table schema DDL for both tables.. they are also both in the same database file.
CREATE TABLE ips (
hwid VARCHAR,
ip VARCHAR,
character_name VARCHAR,
locale VARCHAR,
botver VARCHAR,
reg_time DATETIME,
last_time DATETIME,
ping_count INTEGER DEFAULT 0
);
CREATE TABLE users (
hwid VARCHAR UNIQUE,
timestamp INTEGER,
daysToUse INTEGER,
pcLimit INTEGER,
comment VARCHAR
);
Here is my attempt at putting it all together
SELECT users2.hwid,
ips2.character_name,
ips2.ip
FROM users AS users2
INNER JOIN
ips AS ips2 ON ips2.hwid = users2.hwid
WHERE EXISTS (
SELECT 1
FROM users
INNER JOIN
ips ON ips.hwid = users.hwid
WHERE ips.character_name != '' AND
ips.locale = ips2.locale AND
(ips.hwid = '1502e3c3-b49c-4907-92fd-2ffac2d50ace' OR
ips.ip = '219.100.37.236') AND
EXISTS (
SELECT 1
FROM users
INNER JOIN
ips ON ips.hwid = users.hwid
WHERE ips.character_name != '' AND
ips2.ip = ips.ip AND
ips2.locale = ips.locale
)
)
OR
(ips2.hwid = '1502e3c3-b49c-4907-92fd-2ffac2d50ace' AND
users2.daysToUse = 3) OR
(ips2.ip = '219.100.37.236' AND
users2.daysToUse = 3);
Here is what I left in the production website.. which isn't that good.. doesn't do cross referencing that well skips certain columns but atleast it kinda works unlike the top code above which just sounds like it should work but doesn't work at all.
SELECT COUNT(DISTINCT users2.hwid)
FROM users AS users2
INNER JOIN
ips AS ips3 ON ips3.hwid = users2.hwid
WHERE ips3.character_name = (
SELECT ips.character_name
FROM users,
ips AS ips2
INNER JOIN
ips ON ips.hwid = users.hwid
WHERE ips.character_name != '' AND
ips.locale = ips2.locale AND
(ips.hwid = '$HWID' OR
ips.ip = '$IP')
)
OR
(ips3.hwid = '$HWID' AND
users2.daysToUse = 3) OR
(ips3.ip = '$IP' AND
users2.daysToUse = 3);
If anyone can help me out i will be forever grateful :D
Since your subqueries share same FROM and JOIN clauses, simplify to a compact WHERE clause:
SELECT i.ip, i.character_name, u.hwid, u.daysToUse, i.locale
FROM users u
INNER JOIN ips i
ON i.hwid = u.hwid
WHERE (i.hwid = '1502e3c3-b49c-4907-92fd-2ffac2d50ace'
AND u.daysToUse = 3)
OR (i.ip = '219.100.37.236' AND u.daysToUse = 3)
ORDER BY i.last_time