get a summary from a comma separated varchar field

PHPMaker General Discussion (PHPMaker)
1

Hi,
I collect data in a varchar field through a multiple checkbox, so I have in this field values like ‘1,34,5,9’ for instance.
Now I want to count these comma separated values for all records.I tried but I obtain only the summary of the entire value, so I count how many times I get ‘1,34,5,9’, NOT how many times I get ‘34’ and how many times I get ‘9’…How can I get a summary of the singles comma separated values?Tnx in advance

2

If you use MySQL, you may google “FIND_IN_SET”.

3

In addition, you may Google “how to count element from comma separated values in php”

4

ok,
I set this PHP code that is finding the number of occurency of the ‘15’ (for instance) value in the comma separated field:$rs = (“SELECT COUNT(*) FROM mytable WHERE Locate(‘15’, mytable.commaseparatedfield) > 0;”);<?php echo $rs ?>Where can I put this code in order to obtain a table with the results of all the queries like this?tnx

5

Wrong implementation.

6

arbei wrote:

If you use MySQL, you may google “FIND_IN_SET”.

It is wrong to use LOCATE() unless all your numbers do not have same digits (e.g. “15” will mix up with “155”), and you need to execute the SQL, you may search “ExecuteScalar” in this forum.

7

ok you’re right.So this is my working code:

<table class="table table-striped table-sm ew-view-table">
<?php
for ($mul = 1; $mul < 20; $mul++) {
$ris = ExecuteScalar("SELECT COUNT(*) FROM table WHERE FIND_IN_SET($mul, table.fieldwithcommavalues) > 0;");

if ($ris > 0) {
    ?><tr><td>label</td><td>
    <?php 
    echo "$ris<br/>";
    ?></td></tr>
    <?php 
}

}
?>
</table>

The questions:

  1. I put this code in a CustomTemplate of a Dashboard report. Is this the best way?
  2. If I like to have the correct labels of each value…
8
  1. I put this code in a CustomTemplate of a Dashboard report. Is this the best way?No, if the page only contains above content, you should use Custom File instead.2) If I like to have the correct labels of each value…Then you should use correct labels in your template instead of just “label”.
9

ok, I post my working code to help people in the same situation:

<table class="table table-striped table-sm ew-view-table">
<?php
for ($mul = 1; $mul < 20; $mul++) {
$ris = ExecuteScalar("SELECT COUNT(*) FROM table WHERE FIND_IN_SET($mul, table.fieldwithcommavalues) > 0;");

if ($ris > 0) {
    ?><tr>

<td>
    <?php 
    $label = ExecuteScalar("SELECT label_Name FROM lookuptable WHERE lookupfield_ID = $mul;");
    echo "$rislabel";
    ?>
 </td>

<td>
    <?php 
    echo "$ris<br/>";
    ?>
</td></tr>
    <?php 
}

}
?>
</table>

Big TNX to Arbei and Mobhar