duplicate entries when logging nr of failed login attempts mysql/php

I have this set up to log the amount of failed login attempts from different ip addresses. Probably unnecessary complicated.

mysql db set up:

 CREATE TABLE login_attempts (
  id int(20) NOT NULL auto_increment,
  ip varchar(20) NOT NULL default '',
  `time` datetime NOT NULL default '0000-00-00 00:00:00',
  nr char(4) NOT NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM
# -------

finding failed login attempts:

function ip()
{
if (!empty($_SERVER['HTTP_CLIENT_IP']))
    {
        $ip = $_SERVER['HTTP_CLIENT_IP'];
    } 
    elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR']))
    {
        $ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
    }
    else
    {
        $ip = $_SERVER['REMOTE_ADDR'];
    }
    return $ip;            
}   

$ip = ip(); 
$query = "SELECT `nr` from `login_attempts` WHERE `ip`='".$ip."' ";
$result = $conn->query($query);         
$user = $result->fetch_assoc();     
$failed_attempts = $user["nr"]; 

(... some code ... )

If login fails:

    if( $failed_attempts == 0 ){

                $query =开发者_运维问答 "INSERT INTO login_attempts (`ip`, `time`, `nr`) VALUES ('".$ip."', '".$time."', '1')";
                $result = $conn->query($query);             
                if (!$result){ trigger_error("mysql error: ".mysql_errno($result) . ": " . mysql_error($result)); return 0;  }

            } else {

                $query = "UPDATE login_attempts SET ip = '".$ip."', time = '".$time."', nr = nr + 1 "; 
                $result = $conn->query($query);             
                if (!$result){ trigger_error("mysql error: ".mysql_errno($result) . ": " . mysql_error($result)); return 0;  }

            } 

This somehow gives duplicate entries in my db. Same ip, different nr.. Why?

Your UPDATE query has no WHERE clause, so it updates every row in the table, setting the ip and time columns in all rows to those values.

$query = "UPDATE login_attempts SET time = CURRENT_TIMESTAMP, nr = nr + 1 WHERE ip = '$ip'";

You have to add the WHERE condition to the second SQL query. Otherwise it will update all rows in the table.