count profile views

[LloydFarrell]

Hi all, Im looking for some help with a profile view counter in php.

What im trying to do is update mysql db by 1 everytime a profile is viewed.

ATM when a profile is viewed, my db is updating every profile by 1

please review my code below and point me in the right direction as to where i am going wrong.

//update profile views//
$sql="SELECT * FROM memberst WHERE id='".$_SESSION['id']."'";
$result=mysql_query($sql);

$rows=mysql_fetch_array($result);
$counter=$rows['counter'];
// if have no counter value set counter = 1
if(empty($counter)){
$counter=1;
$sql1="INSERT INTO memberst(counter) WHERE id='$id' VALUES('$counter')";
$result1=mysql_query($sql1);
}
// count more value
$addcounter=$counter+1;
$sql2="update memberst set counter='$addcounter'";
$result2=mysql_query($sql2);
//end of update views//

cheers,

[nonshatter]

Hey Lloyd. You're on the right track. However, from a quick glance, it looks as though your sql statement is causing you problems. This is how I would do it:

<?php
$id = $_SESSION['id'];
$id = addslashes($id);
$sql=mysql_query("SELECT * FROM memberst WHERE id='$id'");
$result=mysql_fetch_assoc($sql);

$counter = $result['counter'];

if ($counter == 0) {
    $counter = 1;
    $sql1= mysql_query("UPDATE memberst SET counter='$counter' WHERE id='$id'");
} else {
    $addcounter=$counter+1;
    $sql1=mysql_query("UPDATE memberst SET counter='$addcounter' WHERE id='$id'");
}
?>

Not sure if this will work exactly as you want it, but give it a go and get back to me. All the best,

Nonshatter

[mrcniceguy]

@nonshatter i think it should be like this from ur codes

if (!$counter) {
    $counter = 1;
    $sql1= mysql_query("UPDATE memberst SET counter='$counter' WHERE id='$id'");
} else {
    $addcounter=$counter+1;
    $sql1=mysql_query("UPDATE memberst SET counter='$addcounter' WHERE id='$id'");
}

or

if (empty($counter)) {
    $counter = 1;
    $sql1= mysql_query("UPDATE memberst SET counter='$counter' WHERE id='$id'");
} else {
    $addcounter=$counter+1;
    $sql1=mysql_query("UPDATE memberst SET counter='$addcounter' WHERE id='$id'");
}

[mschroeder]

This can be handled with a single query and without all of the conditionals and select queries to determine the current counter value.

INSERT INTO memberst (id, counter) VALUES ('', 1) ON DUPLICATE KEY UPDATE counter = counter+1

If the record exists increment it by 1, if the record does not exist create the record and set its value to 1

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

[atullalit91]

I have checked this code, use this one. It is working on my system...

<?php
$host="localhost"; // Host name
$username="root"; // Mysql username
$password=""; // Mysql password
$db_name="test"; // Database name
$tbl_name="counter"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect to server ");
mysql_select_db("$db_name")or die("cannot select DB");
$sql=("SELECT * FROM $tbl_name");
$result= mysql_query($sql);
$rows=mysql_fetch_array($result);
$counter=$rows['counter'];
// if have no counter value set counter = 1
if(empty($counter)){
$counter=1;
$sql1="INSERT INTO $tbl_name(counter) VALUES('$counter')";
$result1=mysql_query($sql1);
}

echo "You 're visitors No. ";
echo $counter;

// count more value
$addcounter=$counter+1;
$sql2="update $tbl_name set counter='$addcounter'";
$result2=mysql_query($sql2);

mysql_close();



?>
<br><hr>
CREATE TABLE `counter` (
`counter` int(9) NOT NULL default '0'
);
<br><br><br><br><br><br>
t_Variable error conveys any missing paraenthesis like ; or ( etc

Required database command needs to run for creating the table.

Goodluck and please vote for me after getting success.

[mschroeder]

Having multiple queries where there is a MySQL syntax for this particular use case is pointless. Use the mysql ON DUPLICATE KEY UPDATE syntax and you don't have to worry about conditionally deciding if the record should be inserted or updated, as MySQL does this for you.

$sql = 'INSERT INTO memberst (id, counter) VALUES ('.$_SESSION['id'].', 1) ON DUPLICATE KEY UPDATE counter = counter+1; SELECT * FROM memberst WHERE id = '.$_SESSION['id'].' LIMIT 1;';
$result = mysql_query($sql);
$rowset = mysql_fetch_assoc($result);

That first inserts or updates the record for the user with $_SESSION and then executes the same select statement as I'm assuming you need the data from that table to display somewhere. If not you can remove the "SELECT * FROM......;" from that query string.

[LloydFarrell]

Hi all,

Many thanks for youre help with this, but its still not updating the users profile im viewing,
when i check mysql DB each counter = the same number ie. it looks as if each profile has been viewed the same amount of times.

when i view a profile, my URL is members/view-profile.php?id=1

This brings back all the information i have coded for the user from the mysql DB.

Is there a way to GET the ID=1 from the URL and update JUST that profile counter field by +1

[nonshatter]

Is there a way to GET the ID=1 from the URL

$urlid = $_GET['id'];

$query = mysql_query("UPDATE memberst SET counter='$counter' WHERE id='$urlid'");

Something like this?