How to implement MYSQL data fetching in JS Gantt chart?

Question

I'm using AnyChart’s JS Charts library and I want to implement this with my project data's, I'm using Codeinghter framework and I want to know what's the best way to do this. I know it's very basic but any help is appreciated. Thanks.

DATABASE

Project Table

id || name || start_date || due_date ||

Tasks Table

id || name || start_date || end_date || milestone_id || project_id

Milestone Table

id || name || startdate || enddate || project_id

Codepen https://codepen.io/its_sam/pen/rNpmJwy

anychart.onDocumentReady(function () {
    // create data
    var data = [{
        id: "1",
        name: "Milestone Development",
        actualStart: Date.UTC(2018, 01, 02),
        actualEnd: Date.UTC(2018, 06, 15),
        children: [{
                id: "1_1",
                name: "Task Planning",
                actualStart: Date.UTC(2018, 01, 02),
                actualEnd: Date.UTC(2018, 01, 22),
                connectTo: "1_2",
                connectorType: "finish-start",
                progressValue: "75%"
            },
            {
                id: "1_2",
                name: "Task Design and Prototyping",
                actualStart: Date.UTC(2018, 01, 23),
                actualEnd: Date.UTC(2018, 02, 20),
                connectTo: "1_3",
                connectorType: "start-start",
                progressValue: "60%"
            },
            {
                id: "1_3",
                name: "Task Evaluation Meeting",
                actualStart: Date.UTC(2018, 02, 23),
                actualEnd: Date.UTC(2018, 02, 23),
                connectTo: "1_4",
                connectorType: "start-start",
                progressValue: "80%"
            },
            {
                id: "1_4",
                name: "Application Development",
                actualStart: Date.UTC(2018, 02, 26),
                actualEnd: Date.UTC(2018, 04, 26),
                connectTo: "1_5",
                connectorType: "finish-finish",
                progressValue: "90%"
            },
            {
                id: "1_5",
                name: "Testing",
                actualStart: Date.UTC(2018, 04, 29),
                actualEnd: Date.UTC(2018, 05, 15),
                connectTo: "1_6",
                connectorType: "start-finish",
                progressValue: "60%"
            },
            {
                id: "1_6",
                name: "Deployment",
                actualStart: Date.UTC(2018, 05, 20),
                actualEnd: Date.UTC(2018, 05, 27),
                connectTo: "1_7",
                connectorType: "start-finish",
                progressValue: "100%"
            },
            {
                id: "1_7",
                name: "Maintenance",
                actualStart: Date.UTC(2018, 05, 30),
                actualEnd: Date.UTC(2018, 06, 11),
                progressValue: "40%"
            },

        ]
    }];
    // create a data tree
    var treeData = anychart.data.tree(data, "as-tree");

    // create a chart
    var chart = anychart.ganttProject();

    // set the data
    chart.data(treeData);
    // configure the scale
    chart.getTimeline().scale().maximum(Date.UTC(2018, 06, 30));
    // set the container id
    chart.container("container");
    // initiate drawing the chart
    chart.draw();
    // fit elements to the width of the timeline
    chart.fitAll();
});

<script src="https://cdn.anychart.com/releases/8.6.0/js/anychart-gantt.min.js"></script>
<script src="https://cdn.anychart.com/releases/8.6.0/js/anychart-core.min.js"></script>
<div id = "container" > </div>

Answer 1

If you are asking for options:

1. Render your Model data directly to the html page within your js script (page only gets loaded after your data is retrieved by your php app from your database, so could be slower to display anything if you have large data set)

• follow https://www.phptpoint.com/codeigniter-display-data-database/

• In your VIEW (html), you would have your code above and just print the php value out into your JS data

  // your view
  <script>
      anychart.onDocumentReady(function () {
         var data = <?= $data ?>
  

2. restful api and Ajax (faster page load with blank chart area until ajax response came back with data, you can have a placeholder and replace after the response)

• Map your Controller to the Route.php as follow https://www.guru99.com/codeigniter-url-routing.html, and create a GET resource

• In your Js, you dont really need to wrap this with onready, since it will only fetch after you render your page.

   // use something like axios, fetch...
   fetch('http://example.com/project/data')
        .then(response => (
             anychart.onDocumentReady(function () {
                   var data = response //.yourStructure
             ...continue the rest of your scripts....
        ))
  

if you are asking how to get data denormalised with CI model

use query builder and do joints

    $builder = $db->table('project');
    $builder->select('*');
    $builder->join('tasks', 'tasks.project_id = project.id');
    $builder->join('milestone', 'milestone.project_id = project.id');
    $query = $builder->get();

NOTE: make sure your Data has been JSON_encoded when returning out from PHP, you can do that either in model, controller or views. up to you