mysql - Incrementing value in PHP script not working

Question

I have the following PHP script to update the blog view in my database,

<?php   include('header.php');  ?>
<?php
    $article_id = $_POST['article'];
    // echo $article_id;
   $dbhost = 'localhost';
   $dbuser = 'root';
   $dbpass = 'password';
   $con = mysql_connect($dbhost, $dbuser , $dbpass);
   $sql = 'SELECT id,blog_title, blog_body, views FROM tinyblog where id="'. $article_id .'" ';
   // UPDATE VIEWS.
   mysql_query("UPDATE tinyblog SET views = views + 1 WHERE id = {$article_id}" , $con );
      mysql_select_db('tinyblog');
      $retval = mysql_query( $sql, $con );

      if(! $retval ) {
         die('Could not get data: ' . mysql_error());
      }

   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
   ?>

   <div class="article-blog-indiv">
       <?php    
          echo '<h1>'. $row['blog_title'] .'</h1>';   
        echo '<p>'. $row['blog_body'] .'</p>';
       ?>
   </div>       
<?php           
   }
?>
<?php   include('footer.php');  ?>

It is the following line of code that actually updates the views field in my database:

mysql_query("UPDATE tinyblog SET views = views + 1 WHERE id = {$article_id}" , $con );

Now this line of code does't seem to work , as every time i go back and check in phpmyadmin, i see that the views field is still 0 , But when i insert the following statement directly for testing my my phpmyadmin:

mysql_query("UPDATE tinyblog SET views = views + 1 WHERE id = 1);

I see an increment in the views field , why is this happening ??

Answer 1

A few things that need to be fixed. first you are using mysql when you should be using mysqli or PDO. Second you are using post data without any escaping at all. Thirdly, you don't need this select and update. You can do it in a single statement.

$query = "UPDATE tinyblog SET views = views + 1 WHERE id = (SELECT id FROM tinyblog where id=:article)"
$db = new PDO('mysql:host=localhost;dbname=mydb', 'username', 'password');
$stmt = $db->prepare($query);
$stmt->execute(array(":article"=>$article_id));

What we are doing here is creating a prepared statement with one place holder. We have named it as :article but it could have been left as ? instead.

Then when the query is executed you need to fill in the missing bits by passing in parameters. That's what we are doing in the last step with array(":article"=>$article_id)

Since it's a named parameter, we use an associative array. Alternatively you could have called execute without any parameters if you had called bindParam first.