Hello! There is an interesting problem. There is a set date, for example 2014-07-02. There is a table in the posts database, where the user adds one entry every day, and it’s something like this:

 +----+----------------------------------+------------+ | id | title | date | | 1 | Очень хороший день | 2014-07-10 | | 2 | Неплохой день | 2014-07-09 | | 3 | Хороший день | 2014-07-08 | | 4 | Могло быть и лучше | 2014-07-07 | | 5 | Не доделал что хотел | 2014-07-06 | | 6 | Забыл вчера написать | 2014-07-05 | | 7 | Начал следить за собой | 2014-07-03 | | 8 | Стартуем | 2014-07-02 | +----+----------------------------------+------------+

I need to display statistics for the last week, relative to that date, and display it to the user, the main indicator: var final = Missing entries this week - 0.

To solve this problem, I need to get the last 7 records, and compare them with the dates of the last 7 days. The task for the user is not to allow var final to be greater than 1. For me the task, if greater than 1 is the value, output the appropriate message.

I wrote a function in php, which receives an array of the last 7 days, but then I don’t know how to tie everything together:

 if ( ! function_exists('get_array_seven_day')) { function get_array_seven_day() { $array = array(); for($i=1;$i<8;$i++) { $array[] = date('d.m',strtotime(date('dmY H:i:s'))-60*60*24*$i); } return $array; } }
  • 3
    You go completely wrong from that end. Change the tag (mostly in your head :) from php to sql , PHP to solve the problem in general no side. Well, that is, you can teach the hare to smoke, but it is better to think in a different direction :-) - user6550
  • @klopp, I'm afraid to solve such a problem using SQL methods, for me a dark forest that I don’t even know how to enter. - chuikoff
  • @chuikoff if (! function_exists ('get_array_seven_day')) And if the function still exists? Wouldn't it be better at this moment to crash the application, instead of using the unknown? - etki
  • four
    If you need only! number: SELECT COUNT (id) AS totalFROM news WHERE DATE_ADD ( date , INTERVAL '1' WEEK)> = NOW (); Compare the result with the number of days in a week (on my planet it is 7) and that's it, don’t look for unnecessary problems. - dekameron pm

1 answer 1

Firstly, the table lacks the user identifier. You also need a unique key for the date + user. The table looks like this:

 CREATE TABLE `posts` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(255) NOT NULL DEFAULT '', `date` date NOT NULL, `user_id` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `u_date_user_id` (`date`,`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Secondly, we receive both values ​​in one request - the number of specified and the number of unspecified days

 SELECT COUNT(DISTINCT `date`) AS set_counts, 7 - COUNT(DISTINCT `date`) AS unset_counts FROM `posts` WHERE `date` BETWEEN CURDATE() - INTERVAL 6 DAY AND CURDATE() AND user_id = 1;

In this query, we select the last 7 days from the current one. If you need to get the last calendar week, then first on php we calculate the date of the last resurrection and specify it in the request instead of CURDATE() .

DISTINCT added in case there is no date unique index in the table.