how to COUNT explode imploding data with comma?

Question

I want to count the number of mechanics in a chart, but some data has multiple data which I use with implode (comma). how to separate data?

I'm taking data from one of my tables, where a column has a value that is implode. the problem here, I just want to explode **nama_tmp **(EKO PAMBUDI, MURNO, ARDI PRASETYO) separately so I can count each name.

The following query I use to display the table:

var chart4; 
    $(document).ready(function() {
        chart4 = new Highcharts.Chart({
            chart: {
                renderTo: 'mygraph4',
                type: 'column'
            },   
            title: {
                text: 'Grafik Mekanik'
            },
            xAxis: {
                categories: ['Mekanik']
            },
            yAxis: {
                title: {
                text: 'Total Perbaikan'
                }
            },
                series:             
                [
                    <?php 
                    include "system/koneksi.php";
                    $sql   = "SELECT nama_tmp FROM tb_tmp GROUP by nama_tmp";
                    $query = mysqli_query($connect, $sql)  or die(mysqli_error());
                    while($temp = mysqli_fetch_array($query))
                    {
                        $namatmp=$temp['nama_tmp'];                     
                        $sql_total   = "SELECT COUNT(nama_tmp) as 'total' from tb_tmp GROUP by nama_tmp = '$namatmp'";        
                        $query_total = mysqli_query($connect,$sql_total) or die(mysql_error());
                        while($data = mysqli_fetch_array( $query_total))
                        {
                            $total = $data['total'];                 
                        }             
                    ?>
                        {
                        name: '<?php echo $namatmp; ?>',
                        data: [<?php echo $total; ?>]
                        },
                        <?php 
                    }   ?>
                    ]
        });
    });

my database:

Personally I don't get the problem ! Is this a SQL or PHP question ? What is the input and the wanted output ? I don't see any implode() in you code ! — Sami, Aug 18 '21 at 08:14
I'm sorry, so in the code above I'm taking data from one of my tables, where a column has a value that is implode. the problem here, I just want to explode (EKO PAMBUDI, MURNO, ARDI PRASETYO) separately so I can count each name. — vivie, Aug 18 '21 at 08:24
Does this answer your question? [How to count items in comma separated list MySQL](https://stackoverflow.com/questions/7020001/how-to-count-items-in-comma-separated-list-mysql) — spinsch, Aug 18 '21 at 08:26
@spinsch the case is almost the same as mine, but I don't understand the solution offered in the problem..... — vivie, Aug 18 '21 at 08:30
We should not see iterated queries. We should see just one query with a JOIN and perhaps WITH ROLLUP. If you have comma separated values in your table column, then you have done an unfortunate job of designing your database table and you are going to suffer from it. Please provide a db fiddle with realistic data and your exact desired output from the sample input. Do you have a master/static list of all of the names that will be enountered? — mickmackusa, Aug 20 '21 at 02:50
@mickmackusa I have updated my question. right, I'm having trouble from implode comma that I have created, is the solution by creating another table without concatenating variables? can you help me? — vivie, Aug 20 '21 at 03:12
`tb_tmp` should have an id column that is the primary key and automatically increments. Your table has very little data in it, so the good news is that repairing your table design will be very easy work. You just need to split PERBAIKAN BONGKAR MESIN's 3 values into 3 separate rows. I noticed that you are using `GROUP BY` unnecessarily in your screenshot. When you redesign your table, the GROUP BY will become absolutely critical. You will then be able to use AGGREGATE functions to conditionally sum what you need (research "[tag:pivot]" table). — mickmackusa, Aug 20 '21 at 03:51

Beowolve · Answer 1 · 2021-08-19T12:19:03.117

After looking at the image again, I can understand your problem. You basically need to prepare your data, instead of printing the PDF directly.

You could use a map to calculate your output like so:

$total = array("freq" => 0, "menit" => 0, "jam" => 0);
$groups = array();
while ($data = mysqli_fetch_array($query)) {
    $names = explode(",",$data['nama_tmp']);
    foreach ($names as $name) {
        if (!array_key_exists($name, $groups)) {
            $groups[$name] = array("freq" => 0, "menit" => 0, "jam" => 0);
        }
        $groups[$name]["freq"] += $data["freq"];
        $groups[$name]["menit"] += $data["menit"];
        $groups[$name]["jam"] += $data["jam"];
        $total["freq"] += $data["freq"];
        $total["menit"] += $data["menit"];
        $total["jam"] += $data["jam"];
    }
}

Now you should have all your data in $groups and you can generate your PDF from that.

foreach ($groups as $name => $group) {
    $pdf->Ln();
    $pdf->Cell(90,5,$name,1,0,'L',0);
    $pdf->Cell(20,5,$group['freq'],1,0,'C',0);
    $pdf->Cell(25,5,$group['menit'],1,0,'C',0);
    $pdf->Cell(25,5,$group['jam'],1,0,'C',0);
}

$pdf->Ln(5);
$pdf->SetFillColor(255, 235, 255);
$pdf->SetFont('times','B',8);
$pdf->Cell(90,5,'TOTAL',1,0,'C',1);
$pdf->Cell(20,5,$total["freq"],1,0,'C',1);
$pdf->Cell(25,5,$total["menit"],1,0,'C',1);
$pdf->Cell(25,5,$total["jam"],1,0,'C',1);

Note: Instead of using a variable for every column sum, I have used a $total map array instead. This makes it more readable. Also you should consider using some functions for each part of your script.

The main function could then be as simple as this:

$data = readData();
$groups= calculateSums($data);
$pdf = generatePDF($groups);

so in the picture above there are "EKO PAMBUDI, MURNO, ARDI PRASETYO" and "ABDULLAH MUIS, EKO PAMBUDI, ARDI PRASETYO". I want to include the two groups based on their names, so if EKO PAMBUDI gets a frequency of 13 it will be 15 because the group has exploded comma. if i use your answer, where can i put it? — vivie, Aug 18 '21 at 08:54
thanks, that's very helpful. but I still don't understand about the second code, the freq, minutes, and hours are correct. but for nama_tmp I'm confused. about "Now you should have all your data in $groups and you can generate your PDF from that." — vivie, Aug 19 '21 at 05:51
I have changed the second part of the code, now it should be clear. — Beowolve, Aug 19 '21 at 12:19
can you help me one more time? I have almost the same case, it's just that I apply this on the chart. @Beowolve — vivie, Aug 20 '21 at 02:30

how to COUNT explode imploding data with comma?

1 Answers1