CODEWITHSUNDEEP
phpmysqlsqlsearch
user1009453
user1009453

Reputation: 707

Counter in mysql doesn't work

I'am trying to implement a "most popular searches" function on a web site using a simple counter. The idea is that the first time a search is made, the query is stored in a special table. After that the count number increments by one every time that same search is made. I think I do exactly as I should but the code doesn't seem to work. The count value stays on NULL in the table. Here's my code. "Searchny" is the table where I store the searches. Any pointers much appreciated! 

$search_result = mysql_query("SELECT * FROM searchny WHERE sok='$sokt'");

if(mysql_num_rows($search_result) == 0 ) { 
    $sql = "INSERT INTO searchny (sok, dt) VALUES ('" . $sokt . "', NOW());"; 
    mysql_query($sql);
}
else {
    mysql_query("UPDATE searchny SET count = count+1 WHERE sok='$sokt'");
}

Upvotes: 1

Views: 128

Answers (3)

Marcus Adams
Marcus Adams

Reputation: 53870

Your problem is most likely that count is NULL. Anything added to NULL is NULL.

Try this query:

SELECT NULL + 1

The result will be:

NULL

You could set your default value for the count field to 0 instead.

However, here's a simpler way to implement this. Note that I've excluded the date field because I don't know what the requirements for the date are:

Table searchny:
---------
sok
count
---------
primary_key (sok)

Then do it all in one statement:

INSERT INTO searchny (sok, count) VALUES ($sokt, 1)
  ON DUPLICATE KEY UPDATE count = count + 1

If it's a new sok value, then a new row is inserted and the count is set to 1. If the sok value already exists, then the record's count is incremented by 1.

Upvotes: 0

user1150525
user1150525

Reputation:

Your code has no mistake, so the problem isn't the code.

Is your value NULL or 0 ?

If it is NULL, change

 INSERT INTO searchny (sok, dt) VALUES ('" . $sokt . "', NOW());

to

 INSERT INTO searchny (sok, dt, count) VALUES ('" . $sokt . "', NOW(), 0);

Upvotes: 1

Michael Krelin - hacker
Michael Krelin - hacker

Reputation: 143229

Probably your count column is NULL by default and every count+1 doesn't make it any less NULL. Try either setting default value of one or explicitly set it to one in INSERT.

Upvotes: 3

Related Questions