Page Views Counting Problem

[thara]

I tried to count views to some particular page in my website. Tried to increment views through a database table.

 

This is the code I tried so far...

 

<?php
session_start();

require_once ('test.php');

$institute_id = 20;

$q = "SELECT views FROM page_views2 WHERE institute_id = $institute_id";
$r = mysqli_query ($dbc, $q);

$row = mysqli_fetch_array($r, MYSQLI_ASSOC);
$views = $row['views'];

if ( mysqli_num_rows( $r) == 1 ) {

 $up = "UPDATE page_views2 SET views = $views++ WHERE institute_id = $institute_id";
 $ur = mysqli_query( $dbc, $uq);

} else {

 $iq = "INSERT INTO page_views2 institute_id = '$institute_id', views = $views + 1";
 $rq = mysqli_query( $dbc, $iq);

}

?>

 

but this code is not working.. can anybody tell me where I am going wrong..?

 

thank you.

[vintox]

$views++

first place the value inside the string

after that it increments it.

 

do it like this:

++$views

 

or

$nViews = $views++

"UPDATE page_views2 SET views = $nViews WHERE institute_id = $institute_id";

[Andy123]

As vintox said, when you do $views++, it uses the variable first and then increments it. ++$views increments it first and then uses it. What I would do instead is to handle this directly in SQL.

 

UPDATE page_views2 SET views = views + 1 WHERE institute_id = $institute_id

 

Then, if the number of affected rows is 0, you can run the insert query. Then you save one database query.

 

Remember to watch out for SQL injection.

 

You may wonder if you could just use REPLACE INTO. I was thinking of this myself, but here is a quote from the MySQL documentation:

 

Values for all columns are taken from the values specified in the REPLACE statement. Any missing columns are set to their default values, just as happens for INSERT. You cannot refer to values from the current row and use them in the new row. If you use an assignment such as SET col_name = col_name + 1, the reference to the column name on the right hand side is treated as DEFAULT(col_name), so the assignment is equivalent to SETcol_name = DEFAULT(col_name) + 1.

Edited October 7, 2012 by Andy123

[thara]

can anybody tell me how can I do this in a single query with 'REPLACE INTO'?

Edited October 7, 2012 by thara

[xyph]

INSERT INTO tablename (counter, unique) VALUES (1, $unique_value)

ON DUPLICATE KEY UPDATE counter = counter + 1;

 

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

 

You should read the manual entry. There are some specific by-products of using this method.

[Andy123]

can anybody tell me how can I do this in a single query with 'REPLACE INTO'?

 

My quote above states that you cannot refer to values from the current row and use them in the new row. Based on that, it appears that you cannot accomplish it with REPLACE INTO.

 

INSERT INTO tablename (counter, unique) VALUES (1, $unique_value)

ON DUPLICATE KEY UPDATE counter = counter + 1;

 

http://dev.mysql.com...-duplicate.html

 

You should read the manual entry. There are some specific by-products of using this method.

 

Excellent, didn't think of that. Good solution!

Edited October 7, 2012 by Andy123