php and mysql hit counter, counting once only

Question

I've tried several methods, but i can only get it to count once. I am thinking there must be something wrong with my database? When i reset my database coloumn "count" to 0, and refresh the page, it counts to 1, but after that no more.

<?php
function update_count() {
    $query = "SELECT `count` FROM `hits`";
    if($query_run = mysql_query($query)) {
        $count++;
        echo $count;
        $query_count = "UPDATE `hits` set `count`='$count'";
        mysql_query($query_count);
    }
}
update_count();
?>

You're only checking if the query ran successfully, you're not returning any data from the query, so `$count` doesn't evaluate to anything — Luke, Sep 09 '14 at 09:00
its a hit counter, i want it to add 1 each time someone enters my website, or refreshes — kensil, Sep 09 '14 at 09:01
You're running the query to get the count value but then not using it. You'll need to use the count that is returned by the query, increment it and store it again. — gratz, Sep 09 '14 at 09:03

score 3 · Accepted Answer · answered Sep 09 '14 at 09:03

3

That's because when you refresh the page $count is lost, you need to reselect it from the database:

$query = "SELECT `count` as cnt FROM `hits`";
$results = mysql_query($query);
$count = mysql_fetch_array($result); // take only the first row.
print_r($count['cnt']);

A simple note, mysql_* functions are deprecated use mysqli_* or prepared statements.

answered Sep 09 '14 at 09:03

Ende Neu

15,581
5
57
68

1

Thanks, i think i understood, i added $count = mysql_result($query_run, 0, 'count'); before the count++ so it knows the previous value? am i correct? – kensil Sep 09 '14 at 09:13

score 2 · Answer 2 · answered Sep 09 '14 at 09:00

When you refresh your page (or run your script again), there is no "direct" memory of the previous execution. Your $count value is lost when the script ends. (And even if you were calling update_count several times in your code, $count is set only in the scope of your function)

If you want to achieve something similar, you will have to store that count somehow : in a cookie, in the database or (but probably the worst way) directly in a file.

score 2 · Answer 3 · edited May 23 '17 at 12:21

You aren't assigning the count in your database to a variable, rather just incrementing a new variable (which should throw an error - Notice: Undefined variable: count... - have you error checking disabled?) without actually assigning your database value. Hence why you always get 1 - you're creating a new value of 0 and adding 1 to it every time you refresh.

You would need -

$row = mysql_fetch_row($result);
$count = $row[0];
$count++;

Note I would recommend you should not use count as a field name in your database as it's a reserved word in both MySQL and PHP. It can cause unexpected errors/output. Try having a look at this thread.

Try this (assuming you have PDO and your connection declared), as it has already been pointed out the logic you're using is faulty with your queries.

function updateCount() {

# Update count
$queryUpdate = $db->prepare('UPDATE hits SET hitcount = hitcount + 1');
$queryUpdate->execute();

# Get the updated count value
$queryCount = $dbo->('SELECT hitcount FROM hits');
$queryCount->execute();

    # Check that a result is returned and assign result 
    if ($result = $queryCount->fetch(PDO::FETCH_OBJ) {
    return $result->hitcount;
    }

}

# Echo returned value
$value = updateCount();
echo $value;

If you get the time, do look into mysqli as mentioned above by the top answer, or preferably PDO. It's worth it and it's easier to write/read too. — iamgory, Sep 09 '14 at 09:33

php and mysql hit counter, counting once only

3 Answers3