Im trying to make a join query between users and ips table to display the users that have a count of 3 or more ips per day
But from somewhat reason it doesn’t display anything. Just say that i have an error in my sql query near count.
https://pastebin.com/eayT6qzq
Any help or advice is welcomed. Thank you.
The AS keyword defines what the column name will be. So instead the other selects you have where you select some column from the ip table (ie ip.userid, which will be shown as userid), you only set the actual column name you wish to use.
Also note to always use placeholders and prepared/parameterized queries.
SELECT
COUNT(ip) AS countip,
u.id,
u.username,
u.class,
u.added,
u.last_access,
ip.userid,
ip.lastlogin
FROM
ips AS ip
LEFT JOIN users AS u ON ip.userid = u.id
WHERE
ip.lastlogin > ?
AND ip.type = 'login'
AND ip.countip > 3
GROUP BY
u.username
ORDER BY
u.username ASC
`Thx a lot. Ive good some progress but now it says ip is ambiguous because i have it in both tables 
make a decision which one to use then.
The ip from ips table
Ive tried count (ip.ip) but without success
Post your DB structure.
CREATE TABLE `ips` (
`id` int(10) UNSIGNED NOT NULL,
`ip` varbinary(16) NOT NULL,
`userid` int(10) DEFAULT NULL,
`type` enum('login','like') CHARACTER SET latin1 NOT NULL,
`lastlogin` int(11) NOT NULL DEFAULT '0',
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Ips table looks like this.
I have a more basic question. Are you trying to retrieve user data and any matching ip information or are you trying to retrieve ip information and any matching user data. The answer to this determines if your LEFT JOIN makes any sense.
Next, the WHERE term cannot use values that are produced in the SELECT term, because the WHERE term determines what rows are in the result set and you cannot produce any SELECT values until you have those rows. For what you are doing, you will need to use a HAVING … term. The error you are currently getting is probably due to this. COUNT(ip.ip) should be correct. However, ip.countip is not correct. countip is the alias name you have given the COUNT() … value. You would just use countip.
I’m trying to retrieve users that use more than 3ips per day, hence the count > 3
Userid from users table, matching the userid from ips table will detect from which row in the ips table it needs to do the count
Also the type is login because i have different types upon ip is registered into ips table
Then the primary table is the users table. The FROM … LEFT JOIN … part of the query would need to be -
FROM users u
LEFT JOIN ips ip ON u.id = ip.useridThe AS keyword is optional and can be left out. Using an alias name that’s one character shorter than the real name isn’t accomplishing much. You should use GROUP BY u.id (this will be faster than grouping by the username string.) If you want a count per day, you will need to add an expression to the GROUP BY term to get you individual row(s) per day from the lastlogin value. If you use a DATETIME value, instead of a unix timestamp, your data and query will be clearer and faster - a unix timestamp requires a conversion to do anything useful with it, such as find rows each day.
Ohh… So i mixed the tables around… Hmm i see… I will test it when i get back home and post results
Just tried it like this and keep receiving error that wrong syntax to use near having count
$secs = 1 * 86400;
$dt = sqlesc(time() - $secs);$query = “SELECT COUNT(ip.ip) AS countip, u.id, u.username, u.class, u.added, u.last_access, ip.userid, ip.lastlogin FROM users u LEFT JOIN ips ip ON u.id = ip.userid WHERE ip.lastlogin > $dt AND ip.type = ‘login’ AND HAVING COUNT(ip.ip) > 3 GROUP BY u.id ORDER BY u.username ASC”;
HAVING isn’t a normal condition as the others so you shouldn’t include it in the AND/OR condition block. Since it affects the data after grouping it should also be after the GROUP BY block.
You also already have the COUNT(ip.ip) so just refer to it with HAVING countip > 3.
Thank you. Working fine. Haven’t got any clue about the HAVING condition until now.