Community

How To Count The Number Of Non Empty MIGXDB Fields?

I am using MIGXDB to populate the MySql and loop collection to display data in a table, this part is successful. However I want to something a little different and I cannot find a solution:

I have 2 questions

1/ Is there a way to produce a value in the front end that displays whether there is any data in the fields of a MySql column?
#Note# I do not want to display the actual data from each field, I want to count the number of fields with data in and display that as a number excluding the fields with blank data.

For example: If a column has 20 rows and 15 have actual data with 5 blank then it should display front end 15

2/ How to display difference between the number of entries in one column compared to another (simple subtraction)?

For example: if column A has 15 fields with actual data and column B has 5 fields with actual data then it should display the sum difference = 10

MySql info:

packageName=`tfsabah`
classname=`Tfsabah`
selectfields=`Asha_Region,Asha_Loc_id,Asha_Site_Name`

Any advice, guidance or code examples would be greatly appreciated

I found and modified the following snippet (for 1 column only) but it is just returning a result 0 (zero)

<?php
$c = $modx->newQuery('Tfsabah');
$c->select('sum(Asha_Region) as cnt');
if ($c->prepare() && $c->stmt->execute()) {
echo $c->stmt->fetchColumn();
}

From reading the Modx docs I think the best way to do this would be to use xPdo getCount, however I cannot find any examples of code to replicate what I am trying to do.

Still looking for the solution …

I replaced the ‘select’ line with a ‘getCount’ line as shown below:

<?php
$c = $modx->newQuery('Tfsabah');
$count = $modx->getCount('Asha_Region', $c);
if ($c->prepare() && $c->stmt->execute()) {
echo $c->stmt->fetchColumn();
}

However, this is returning a count of 1 whereas there are 4 fields with data in the column ‘Asha_Region’ so it is still not returning the correct result.

class = Tfsabah
table = tfsabah
field key = Asha_Region

Could it be the above code is counting the columns with the name Asha_Region as opposed to counting the contents within the column?
If yes that might explain why I am getting the result 1 and not 4

Still searching for the solution, when/if I find it I will post it on here

I am getting closer, I changed the code in the chunk and now I can get a row count (see below code). However there is still a number of issues:

  1. Currently there is only 5 rows of data, but the field I have chosen in this code only has data in one row but it is returning a row count and not a count of the non empty fields.
  2. It is also repeating the output five times for each row
  3. If I add another field it makes no difference to the output, it just outputs the number of rows:-(

I added the following filter thinking it would filter out the empty fields but it does not work:

&where=`{"published":"1"}`

Here is the current code I am using

[[!migxLoopCollection?
&packageName=`tfsabah`
&classname=`Tfsabah`
&selectfields=`id,Asha_Region,`
&where=`{"published":"1"}`
&tpl=`mytotal`
]]

The Tpl

<ul><li>
[[+total]]
</li></ul>

Tried using ‘totalVar’ also but I cannot get that working either

Still working on this …