1

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 ??

Improve this question
4
  • dont use mysql_ functions, they are deprecated since php 5.5.0 Commented Nov 13, 2016 at 11:50
  • use mysqli_ php.net/manual/en/book.mysqli.php instead of mysql_ functions, or even better use PDO php.net/manual/en/book.pdo.php. Also, do not use root user to connect to database from applications. Commented Nov 13, 2016 at 11:53
  • Do you think views = views + 1 will work in $sql statement? Commented Nov 13, 2016 at 11:54
  • @AniketSahrawat Mate the object is't to get the accurate no. of views here ... neither to be efficient , ... its just to get my script working ! :) Commented Nov 13, 2016 at 11:56

2 Answers 2

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

You can't do where id = (select id, blog_title, etc...
sorry @Dekel copy pasted without thinking
@e4c5 thanks alot for the help , i appreciate that , one more supplimentary question if i am alllowed to $stmt->execute(array(":article"=>$article_id)); what is the last line of code really doing ?
@AlexanderSolonik it binds the key :article to an actual value. This way sql injection is prevented.
Glad to have helped.
2

Besides all the comments you already got regarding the mysql_ functions, bobby tables (that you will probably get soon), etc, notice that you choose your db before you run the query:

mysql_query("UPDATE tinyblog SET views = views + 1 WHERE id = {$article_id}" , $con );
mysql_select_db('tinyblog');
  1. Make sure you choose the db right after your connection.

  2. Add error handling to your code (so you will see the errors if they exists).

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

Improve this answer

4 Comments

Make sure you choose the db right after your connection. thats exactly what i've done . NO ?
no, it's not. notice that you send a query (mysql_query(...)) and no the next line you select the db (mysql_select_db(..)).
no what is meant here is that you should be calling mysql_select_db (is that what it is called, I really have forgotten) after the connect. You are executing a seclect but that's different from what's meant here. Alternatively you can pass in the dbname to the connect call
much more polite answer than mine :) +1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.