How to get a word count of data in mysql and display in bar graph

Question

I would like to be able to get data from my sql database in php (phpMyAdmin) and display in a bar graph using chart.js.

To elaborate a bit more here is what i need to achieve.

Goal: To display the license type as a label in my bar graph and the word count for L1, L2 and B4 as data displayed:

50 |
40 | ||
30 | ||          ||
20 | ||    ||    ||
10 | ||    ||    ||
   -----------------
     L1    L2    B4

This is my license_table at the moment (there are more rows than this :-) )

------------------------------
|id | display_name | license | 
------------------------------
|1  | name1        | L1      |
|2  | name2        | L2      |
|3  | name3        | B4      |
|4  | name4        | L1      |
------------------------------

Here is my dblicense.php code I have so far:

<?php

$dbhost = 'localhost';
$dbname = 'email_licenses';
$dbuser = 'administrator';
$dbpass = '-----';

try{
$dbcon = new PDO("mysql:host={$dbhost};dbname={$dbname}",$dbuser,$dbpass);
$dbcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

}catch(PDOException $ex){
die($ex->getMessage());
}

$stmt=$dbcon->prepare("SELECT license, COUNT(license)
FROM license_table
WHERE license='L1' OR license='L2' OR license='B4' 
GROUP BY license;");
$stmt->execute();
$json=[];
while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
extract($row);
$json[]= $license;

}
?>

And then my license.php:

<?php include'dblicenses.php'; ?>


<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Reports</title>
</head>

<body>
<div class="col-lg-8 col-md-8 col-sm-8" align="center">
    <h3 class="text-center">TICKET REPORT</h3>
<canvas id="myChart"></canvas>
<script src="https://cdn.jsdelivr.net/npm/chart.js@2.8.0"></script>
<script type="text/javascript">
var ctx = document.getElementById('myChart').getContext('2d');
    var chart = new Chart(ctx, {
// The type of chart we want to create
type: 'bar',

// The data for our dataset
data: {
    labels: <?php echo json_encode($json);?>,
    datasets: [{
        label: 'Assigned Licenses',
        backgroundColor: 'rgba(36,247,27,1.00)',
        borderColor: 'rgb(36,247,27,1.00)',
        data: <?php echo json_encode($json);?>,
    }]
},

// Configuration options go here
options: {}
});

</script>
</div>
</body>
</html>

By testing this code I get the following:(no data is diplayed)

 1 |
 2 |
 0 |
-2 |
-1 |
   -----------------
     L1    L2    B4

To sum up: I need to get total word count for L1, L2 and B4 and display the data in my bar graph and the L1, L2 and B4 to display as the labels as well.

Really hoping someone could assist me on this?

Thanks

Possible duplicate of [Using SQL to determine word count stats of a text field](https://stackoverflow.com/questions/748276/using-sql-to-determine-word-count-stats-of-a-text-field) — Pinke Helga, Mar 19 '19 at 05:28
This question is too broad for StackOverflow. Please split it up into more questions, the word count and the bar graph. — Pinke Helga, Mar 19 '19 at 05:31
@Quasimodo'sclone Thanks, I have tried that solution but not getting it right :-( — , Mar 19 '19 at 05:32
@Quasimodo'sclone He's getting the word count correctly already, he's just not extracting it properly to put in the graph. — Barmar, Mar 19 '19 at 05:32
@KeithKing correct include at top of license.php, In your case db connection file is dblicense.php not dblicenses.php — Surya prakash Patel, Mar 19 '19 at 07:12

Barmar · Accepted Answer · 2019-03-19T05:51:57.307

0

You're not putting the counts anywhere. You need one array for the labels, another array for the counts.

$stmt=$dbcon->prepare("SELECT license, COUNT(license) AS count
FROM license_table
WHERE license IN ('L1', 'L2', 'B4')
GROUP BY license;");
$stmt->execute();
$labels=[];
$counts = [];
while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
    $labels[]= $row['license'];
    $counts[] = $row['count'];
}

Then you can use one in the labels: option, the other in data:.

data: {
    labels: <?php echo json_encode($labels);?>,
    datasets: [{
        label: 'Assigned Licenses',
        backgroundColor: 'rgba(36,247,27,1.00)',
        borderColor: 'rgb(36,247,27,1.00)',
        data: <?php echo json_encode($counts);?>,
    }]
},

edited Mar 19 '19 at 05:51

answered Mar 19 '19 at 05:31

Barmar

741,623
53
500
612

Thanks, Just tried it but now it has removed the labels. just shows me an empty graph :-) – Mar 19 '19 at 05:47
Are you seeing the correct arrays when you use `View Source`? – Barmar Mar 19 '19 at 05:50
Nevermind. I had a typo in my code. (I don't like to copy and paste code I would rather retype it and understand it.) Thanks alot all is working correctly now – Mar 19 '19 at 05:52

How to get a word count of data in mysql and display in bar graph

1 Answers1