Query based instant widget

Rohit commented on Nov 3, 2017

Hello Koolreport, I am trying Instant package,It is very good feature from koolreport team but is there any way by which i can use Query based data to display in widget. Because at some point my data might be large and changing by the time

Can you please guide me through this

KoolReport commented on Nov 3, 2017

Well, all you need to do is to query your data in form of associate array. Put the data to "data" property of widget and it will work fine.

Rohit commented on Nov 3, 2017

I got my data in following format as u said Array ( [0] => Array ( [State] => NEW [Count] => 22 ) [1] => Array ( [State] => CLOSED [Count] => 1287 ) [2] => Array ( [State] => OPEN [Count] => 85 ) [3] => Array ( [State] => RESOLVED [Count] => 2 ) [4] => Array ( [State] => PENDING FOR APPOVAL [Count] => 4 ) [5] => Array ( [State] => PENDING FOR BUSINESS [Count] => 2 ) [6] => Array ( [State] => PENDING WITH VENDOR [Count] => 1 ) )

I passed it to data property of widget but is displaying

Rohit commented on Nov 6, 2017

Hey koolreport team, anything on this issue??

KoolReport commented on Nov 6, 2017

I just make a small test:

require_once "..\koolreport\autoload.php";
use \koolreport\instant\Widget;
use \koolreport\widgets\google\PieChart;


Widget::create(PieChart::class,array(
    "data"=>array(
        array("state"=>"Alabama","population"=>4863300),
        array("state"=>"Alaska","population"=>741894),
        array("state"=>"Arkansas","population"=>2988248),
        array("state"=>"New Mexico","population"=>2081015),
        )
));

The result shows

It seems working fine.

Rohit commented on Nov 6, 2017

I am getting my data from Sql and passing that variable to the data of Widget. The data that i sent you in previous comment was the var_dump of that variable.

KoolReport commented on Nov 6, 2017

Please up your code

Rohit commented on Nov 6, 2017

<?php
    require_once "autoload.php";
    use \koolreport\instant\Widget;
    use \koolreport\widgets\google\PieChart;
?>

<html>
    <head>
        <title>Instant PieChart</title>
    </head>
    <body>
    <?php

    $con = mysqli_connect("connection string");
    // Check connection
    if (mysqli_connect_errno())
      {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
      }

    $sql = "SELECT UPPER(ts.name) as State, Count(*) as Count
FROM ticket t
JOIN ticket_state ts
ON t.ticket_state_id = ts.id 
GROUP BY t.ticket_state_id";

$result = mysqli_query($con,$sql);

if (mysqli_num_rows($result) == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
}

$res = array();
while($line = mysqli_fetch_assoc($result)){
    $something[State] = $line["State"];
    $something[Count] = $line["Count"];
    array_push($res,$something);
}

// print_r($res);

    Widget::create(PieChart::class,array(
        "data"=>$res
            ));
    ?>
    </body>
</html>

KoolReport commented on Nov 6, 2017

Change this code to:

while($line = mysqli_fetch_assoc($result)){
    array_push($res,array(
        "State"=>$line["State"],
        "Count"=>$line["Count"],
    ));
}

Rohit commented on Nov 6, 2017

It is still giving me same result as before

Rohit commented on Nov 7, 2017

Hello, any updates because i need this ASAP Waiting for your reply

KoolReport commented on Nov 7, 2017

Could you please set it up in the server like previously did so I could see what went wrong because I could not replicate the issue here.

KoolReport commented on Nov 7, 2017

You do this:

while($line = mysqli_fetch_assoc($result)){
    array_push($res,array(
        "State"=>$line["State"],
        "Count"=>(int)$line["Count"],
    ));
}

The problem is that $line["Count"] is returned from database in string type so we use (int) to convert.

Let me know if it works.

Rohit commented on Nov 7, 2017

It worked Thanks a lot for such a quick response. Appreciate it.

KoolReport's Forum

Query based instant widget #145

Build Your Excellent Data Report