Page 1 of 1
get a summary from a comma separated varchar field
Posted: Wed Aug 17, 2022 4:20 pm
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
Re: get a summary from a comma separated varchar field
Posted: Wed Aug 17, 2022 8:51 pm
by arbei
If you use MySQL, you may google "FIND_IN_SET".
Re: get a summary from a comma separated varchar field
Posted: Thu Aug 18, 2022 7:32 am
by mobhar
In addition, you may Google "how to count element from comma separated values in php"
Re: get a summary from a comma separated varchar field
Posted: Thu Aug 18, 2022 3:45 pm
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
Re: get a summary from a comma separated varchar field
Posted: Thu Aug 18, 2022 10:36 pm
by mobhar
Re: get a summary from a comma separated varchar field
Posted: Thu Aug 18, 2022 11:56 pm
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.
Re: get a summary from a comma separated varchar field
Posted: Fri Aug 19, 2022 4:00 pm
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....
Re: get a summary from a comma separated varchar field
Posted: Fri Aug 19, 2022 4:44 pm
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".
Re: get a summary from a comma separated varchar field
Posted: Sat Aug 20, 2022 11:40 pm
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