Calculate number of records by date

[keianrad]

How can I calculate number of records of today, last week, last month, last three month and year?

 

[keianrad]

Help please ...

 

[cheesegrits]

What do you mean by "calculate"?

Where do you want to display the information?

-- hugh

 

[keianrad]

I have a report table by several calc elements for making reports. I need to report number of records base on date (today, last week, last month, last three month and year).
I don't know if there is any better solution than calculate element for making report.

I need to display reports to admin and managers.

 

[cheesegrits]

You'd need to come up with the queries that produce those numbers.

So for "today" ...

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery
   ->select('COUNT(*) AS tot')
   ->from('yourtable')
   ->where('DAYOFYEAR(yourdate) = DAYOFYEAR(NOW())
   ->where('YEAR(yourdate) = YEAR(NOW());
$myDb->setQuery($myQuery);
return $myDb->loadResult();

If you google for each of the selections you want ("mysql select all rows from last week" etc), you should be able to put together all the numbers you need. Stack Overflow is your friend.

When doing stuff like this, I quite often do it outside of Fabrik, if all you need is a display of it, and don't actually need it in some table format. use either Sourcerer the Flexi Custom Code module to just do that query and output some content ... so instead of returning the result, just ...

myResult = $myDb->loadResult();
echo "<div>Rows from today: $myResult</div>";

... or however you want to format it.

-- hugh

 

[keianrad]

Thank you for your reply. I don't know what should I put instead of (yourdate). I putted date_time element but it doesn't work :

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery
->select('COUNT(*) AS tot')
->from('support_inquiry')
->where('DAYOFYEAR(date_time) = DAYOFYEAR(NOW())
->where('YEAR(date_time) = YEAR(NOW());
$myDb->setQuery($myQuery);
return $myDb->loadResult();

 

[cheesegrits]

Well, I presume if you are wanting to count records by date, you have a date element you use to record when they were submitted.

I can't really go much further with working on custom queries for you on a Standard sub. You should be able to figure out the query you need by playing around in phpMyAdmin. Run SQL queries by hand until you get the result you expect.

Here's an example of playing around with that query, on a table that has two rows with a date_time from today:

https://www.screencast.com/t/ppZMBtpwe

-- hugh