Count form clicks and updating MySQL databases

[phaedrusGhost]

Hi All,

My MySQL experience is fair but when it comes to doing some of the more complex queries I get stupid fast. That said, in the past, I have written queries that INSERT data to the DB based on clicks. In this case I want to use the same theory as HOT or NOT showing an image and then allowing a reviewer to "Like" or "Dislike" then record that choice in the DB. I've learned in the past that the way to record this type of variable is to INSERT each entry seperately and then counting each row with the "user_id" associated with that image and parsing that data for display on the site. My thinking is there has to be a better way that would essentially be quicker to parse each time the page is viewed.

Rather than enter a new entry (ROW) each time and do a COUNT "likes" WHERE user_associated = "$user_id", I would rather UPDATE the existing Value by adding the new like to it.

My first thought is to take the $_POST['like] and make it equal to "1" then add it to the existing "like" VALUE in the DB for the SUM. Am I on the right track there?

I think this would make for a neat and slim DB but maybe I am off on this. Any idea and help is appreciated. I hope I have been clear in my explanation and concept.

Example below:

table "clicks"

Column | Type | Value
id | bigint(20)
likes | varcahr(25) | 1
dislikes | varchar(25) | 1
user_associated | int(20) | 402 (*this would be the user_id associated with each like and dislike)

[Excizted]

Hi there.

I assume you are using PHP as your programming language. You must use PHP to develop the logic of finding out whether or not something has been clicked on the HTML end.

When your PHP program knows this, fx by the POST-variable you mentioned

if(isset($_POST['like']))
{ // So the LIKE-button was pressed

}

You would add any logic to prevent liking multiple times in there too.

Then you can increment the database value independent of PHP-variables, with the query below. But first, you are saying your table columns "likes" and "dislikes" are varchar-type. Varchar is for characters, and I think you want "likes" and "dislikes" to be a count (number, integer). So please change their type to something like int(11), unsigned optionally.

Then you can do

UPDATE clicks SET likes = likes+1 WHERE user_associated = {$_SESSION['user_id']}

Where $_SESSION['user_id'] being an integer user id, you can change that to whatever variable you use.

I hope this helps.

[phaedrusGhost]

Thank Excizted...This looks like a super nice string to do what I want to do. I am going to check this out here in a bit and will come back and update my post.

Big thanks for the time on this.... ;)

[phaedrusGhost]

A most excellent solution. Thank you for saving me some frustration. Beer on me if you are ever in Bali, Indonesia. ;) Thank you Excizted!!!!