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
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.
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:
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.
It is also repeating the output five times for each row
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:
Thank you for taking the time to try to find a solution, it is appreciated
I have looked at both the links you provided but unfortunately I am still scratching my head wondering how I can implement a solution.
I do not think what I need is complicated, it is just a âcountâ of non empty fields, however I have tried so many different ways of using âgetImageListâ and I still cannot get the correct output.
I think a snippet of code could produce what I want, but unfortunately everything I have tried so far has not worked.
Off Topic
Actually MIGXDB is awesome as it is, but I can think of many âuse casesâ where it could do so much more for me if I had the coding knowledge âŚ
For example: My wish list would be some tutorials with coding examples replicating Excel maths (add, subtract, multiply, average, percentage âŚ) or anything that could be done on Excel could be saved to a database and rendered on the front end using code to get the same outcome.
Iâve gone off topic a bit here but just wanted to share my thoughts
Count the number of fields that has data. (not counting empty or null fields)
If the CMP has been updated 10 times but any given field, say âAsha_Regionâ only has 7 entries then it should count the 7 entries and display 7 on the front end.
When the CMP is updated then and data is added to âAsha_Regionâ then that count should be reflected in the front end.
The front end can display in a table or anything, using a Tpl.
Itâs just how to count that number