Hello! I'm needing some help integrating HighCharts into my CodeIgniter application and getting data from my MySQL database!
I've followed a YouTube video on actually integrating HighCharts and it was extremely straightforward. However, that method included a hardcoded array and I'm needing to generate an array from data stored in a MySQL table.
What I'm trying to accomplish is search my ORDER and ORDERDETAILS tables and find the 4 week shipment history for a certain product and break it up in individual weeks and have a line chart showing the trend between those 4 weeks. I've already got the correct SQL Query and it's generating the correct numbers.
Below is what I have so far...
Model:
public function four_week($inventoryId) {
$startdate = date('Y/m/d');
$enddate = date("Y/m/d", strtotime("-4 months"));
$this->db->select('ORDERDETAILS.PRODUCTID, ORDERDETAILS.ORDERID, SUM(ORDERDETAILS.QUANTITY) AS SHIPQTY, ORDERS.STATUS, ORDERS.SHIPDATE');
$this->db->from('ORDERDETAILS');
$this->db->join('ORDERS', 'ORDERS.ID = ORDERDETAILS.ORDERID', 'LEFT');
$this->db->where('ORDERDETAILS.PRODUCTID', $this->uri->segment(3));
$this->db->where('ORDERS.STATUS', 1);
$this->db->where('ORDERS.SHIPDATE <=', $startdate);
$this->db->where('ORDERS.SHIPDATE >=', $enddate);
$this->db->group_by('WEEK(ORDERS.SHIPDATE)');
$this->db->order_by('ORDERS.SHIPDATE', 'ASC');
$this->db->limit(4);
$query = $this->db->get();
return $query->result_array();
}
Controller:
function inventory_info(){
$this->load->model('model_security');
$this->model_security->check_login();
$itemId = $this->uri->segment(3);
$this->load->model('model_inventory');
$data['query'] = $this->model_inventory->get_inventory($itemId);
$warning['querys'] = $this->model_inventory->sum_lot($itemId);
$shipArray['shipHis'] = $this->model_inventory->four_week($itemId);
$newArray[] = array();
if(isset($shipArray['shipHis'])){
foreach ($shipArray['shipHis'] as $record) {
$newArray['thatArray'][] = $record['SHIPQTY'];
}
}
$testArray = Array(0 => 45, 1 => 34, 2 => 67,3 => 46);
$series_data[] = array('name' => 'Product', 'data' => $newArray['thatArray']);
$series_data[] = array('name' => 'Funky', 'data' => $testArray);
$this->view_data['series_data'] = json_encode($series_data);
$this->load->view('include/header');
$this->load->view('view_inventory', array_merge($data, $warning, $this->view_data, $newArray));
$this->load->view('include/footer');
}
View:
<script type="text/javascript">
var test =
$(function () {
$('#container').highcharts({
chart: {
type: 'line'
},
title: {
text: '4 Week Ship History'
},
xAxis: {
categories: ['Week 1', 'Week 2', 'Week 3', 'Week 4']
},
yAxis: {
title: {
text: 'Quantity Shipped'
}
},
series: <?php echo $series_data ?>
});
});
</script>
<div id="container" style="width:100%; height:300px;"></div>
In the controller you can see I still have the hardcoded $testArray = Array(0 => 45, 1 => 34, 2 => 67,3 => 46); and that is displaying properly on the chart. When I print the $series_data array I get the following:
[{"name":"Product","data":["3","2","5","2"]},{"name":"Funky","data":[45,34,67,46]}]
The double quotes around the first data array is my problem! Just can't fix it...