1

I am currently writing my own CMS system in PHP.

All views of a post should be stored in the MySQL database every day.

Currently I have a view counter, which stores all post views of a post in the post table.

$article_id = $_GET['p_id'];

// article view counter
$view_query = "UPDATE articles SET article_views_count = article_views_count + 1 WHERE article_id = $article_id ";
$send_query = mysqli_query($connection, $view_query);

Now I want to change the whole thing that every day for each post a new entry is made in a separate table.

I've learned how to do it all, but my current SQL command adds a new entry to the table each time it's called, not counting for the whole day.

The new SQL-Command:

INSERT INTO days_views (`article_id`, `day_views_count`, `day`) VALUES (1, 1, CURDATE()) ON DUPLICATE KEY UPDATE `day_views_count` = `day_views_count` + 1;

CREATE TABLE `days_views` (
  `day_views_id` int(11) NOT NULL,
  `article_id` int(11) NOT NULL,
  `day_views_count` int(255) NOT NULL,
  `day` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_german2_ci;

ALTER TABLE `days_views`
  ADD PRIMARY KEY (`day_views_id`);

ALTER TABLE `days_views`
  MODIFY `day_views_id` int(11) NOT NULL AUTO_INCREMENT;

My current test table

How can I add one row every day for every post?

How can I count the views for the whole day?

Sven Lorenzino
  • 149
  • 8
  • 1
    Do you have a compound unique index on article_id - day columns? This is required for on duplicate key update extension to work. – Shadow Oct 09 '19 at 13:43
  • I got that. I asked you about something else. – Shadow Oct 09 '19 at 13:48
  • Please [edit] your question and show us the DDL for your test table. As Shadow said, the index must include both the `days_views_ids` and `article_id` not just one or the other alone. – Dave Oct 09 '19 at 13:48
  • 2
    Please use prepared statements to avoid SQL injections in your CMS. See https://bobby-tables.com/ – dolmen Oct 09 '19 at 14:06
  • 1
    your code is vulnerable to sql injection attacks, check this https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – user1844933 Oct 09 '19 at 14:07

3 Answers3

1

Change your table definition to use both article_id and day as the primary key.

CREATE TABLE `days_views` (
  `article_id` int(11) NOT NULL,
  `day` date NOT NULL,
  `day_views_count` int(255) NOT NULL,
  PRIMARY KEY (`article_id`, `day`),
  CONSTRAINT `article` FOREIGN KEY (`article_id`) REFERENCES `articles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_german2_ci;

Then use an UPSERT statement to increment:

INSERT INTO days_views (`article_id`, `day`, `day_views_count`)
VALUES (<article_id>, CURDATE(), 1) 
ON DUPLICATE KEY UPDATE day_views_count = day_views_count + 1;
dolmen
  • 8,126
  • 5
  • 40
  • 42
0

Your primary key needs to be a composite key of day_views_id and article_id NOT just day_views_id.

ALTER TABLE `days_views` ADD UNIQUE `CompKey` (`day_views_id`, `article_id`);

Once you add the unique index your existing logic will work. Do pay heed to the comments about SQL injection though too.

Dave
  • 5,108
  • 16
  • 30
  • 40
-2

It could be a good idea to think different. I will try to explain:

1. How can I add one row every day for every post?

You could make an insert for every view hint saving the followings:

  • day_views_id
  • article_id
  • day (must be DateTime or timestamp type, you will manipulate it better in PHP)

In this way, you add one row every day for every post.

2. How can I count the views for the whole day?

It's easy, you could make a count(); of rows with a specific article_id, with a DateTime field = NOW();. This will return the total views for a specific article for a specific day.

It could be a good idea adding the session id and store it for every insert to your table.

I hope I’ve been of help.

Lord Essen
  • 1