Count the number of records of a section

By andreasml - August 25, 2019

Hi.

I would like to add a script in the modify_homepage plugin to show the current number of records in specific sections. I assume that I need to add a PHP script, like

<?php

echo COUNT("Variable");

?>

 Unfortunately, all my efforts of editing a "Variable" have failed. May I have a hint?

Regards,

Andreas Lazaris

By daniel - August 26, 2019

Hi Andreas,

There are a few ways that you can display the number of records in a section. A very simple method is with the mysql_count() function:

<?php echo mysql_count('section_name'); ?>

Note that his function will return the number of all records in a section, including any that have been hidden/disabled. If you want to make sure that the number reflects only the records that are returned by getRecords(), you can use the metadata returned by getRecords() itself, something like this:

<?php
  list($sectionRecords, $sectionMetaData) = getRecords(array(
    'tableName'   => 'section',
    'loadUploads' => true,
    'allowSearch' => false,
  ));
  
  echo $sectionMetaData['totalRecords'];
?>

You'll want to use the same options here as you use for that section on your front-end pages.

Let me know if you have any other questions!

Thanks,

Daniel
Technical Lead
interactivetools.com

By andreasml - August 26, 2019

Hi Daniel

Thank you for your quick response. I think the first version seems fine. I wonder what will be the code if i would like to limit the counting to the records that have been inserted in the section by the current user.

Regards

Andreas

By daniel - August 26, 2019

Hi Andreas,

I believe something like this should work:

global $CURRENT_USER;
echo mysql_count('section_name', ['createdByUserNum' => $CURRENT_USER['num']]);

This assumes that the section has been set up to use the createdByUserNum field, though most sections should by default.

Let me know if this works for you!

Thanks,

Daniel
Technical Lead
interactivetools.com

By andreasml - August 27, 2019

Hi Daniel,

This is great! It seems it does work. And a final issue. I need to count the number of records from the current section (i.e. section_a) that a field takes the value of this specific field from the $CURRENT_USER. In my case each $CURRENT_USER works in a 'hospital' (field name). I want to count the records from the specific section (i.e. 'section_a') where the field 'hospital' has the value of the 'hospital' of the specific '$CURRENT_USER'. Based on your script, I am using the following one, but it turns out 0 (zero). 

global $CURRENT_USER;
echo mysql_count('section_a', ['hospital' => $CURRENT_USER['hospital']]);

Obviously, there is a mistake.

Thanks again,

Andreas 


By daniel - August 28, 2019

Hi Andreas,

You're using the right approach, however, there is clearly a mismatch somewhere.The first things that come to mind are:

  • Are the 'hospital' fields single- or multiple-choice? This can impact how we perform the query.
  • Are the 'hospital' choices configured the exact same between the user and section_a?

Cheers,

Daniel
Technical Lead
interactivetools.com

By andreasml - August 28, 2019

Hi Daniel

It worked. Actually, the script was correct but turned out 0 because the username I used was not assigned to any hospital!! When I used a different username (assigned to a specific hospital) it gave the correct number.

Thank you very much for your assistance.

Regards,

Andreas

By andreasml - September 22, 2019

Hi Daniel

I would appreciate if you gave me an extra hint on the previous issue. 

If I wanted to limit the count in a certain period of time (for example after January 1, 2019), should I use the following code?

mysql_count('section_name', ['hospital' => $CURRENT_USER['hospital']], ['date_of_treatment' >= '01/01/2019']);

or, between Jan 1, 2019 and June 1, 2019

mysql_count('section_name', ['hospital' => $CURRENT_USER['hospital']], ['date_of_treatment' >= '01/01/2019' && 'date_of_treatment' <= '01/06/2019')];

Regards,

Andreas

By daniel - September 23, 2019

Hi Andreas,

That sort of filtering format with mysql_count() (and a few other mysql_ functions) only works when trying to match values. If you want to start doing more advanced comparisons, it requires explicitly creating the "WHERE" portion of the MySQL query (similar to the "where" parameter for getRecords()). So for your examples, it could look something like this:

$countWhere = "hospital = '" . mysql_escape($CURRENT_USER['hospital']) . "' AND date_of_treatment >= '2019-01-01 00:00:00'";
mysql_count('section_name', $countWhere);

or:

$countWhere = "hospital = '" . mysql_escape($CURRENT_USER['hospital']) . "' AND date_of_treatment >= '2019-01-01 00:00:00' AND date_of_treatment <= '2019-06-01 00:00:00'";
mysql_count('section_name', $countWhere);

Note that the standard date/time format in a MySQL context is "y-m-d h:m:s"

Let me know if you have any other questions!

Thanks,

Daniel
Technical Lead
interactivetools.com