Display Mysql Database Column in Morris Bar Graph labels field-CodePudding

I am using the Morris Bar Chart to display the number of times a user has logged in into the system. I would like to have dynamic label ( on hovering). I am getting the data through PHP.

<?php
    $conn = mysqli_connect("localhost", "root", "", "database_name");
    $sql = "SELECT account, 
                        (SELECT fullname 
                        from employees as d 
                        WHERE r.emp_id = d.employee_code) as emp_id, 
                        count(username) as total
            From logins as r 
            group by account 
            ORDER BY emp_id ASC 
            LIMIT 10";

    $most_link =mysqli_query($conn,$sql);
    $chart_data = '';
    $most_sold = '';
    
    while($row_most= mysqli_fetch_array($most_link)){
        $most_sold .= "{ accounnt:'".$row_most["account"]."',  employee:'".$row_most["emp_id"]."', total:".$row_most["total"].", }, ";
    }
    $most_sold = substr($most_sold, 0, -2);
?>
<script>
    new Morris.Bar({
        element : 'most',
        data:[<?php echo $most_sold; ?>],
        xkey:'employee',
        ykeys:['total'],
        labels:'account',
        hideHover:'auto',
        barColors:['#F5761A'],
    });
</script>

If you notice, the labels are hardcoded to 'account(the label text words)', However, I would like it to be dynamic ( 'account' attribute in PHP ). Any idea how it can be done ?

CodePudding user response：

You are not building the JSON correctly, its never easy to do that manually by building a string

Simple option

First change the sql to get the columns with the names you want to exist in the JSON

$sql = "SELECT account, 
                    (SELECT fullname 
                    from employees as d 
                    WHERE r.emp_id = d.employee_code) as employee, 
                    count(username) as total
        From logins as r 
        group by account 
        ORDER BY emp_id ASC 
            LIMIT 10";

$most_sold = '';
$most_arr = [];
$labels = [];

while($row_most= mysqli_fetch_array($most_link)){
    $most_arr[] = $row_most;
    $labels[] = $row_most['account'];
}
$most_sold = json_encode($most_arr);
$json_labels = json_encode($labels);

Then

labels: <?php echo $json_labels; ?>,

CodePudding user response：

I finally got the solution simply get the value and store it a variable
The variable is stored in here $account = $row_most["account"];
Then echoed in the labels field
labels: ['<?php echo $account ?>'], gets dynamic data

        $conn = mysqli_connect("localhost", "root", "", "database_name");
        $sql = "SELECT account, 
            (SELECT fullname from employees as d WHERE r.emp_id = d.employee_code) as emp_id, count(username) as total
            From logins as r group by account ORDER BY emp_id ASC LIMIT 10";
        $most_link =mysqli_query($conn,$sql);
        $chart_data = '';
        $most_sold = '';
            while($row_most= mysqli_fetch_array($most_link)){
            $most_sold .= "{ account:'".$row_most["account"]."',  employee:'".$row_most["emp_id"]."', total:".$row_most["total"].", }, ";
            $account = $row_most["account"];
            }
        $most_sold = substr($most_sold, 0, -2);
    ?>
    <script>
        new Morris.Bar({
        element : 'most',
        data:[<?php echo $most_sold; ?>],
        xkey:'employee',
        ykeys:['total'],
        labels: ['<?php echo $account ?>'],
        hideHover:'auto',
        barColors:['#F5761A'],
    });
    </script>```