How to add 1 to my "kijkers" table in my database. It is for counting how many times the site is visited

Question

I have this code and it should add 1 when you open the site but it adds 1 to the current number and doesn't update it. It should update it so the amount of visited can be shown correctly. "Kijkers" is 0 so when I open it it goes to 1 but after that it doesn't update it.

I tried changing some things but no succes.

<?php
    include 'db_conn.php';

    $find_counts = "SELECT * FROM visitors";
    $result = mysqli_query($conn, $find_counts);
    while ($row = mysqli_fetch_assoc($result)) 
    {
        //Kijkers = 0 in database
        $current_counts = $row['Kijkers'];
        $new_count = $current_counts + 1;
        $update_count = "UPDATE visitors SET Kijkers='$new_count'";

        echo $new_count;
    }

?>

I expected that it would update it and keep adding 1 on refresh or reloading the page.

You're not executing the update query. That query could also just be `UPDATE visitors SET Kijkers = Kijkers + 1;`. No need to retrieve the data first. — Robby Cornelissen, Sep 18 '19 at 08:57

score 3 · Accepted Answer · edited Sep 18 '19 at 09:27

There are two problems with your approach.

The first one is that you're not executing your update query. That could easily be fixed by adding a mysqli_query($conn, $update_count) statement.

The second problem is a little more insidious: your transaction is not atomic. Consider the following situation of two users accessing almost simultaneously. Let's say the value of Kijkers at that point is 10:

User A            | User B
------            | ------
select value 10   |
                  | select value 10
update value 11   |
                  | update value 11
                  |
                  V

So, even with two separate visits, your count only increased by one.

For this reason, it's better to just execute the update query based on the current value in the database:

$update_count = "UPDATE visitors SET Kijkers = Kijkers + 1";

if (mysqli_query($conn, $update_count)) {
    echo "<br>Record updated successfully";
} else {
    echo "Error updating record: " . mysqli_error($conn);
}

This is an atomic operation, and solves the problem described above.

It's a bad idea to display error messages to the user as it leaves your script vulnerable to exploitation. Please see this post on how to enable MySQLi exceptions: https://stackoverflow.com/a/22662582/1839439 — Dharman, Sep 18 '19 at 13:23

score 2 · Answer 2 · edited Sep 18 '19 at 09:12

2

You don't have to fetch the current value for Kijkers beforehand. You can just send an increment statement to the database.

Like this:

UPDATE visitors SET Kijkers=Kijkers + 1;

edited Sep 18 '19 at 09:12

GMB

216,147
25
84
135

answered Sep 18 '19 at 09:00

Maickel

54
2

How to add 1 to my "kijkers" table in my database. It is for counting how many times the site is visited

2 Answers2