Mysql datatype or php code to increment by 1 every

Question

I am trying to use Dreamweaver to build a Lyrics database website. I have a table for the lyrics and I have a column called "views" that I want to increase by 1 every time that particular lyric is viewed in a browser.

How can I accomplish this using mysql? What mysql datatype Or PHP can I use?

Please explain thoroughly because I do not know php or mysql that well, I'm just trying.

Remember I am using Dreamweaver.

Thanks.

Answer 1

I've never been a fan of a 'views' column because there is no proof that is the actual number, instead I would create a transaction table where I would store a timestamp along with some other info, then if I wanted to get a count of how many times the lyrics were viewed I would just do:

SELECT count(*) FROM lyric_views WHERE lyric_id = ?

For demonstration purposes, the table design might look like:

CREATE TABLE `lyric_views` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `lyric_id` int(11) unsigned NOT NULL,
  `viewed_at` timestamp NOT NULL default CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Answer 2

Well we would need to see how your PHP and MySQL is laid out to be honest. Do you want someone to just write it for you or do you want to learn?

The query would be something like this:

$query = "UPDATE `myviewstable` SET count = count+1 WHERE id = '$id'";

I believe that would work. id is your lyric id and count is your column for keeping track of numbers.

Answer 3

UPDATE lyrics SET views=views+1 WHERE id = $id_of_song

and have that execute every time the lyrics page is loaded.