php - WHERE doesn't recognize and INSERT throws unique exception -

php - WHERE doesn't recognize and INSERT throws unique exception -

i read of answers here , "mind-locked". new , don't work @ this, taught myself on lastly 43 years. converted piece of code nail counter wrote other day, , counts number of times ip runs denied access page. code checks see if ip in database table (unique). if is, increments , updates. if isn't there, inserts value of 1.

all well, noticed no increment when testing. isolated , found won't recognize table column value update it, , throws exception when tries insert new value because unique.

i have looked , looked , cannot seem understand why works fine in hitcounter, fails miserably here?!?

$ip = $_server['remote_addr']; $ip = preg_replace('#[^0-9\.]#','',$ip); $db_table = 'deniedcounter'; echo 'enter denied_record.php<br />'; //$ip = str_replace('.','x',$ip); function setdeniedcounter($ip, $db_handle, $db_table){ $hits = null; $ip = "'".$ip."'"; try{ echo "select * $db_table ip = $ip".'<br />'; $stmt = $db_handle->query("select * $db_table ip = $ip"); $row_count = $stmt->rowcount(); echo $row_count.' = rows selected.<br />'; } catch(pdoexception $e){ db_exception_handler($e); } if($row_count == 1){ $row = $stmt->fetch(pdo::fetch_assoc); echo $row['ip'].' '.$row['hits'].'<br />'; $hits = $row['hits']; $ip = $row['ip']; $hits++; try{ $stmt = $db_handle->prepare("update $db_table set hits=? ip=?"); $stmt->execute(array($hits, $ip)); $affected_rows = $stmt->rowcount(); echo $affected_rows.'<br />'; } catch(pdoexception $e){ db_exception_handler($e); } exit(); } elseif($row_count == 0){ $hits = 1; try{ $stmt = $db_handle->prepare($sql = "insert $db_table(ip, hits) values(?, ?)"); $stmt->execute(array($ip, $hits)); $affected_rows = $stmt->rowcount(); //echo $affected_rows.'<br />'; } catch(pdoexception $e){ db_exception_handler($e); } } //echo 'hits = '.$hits.'<br />'; if(isset($hits)){return $hits;} } $db_handle = db_open($db_host, $db_name, $db_username, $db_pwd); if(strlen($ip) > 6){$da_hits = setdeniedcounter($ip, $db_handle, $db_table);} if(!isset($da_hits)){$da_hits = setdeniedcounter('alert', $db_handle, $db_table);} $db_handle = db_close($db_handle); echo 'exit denied_record.php<br />'; exit();

==================== output:

enter denied_record.php select * deniedcounter ip = '127.0.0.1' 0 = rows selected. sqlstate[23000]: integrity constraint violation: 1062 duplicate entry ''127.0.0.1'' key 'ip' exit denied_record.php

mysql has special operator you. no need select first - insert code:

insert $db_table(ip, hits) values(?, ?) on duplicate key update hits=hits+1

so, function have be

function setdeniedcounter($ip, $db_handle, $db_table) { $sql = "insert $db_table(ip, hits) values(?, 1) on duplicate key update hits=hits+1"; $stmt = $db_handle->prepare(); $stmt->execute(array($ip)); }

bu if want hits returned - need select them though

php mysql sql pdo