drc4925
drc4925
2016-03-19 14:48

CodeIgniter和Google图表 - 基于mysql值的下拉列表

已采纳

I am using code igniter, google charts with php and MySQL to display charts. It works using fixed query. I am trying to add a dropdown to display the chart based on the option (sql column "status") selected Here is what I have so far. How can I modify this to accept dropdown values?

model.php

public function get_chart_data()  
{
    $query = $this->db->get($this->db_mgmt);
    $this->db->select('rating, COUNT(rating) AS Count');
    $this->db->from('db_mgmt');
    $this->db->where('status =', $status);
    $this->db->group_by('rating'); 
    $query = $this->db->get();
    $results['chart'] = $query->result();
}

controller.php

$this->load->model('model', 'chart');
public function index() {
        $results = $this->chart->get_chart_data();
        $data['chart'] = $results['chart'];
        $this->load->view('index.php', $data);
}

view.php

<?php 
foreach ($chart as $object) {
$open_all[] = "['".$object->rating."', ".$object->Count."]";
}
?>

  google.load("visualization", "1", {packages:["corechart"]});
  google.setOnLoadCallback(drawChart_open);

   function drawChart_open() {
    // Create the data table.
    var data = new google.visualization.DataTable();
    data.addColumn('string', 'Rating');
    data.addColumn('number', 'Count');
    data.addRows([
    <?php echo implode(",", $open_all);?>
    ]);

    var options = {
      pieSliceText: 'value-and-percentage',
    };

    var chart = new google.visualization.PieChart(document.getElementById('open_div'));
    chart.draw(data, options);

  }

  <div id="open_div" class="chart"></div>

Thanks in advance!

UPDATE:

I have tried the below using ajax but it doesn't seem to work. I am definitely sure I am doing something wrong here but not sure where. Using Inspect in chrome also doesn't give any errors.

model.php

public function fetch_result($status)  
  {
    $query = $this->db->get($this->db_mgmt);
    $this->db->select('rating, COUNT(status) AS Status_Count');
    $this->db->from('db__mgmt');
    $this->db->where('status =', $status);
    $this->db->group_by('rating'); 
    $query = $this->db->get();
    return $query;
  }

controller.php

$this->load->model('model', 'chart');
public function mychart() {

if(!empty($_POST["val"])) {
    $val=$_POST["val"];
    $result_new=$this->chart->fetch_result($val);

    $array = array();
    $cols = array();
    $rows = array();
    $cols[] = array("id"=>"","label"=>" Rating","pattern"=>"","type"=>"string");
    $cols[] = array("id"=>"","label"=>"Count","pattern"=>"","type"=>"number");  

    foreach ($result_new as $object) {
      $rows[] = array("c"=>array(array("v"=>$object->risk_rating,"f"=>null),array("v"=>(int)$object->Status_Count,"f"=>null)));
   }

    $array = array("cols"=>$cols,"rows"=>$rows);
    echo json_encode($array);

    }

}

view.php

function drawChart_open_all(num) {         
    var PieChartData = $.ajax({
    type: "POST",
    url: "<?php echo base_url(); ?>" + "dashboard/chart/mychart",
    data:'val='+num,
    dataType:"json"
    }).responseText;

    alert(PieChartData);


    // Create the data table.
    var data = new google.visualization.DataTable(PieChartData);
    var options = {
              pieSliceText: 'value-and-percentage',       
    };

    var chart = new google.visualization.PieChart(document.getElementById('open_new'));
    chart.draw(data, options);

  }

<div><span> <b>Pie Chart<br /><br /></span></div>
<form>
  <select name="status" onchange="drawChart_open_all(this.value)">
<option value="WIP">WIP</option>
<option value="Close">Close</option>
  </select>
</form>
<div id="open_new" class="chart"></div>

Thanks in advance!!

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • douyang5943 douyang5943 5年前

    I managed to figure out what the problem was and used ajax in the end. @WhiteHat solution led to also in the right direction. Thanks for that!

    model.php

    public function fetch_result($status)  
      {
        $query = $this->db->get($this->db_mgmt);
        $this->db->select('rating, COUNT(status) AS status_count');
        $this->db->from('db_mgmt');
        $this->db->where('status =', $status);
        $this->db->group_by('rating'); 
        $query = $this->db->get();
    
        $results_new = $query->result();  // <-- Forgot to add this!
        return $results_new;
      }
    

    controller.php

    $this->load->model('model', 'chart');
    
    public function mychart() {
    
    if(!empty($_POST['option'])) {     
    
    $val = $_POST['option'];            
    $result_new=$this->chart->fetch_result($val);
    
    $array = array();
    $cols = array();
    $rows = array();
    $cols[] = array("id"=>"","label"=>" Rating","pattern"=>"","type"=>"string");
    $cols[] = array("id"=>"","label"=>"Count","pattern"=>"","type"=>"number");  
    
    foreach ($result_new as $object) {
      $rows[] = array("c"=>array(array("v"=>(string)$object->rating),array("v"=>(int)$object->status_count)));
    }
    
    $array = array("cols"=>$cols,"rows"=>$rows);
    echo json_encode($array);
    
    }
    }
    

    view.php

       function drawChart_open_all(status) {
    
        var PieChartData = $.ajax({
        type: 'POST',
        url: "<?php echo base_url(); ?>" + "dashboard/chart/mychart",
        data: { 'option':status },  // <-- kept as option instead of val
        dataType:"json",
        global: false,              // <-- Added 
        async:false,                // <-- Added 
        success: function(data){    // <-- Added 
        return data;                // <-- Added 
        },
        error: function(xhr, textStatus, error){
            console.log(xhr.statusText);
            console.log(textStatus);
            console.log(error);
        }
        }).responseText;
    
        // Create the data table.
        var data = new google.visualization.DataTable(PieChartData);
        var options = {  pieSliceText: 'value-and-percentage', };
    
        var chart = new google.visualization.PieChart(document.getElementById('open_new'));
        chart.draw(data, options);
      }
    
    <div><span> <b>Pie Chart<br /><br /></span></div>
    <form>
    <select name="status" onchange="drawChart_open_all(this.value)">
    <option value="WIP">WIP</option>
    <option value="Close">Close</option>
    </select>
    </form>
    <div id="open_new" class="chart"></div>
    
    点赞 评论 复制链接分享
  • duanfu3634 duanfu3634 5年前

    I think the easiest thing would be to send a GET request with the <option> value

    First, go back to your first version.

    Next, send the value in your onchange event

    function drawChart_open_all(num) {
      location = "<?php echo base_url(); ?>" + "dashboard/chart/mychart?option=" + num;
    }
    

    Then in Model --
    get_chart_data()

    you should be able to access the value with --
    $_GET['option']

    use that to modify your query

    here's an old answer with similar concept -- difference is it uses POST vs. GET
    and a <form> with a <input type="submit"> button to send the request
    How to pass JavaScript variables to PHP?

    点赞 评论 复制链接分享