mysql pdo hit counter

Question

I can't make the counter add 1 into the DB and the value increment every time when somebody go to the page... only works when is reloaded. Where is the error in my code? Can you help me with this issue?

I don't know if you need my entire html page, if you want I page it. the url have the id is show like this: post.php?id_blog=4

THe code:

<?php 
                    try {
                    $query = "SELECT id_blog, blog_titulo, blog, vistoblog FROM BLOG WHERE id_blog = ?";
                    $stmt = $conn->prepare( $query );
                    $stmt->bindParam(1, $_REQUEST['id_blog']);    
                    $stmt->execute();
                    $row = $stmt->fetch(PDO::FETCH_ASSOC);
                        $id_blog = $row['id_blog'];
                        $blog_titulo = $row['blog_titulo'];
                        $blog = $row['blog'];
                        $vistoblog = $row['vistoblog'];
                    }catch(PDOException $exception){ 
                    echo "Error: " . $exception->getMessage();
                    }
                    try{
                        $visto = $vistoblog + 1;
                        $sql = "UPDATE BLOG SET 
                        vistoblog = :vistoblog
                        WHERE id_blog = :id_blog";
                        $stmt = $conn->prepare($sql);
                        $stmt->bindParam(':vistoblog', $visto, PDO::PARAM_STR);
                        $stmt->bindParam(':id_blog', $id_blog, PDO::PARAM_INT);
                        $stmt->execute();
                        }catch(PDOException $exception){ 
                        echo "Error: " . $exception->getMessage();
                        }
                ?>

Answer 1

I recommend making your SQL just like this:

UPDATE BLOG SET 
vistoblog = vistoblog + 1
WHERE id_blog = :id_blog

The reason is to avoid a race condition. What if two people visit the page simultaneously, and both PHP threads read vistoblog value 123, add 1, and both try to increment it to value 124?

By using the expression above, you don't have to read the current value, and you avoid the chance of a race condition like that.