How to find values between and count it [SOLVED]-CodePudding

I want to build a panel where the admin can display the total page views. I do save timestamps for each click when a user change page or refresh it.

I want to find how many user viewed at the date by using timestamp.

$Today = time();

$EightDaysSec  = 691200;
$SevenDaysSec  = 604800;
$SixDaysSec    = 518400;
$FiveDaysSec   = 432000;
$FourDaysSec   = 345600;
$ThreeDaysSec  = 259200;
$TwoDaysSec    = 172800;
$OneDaySec     = 86400;

$FoundEightDaysAgo  = $Today - $EightDaysSec;
$FoundSevenDaysAgo  = $Today - $SevenDaysSec;
$FoundSixDaysAgo    = $Today - $SixDaysSec;
$FoundFiveDaysAgo   = $Today - $FiveDaysSec;
$FoundFourDaysAgo   = $Today - $FourDaysSec;
$FoundThreeDaysAgo  = $Today - $ThreeDaysSec;
$FoundTwoDaysAgo    = $Today - $TwoDaysSec;
$FoundOneDaysAgo    = $Today - $OneDaySec;

With this code I'm able to catch it back 8 days past.

Now the problem I have user timestamps coming from database in a array and I do loop it with foreach()

When I do


$Query          = $db->prepare('SELECT * FROM new_user_last ');
$Query->execute();
$QueryNumber    = $Query->rowCount();
$QueryRecords   = $Query->fetchAll();

foreach ($QueryRecords as $Records ) {
  
  $UserDate = $Records['Date'];

  if($UserDate >= $FoundSevenDaysAgo and $UserDate <= $FoundSixDaysAgo){
 

  //Yes it does run true but then? How do I display those values?

  }

}

I want to find the values between and record it so I can count and display it as a view.

I'm open to any new ideas that can do the work.

I'm using canvaJS template for graph view if anyone wonders. => https://canvasjs.com/php-charts/spline-chart/

CONSULUTION

With the help of Markus Zeller now I'm able to achieve what I want it to.



$Query = $db->prepare('SELECT *, TIMESTAMPDIFF(DAY, FROM_UNIXTIME(`Date`), CURDATE()) AS diff FROM new_user_last WHERE FROM_UNIXTIME(`Date`) >= CURDATE() - INTERVAL 7 DAY ORDER BY `Date`');
$Query->execute();
$SeventhDay    = $Query->rowCount();

$Query = $db->prepare('SELECT *, TIMESTAMPDIFF(DAY, FROM_UNIXTIME(`Date`), CURDATE()) AS diff FROM new_user_last WHERE FROM_UNIXTIME(`Date`) >= CURDATE() - INTERVAL 6 DAY ORDER BY `Date`');
$Query->execute();
$SixthDay    = $Query->rowCount();

$Query = $db->prepare('SELECT *, TIMESTAMPDIFF(DAY, FROM_UNIXTIME(`Date`), CURDATE()) AS diff FROM new_user_last WHERE FROM_UNIXTIME(`Date`) >= CURDATE() - INTERVAL 5 DAY ORDER BY `Date`');
$Query->execute();
$FifthDay    = $Query->rowCount();

$Query = $db->prepare('SELECT *, TIMESTAMPDIFF(DAY, FROM_UNIXTIME(`Date`), CURDATE()) AS diff FROM new_user_last WHERE FROM_UNIXTIME(`Date`) >= CURDATE() - INTERVAL 4 DAY ORDER BY `Date`');
$Query->execute();
$FourthDay    = $Query->rowCount();

$Query = $db->prepare('SELECT *, TIMESTAMPDIFF(DAY, FROM_UNIXTIME(`Date`), CURDATE()) AS diff FROM new_user_last WHERE FROM_UNIXTIME(`Date`) >= CURDATE() - INTERVAL 3 DAY ORDER BY `Date`');
$Query->execute();
$ThirdDay    = $Query->rowCount();

$Query = $db->prepare('SELECT *, TIMESTAMPDIFF(DAY, FROM_UNIXTIME(`Date`), CURDATE()) AS diff FROM new_user_last WHERE FROM_UNIXTIME(`Date`) >= CURDATE() - INTERVAL 2 DAY ORDER BY `Date`');
$Query->execute();
$SecondDay    = $Query->rowCount();

$Query = $db->prepare('SELECT *, TIMESTAMPDIFF(DAY, FROM_UNIXTIME(`Date`), CURDATE()) AS diff FROM new_user_last WHERE FROM_UNIXTIME(`Date`) >= CURDATE() - INTERVAL 1 DAY ORDER BY `Date`');
$Query->execute();
$FirstDay    = $Query->rowCount();

$Seven   = $SeventhDay - $SixthDay;
$Six     = $SixthDay - $FifthDay;
$Five    = $FifthDay - $FourthDay;
$Four    = $FourthDay - $ThirdDay;
$Three   = $ThirdDay - $SecondDay;
$Two     = $SecondDay- $FirstDay;
$One     = $FirstDay; 



$dataPoints = array(
    array("y" => $Seven, "label" => $DayLast),
    array("y" => $Six, "label" => $DaySix),
    array("y" => $Five, "label" => $DayFive),
    array("y" => $Four, "label" => $DayFour),
    array("y" => $Three, "label" => $DayThree),
    array("y" => $Two, "label" => $DayTwo),
    array("y" => $One, "label" => $DayFirst)
);

CodePudding user response：

Considering your fieldname is Date you can use the database for calculation and filtering.

This query will create a value diff in days and the WHERE clause will find all entries up to 7 days. The results are sorted by diff.

$Query = $db->prepare('SELECT *, TIMESTAMPDIFF(DAY, `Date`, CURDATE()) AS diff FROM new_user_last WHERE `Date` >= CURDATE() - INTERVAL 7 DAY ORDER BY `Date`');

You can access the diff as all the other fields.

foreach ($QueryRecords as $Records) {
    echo "This record is {$Records['diff']} days old.\n";
}

Update

As the Date field is timestamp instead datetime, please try this:

$Query = $db->prepare('SELECT *, TIMESTAMPDIFF(DAY, FROM_UNIXTIME(`Date`), CURDATE()) AS diff FROM new_user_last WHERE FROM_UNIXTIME(`Date`) >= CURDATE() - INTERVAL 7 DAY ORDER BY `Date`');