get a summary from a comma separated varchar field

This public forum is for user-to-user discussions of PHPMaker. Note that this is not support forum.
Post Reply
actarus99
User
Posts: 25

get a summary from a comma separated varchar field

Post by actarus99 »

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


arbei
User
Posts: 9284

Post by arbei »

If you use MySQL, you may google "FIND_IN_SET".


mobhar
User
Posts: 11660

Post by mobhar »

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


actarus99
User
Posts: 25

Post by actarus99 »

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


mobhar
User
Posts: 11660

Post by mobhar »

Wrong implementation.


arbei
User
Posts: 9284

Post by arbei »

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.


actarus99
User
Posts: 25

Post by actarus99 »

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....


arbei
User
Posts: 9284

Post by arbei »

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".


actarus99
User
Posts: 25

Post by actarus99 »

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


Post Reply