将mysql数据检索为特定格式的javascript的问题

Question

I'm trying to create a Highchart gantt chart using PHP and mysql. I have problem in retrieving data from the mysql database. The format in the example given by Highchart Gantt to store the data is:

 cars = [{
   model: 'Nissan Leaf',
   current: 0,
   deals: [{
     rentedTo: 'Lisa Star',
     from: today - 1 * day,
     to: today + 2 * day
   }, {
     rentedTo: 'Shane Long',
     from: today - 3 * day,
     to: today - 2 * day
   }, {
     rentedTo: 'Jack Coleman',
     from: today + 5 * day,
     to: today + 6 * day
   }]
  }}

This is my table for example:

kategori |sub categori | start_date | end_date
-----------------------------------------------
cat 1    | sub 1       | 2019-03-01 | 2019-03-04
cat 2    | sub 2       | 2019-03-06 | 2019-03-10

I tried the following to retrieve the data from the mysql table to get the exact format like given in the example:

The query:

   $query = "SELECT `kategori`, `sub_kategori`, `start_date`, `end_date`, FROM `table` ";

The function:

public function datatable_mini($query)
{
    $stmt = $this->db->prepare($query);
    $stmt->execute();

    if($stmt->rowCount()>0)

    {
        $y=$stmt->rowCount();
        $x=1;

        while($row=$stmt->fetch(PDO::FETCH_ASSOC))
        {
            $start_date = DateTime::createFromFormat('Y-m-d', $row['start_date']);
            $year = $start_date->format('Y');
            $month = $start_date->format('m');
            $day = $start_date->format('d');
        ?>

            {
            <br/>
            <span>Kategori: '<?php print($row['kategori']); ?>',</span>
            <br/><span>current: 0,</span>
            <br/><span>deals: [{</span>
            <br/><span>SubKategori: '<?php print($row['sub_kategori']); ?>',</span>
            <br/><span>Mulai: Date.UTC(<?php print($year); ?>,<?php print($month); ?>,<?php print($day); ?>),</span>
            <br/><span>Selesai: Date.UTC(<?php print(date('Y',strtotime($row['end_date']))); ?>,<?php print(date('m',strtotime($row['end_date']))); ?>,<?php print(date('d',strtotime($row['end_date']))); ?>)</span>
            <br/>
            }]
            <br/>}<?php

            if($x<$y){
                echo ',';
            }
            $x=$x+1;
            ?>
        <?php
        }
    }
    else
    {
       echo "Nothing here...";

    }

}

And this is in the javascript:

   cars = [<?php echo $crud->datatable_mini($query); ?>];

When I tried to echo the result, it gives the exact same format needed, like in the example:

{ 
Kategori: 'cat 1', 
current: 0, 
deals: [{ 
    SubKategori: 'sub 1', 
    Mulai: Date.UTC(2019,03,01), 
    Selesai: Date.UTC(2019,03,04) 
    }] 
}

etc..

But no graph is shown.

How can I retrieve the mysql data into the javascript with the format given in the example?

Answer 1

Your object is not recognized by highcharts because the object names are incorrect and it is not in an array (maybe you fix the array part in something not shown though).

For the data you have pasted above, you need to end up with this format:

[{ 
  data: [{
    name: 'cat 1', 
    id: 'cat 1'
  }, { 
    name: 'sub 1', 
    parent: 'cat 1',
    start: Date.UTC(2019,03,01), 
    end: Date.UTC(2019,03,04),
    completed: {
      amount: 0
    }
  }] 
}],

Working JSFiddle example: https://jsfiddle.net/ewolden/muj5vx1f/6/

Refer to the API for a list of all defined objects: https://api.highcharts.com/gantt/series.gantt.data