CODEWITHSUNDEEP
phphtmlmysqlsql
Dave
Dave

Reputation: 11

Adding a row to database if it doesn't exist

I have a databaste, visitorsCounter, and I want to add a new row everytime someone visits the page. I'm getting their IP, and then I check if there isn't a row in the databaste with their IP. If there isn't, I add a new row. 

$visitor_ip = getUserIP();
if(!isset($db)) $db = connect_db(); // connecting to the databaste with PDO
$userName = $_SESSION['username'];

$query = "SELECT entries from visitorsCounter WHERE ip=:ip";
$stmt = $db->prepare ( $query );
$stmt->bindParam ( ":ip", $visitor_ip);
$stmt->execute ();
$result = $stmt->fetch ( PDO::FETCH_ASSOC );

if($result != null){ // **** if there isn't a row with that IP ****
$addEntryQuery = "UPDATE visitorsCounter SET entries = entries + 1 WHERE ip=:ip";
$stmt = $db->prepare ( $addEntryQuery );
$stmt->bindParam ( ":ip", $visitor_ip);
$stmt->execute ();
}

EDIT: adding a row with user code:

    $userName = $_SESSION['username'];
$query = "INSERT INTO visitorsCounter(ip, entries, user)
    VALUES (:ip, 1,:user)
    ON DUPLICATE KEY UPDATE entries = entries + 1;
        ";
$stmt = $db->prepare ( $query );
$stmt->bindParam ( ":ip", $visitor_ip);
$stmt->bindParam ( ":user", $userName);
$stmt->execute ();


if(isset($_SESSION['username'])){
$addEntryQuery = "UPDATE visitorsCounter SET user = :user WHERE ip=:ip";
$stmt = $db->prepare ( $addEntryQuery );
$stmt->bindParam ( ":ip", $visitor_ip);
$stmt->bindParam ( ":user", $userName);
$stmt->execute ();
}

A visitor come in the website, and the user is null in the database. But when he logins, it stays null. Any idea why?

Upvotes: 0

Views: 102

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You should be doing this as one statement. The key idea is on duplicate key update.

So, add a unique index to your table so the database enforces one row per ip:

create unique index unq_visitorscounter_ip on visitorsCounter(ip);

Then, update/insert the value as:

INSERT INTO visitorsCounter(ip, entries)
    VALUES (ip, 1)
    ON DUPLICATE KEY UPDATE entries = entries + 1;

In addition to simplifying your code, this is the more correct way of expressing the logic. You code can have race conditions if the same ip is being inserted at the same time by different applications/threads.

Upvotes: 2

Ulrik
Ulrik

Reputation: 513

I think the reason you never get inside the "update" if-statement is that you shouldn't check for null, but instead check for a false value. According to the documentation for PDOStatement::fetch() you will get false returned on failure.

So your code should work better with a

if($result !=== false) {
    ...
}

Upvotes: 0

Related Questions