CODEWITHSUNDEEP
phpmysqlpdo
Jon W
Jon W

Reputation: 13

Using IPs in php and mysql

I'm trying to create a system (well it's a project) where I can log users IP addresses when they do certain activities. After some research, I've found that ip2long is needed to convert into the database. I've got a database called ips (unsigned) and the main columns are ip and count.

Mysql Screenshot:

enter image description here

This is what I've got currently:

$ip=$_SERVER['REMOTE_ADDR'];
$ip = ip2long($ip);

$stmt = $DB_con->prepare("INSERT INTO ips (ip, count) VALUES  (:addr, 1) ON DUPLICATE KEY UPDATE count = count + 1");
$stmt->bindparam(":addr", $ip);
$stmt->execute();

This works fine. But I can't seem to be able to retrieve the values from the database and display them. I'm probably doing this horribly wrong but have no idea. After this statement, I then need to convert longtoip. I'd appreciate some help as this just gives me an Error 500:

$stmt = $DB_con->prepare("SELECT ip , count FROM ips WHERE 1")
$stmt->execute();

Upvotes: 1

Views: 47

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

There is nothing wrong with the following query, as you can see from the SQL Fiddle link given below the query:

SELECT ip, count FROM ips WHERE 1

SQLFiddle

However, if you really did intend to use COUNT as a function, then your query needs a GROUP BY clause:

SELECT ip, COUNT(*) AS ipCount
FROM ips
GROUP BY ip

As general good practice, you might want to avoid naming your columns count, or after any other MySQL function, because it could lead to confusion about what your query is actually doing (and you may have fallen prey to this yourself).

Upvotes: 4

Related Questions