Jonathan W Jonathan W - 4 months ago 16
PHP Question

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();

Answer

COUNT is a reserved keyword in MySQL (and most RDBMS). You should not be naming your columns count. But if you did, you can escape the column name with backticks to tell MySQL to treat it as a name. Try the following query:

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

Update:

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
Comments