How do I update a row in MySQL using php?

Question

How do I update the row $pageviews by +1 each time someone visits my page. The table name is news and the row to be updated is $pageviews whose default value is +1. Should I use the update function? And how do I implement it? Below is my code. Can you show a demonstration using these data?

$data = mysql_query("SELECT * FROM news") or die(mysql_error()); 
while($info = mysql_fetch_array( $data )) 
{  
    $id = $info['id'];
    $pageviews = $info['pageviews'];
}

Have you tried something like UPDATE news SET pageviews = pageviews + 1 ?? — Shankar Narayana Damodaran
– Shankar Narayana Damodaran, Commented Mar 20, 2014 at 9:25
Oligatory "mysql is deprecated, use mysqli_* functions" comment. php.net/manual/en/book.mysqli.php — pdu
– pdu, Commented Mar 20, 2014 at 9:25
Just pass the query I gave it on your mysql_query() function.. Note : You are using an obsolete database API. — Shankar Narayana Damodaran
– Shankar Narayana Damodaran, Commented Mar 20, 2014 at 9:31
I'm now changing my functions to mysqli_* function. Thank you. — Susan
– Susan, Commented Mar 20, 2014 at 9:33

Yang · Accepted Answer · 2014-03-20 10:39:12Z

1

All you want to do is to increment pageviews value each time that page renders. All you need for that:

1) Get the current value

2) Run UPDATE query updating current value + 1

To do so, you can write a function that would look like as,

function inc_page_views($id) {

  $res = mysql_query(sprintf("SELECT * FROM `news` WHERE `id` ='%s'", mysql_real_escape_string($id))); 

  $data = mysql_fetch_array($res);

  // Target view count
  $target = $data['pageviews'];

  $query = sprintf("UPDATE `news` SET `pageviews` = '%s' WHERE `id` ='%s'", $target + 1, $id);

  return mysql_unbuffered_query($query);
}

And then, when rendering a page, you can simply call inc_page_views(..here page id..) and it will do the rest

edited Mar 20, 2014 at 10:39

answered Mar 20, 2014 at 9:49

Yang

8,7408 gold badges35 silver badges61 bronze badges

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

Comments

Ananth · Accepted Answer · 2014-03-20 09:41:26Z

0

use this query

mysql_query("Update news SET pageviews = pageviews + 1 ");

or

store the current page id in $current_page_id.

 mysql_query("Update news SET pageviews = pageviews + 1  where id = '$current_page_id' ");

answered Mar 20, 2014 at 9:41

Ananth

1,5823 gold badges17 silver badges36 bronze badges

2 Comments

Susan Over a year ago

Could you please clarify how I can store the current page id in $current_page_id ?

Ananth Over a year ago

your news table will have unique id, which you will send in url. After that, url will be like example.com/news.php?id=33. So in this case, id is 33. So in the new.php, you must write the code like. $current_page_id = $_GET['id']; then you have to use the above 2nd query

Collectives™ on Stack Overflow

How do I update a row in MySQL using php?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related