Connect ZingCharts to MySQL (build a line chart)

Question

Thank you. I can now see the second graph but the second graph I'm having does not have any data in it so it is just a blank chart. This is my php code. What am I doing wrong? My code:

    <?php 
    /* Open connection to "zing_db" MySQL database. */
    $mysqli = new mysqli("localhost", "root", "database123", "productno");

    /* Check the connection. */
    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }
?> 



    <script>
/* First line chart */

var myData=[<?php 
$data=mysqli_query($mysqli,"SELECT * FROM records");
while($info=mysqli_fetch_array($data))
    echo '["'.$info['Date'].'",'.$info['Alibaba'].'],'; /* Data is formatted here, using the . concatenation operator */
    echo '[]'; /* We'll end up with a trailing comma, so we add an empty element here to pop off later*/
?>];

//--------- Second Line Chart ---------------

var myData1=[<?php 
$data1=mysqli_query($mysqli,"SELECT * FROM records");
while($info1=mysqli_fetch_array($data1))
    echo '["'.$info1['Date'].'",'.$info1['Lelong'].'],'; /* Data is formatted here, using the . concatenation operator */
    echo '[]'; /* We'll end up with a trailing comma, so we add an empty element here to pop off later*/
?>];

<?php
/* Close the connection */
$mysqli->close();
?>
myData.pop(); /* Pop off the final, empty element from the myData array */

//Display first line chart

    window.onload=function(){
    zingchart.render({
        id:"AlibabaChart",
        width:"100%",
        height:300,
        data:{
        "type":"line",
        "title":{
            "text":"Alibaba"
        },
        "series":[
            {
                "values":myData
            }
    ]
    }
    });

//Display second line chart

zingchart.render({
        id:"LelongChart",
        width:"100%",
        height:300,
        data:{
        "type":"line",
        "title":{
            "text":"Lelong"
        },
        "series":[
            {
                "values":myData1
            }
    ]
    }
    });

    };

</script>

FYI, the data are from the same table, x value and database but different columns(y value). Thanks!

Answer 1

I'm on the team here at ZingChart, and I'll be happy to help you out.

I've replicated your MySQL database setup using your specified structure to better assist you, and I've filled it up with some dummy data that looks like this:

Date,Alibaba
1/13/11,70
2/13/11,42
3/13/11,33
4/13/11,3
5/13/11,28
...

First, we need to open a connection to the database:

<?php 
/* Open connection to database */
$mysqli = new mysqli("localhost", "root", "pass", "productno");
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
?>

Once we have opened a connection successfully, the next step is to format the data into a format readable by ZingChart. Assuming that you want to use your date values along the x-axis, perhaps it would be best to format your data as ordered pairs like this:

"values":[ 
    [Date(string),value(int)],
    [Date2(string),value2(int)],
    [Date3(string),value3(int)]
    ... 
    ]

In the next step, we will query the database, and use concatenation to wrap quotation marks around each date, and to wrap each Date,Alibaba ordered pair in square brackets.

<script>
/* Create myData variable in js that will be filled with db data */
var myData=[<?php 
$data=mysqli_query($mysqli,"SELECT * FROM records");
while($info=mysqli_fetch_array($data))
    echo '["'.$info['Date'].'",'.$info['Alibaba'].'],'; /* Data is formatted here, using the . concatenation operator */
    echo '[]'; /* We'll end up with a trailing comma, so we add an empty element here to pop off later*/
?>];
<?php
/* Close the connection */
$mysqli->close();
?>
myData.pop(); /* Pop off the final, empty element from the myData array */

At this point, if you were to view the page source, variable myData would look like this:

var myData = [
    ["2011-01-13", 70],
    ["2011-02-13", 42],
    ["2011-03-13", 33],
    ...
];

...which means that it's ready to be put into our chart!

    window.onload = function() {
        zingchart.render({
            id: "myChart",
            width: "50%",
            height: 400,
            data: {
                "type": "line",
                "title": {
                    "text": "Data Pulled from MySQL Database"
                },
                "series": [{
                    "values": myData
                }]
            }
        });
    };
</script>

Here's our final result:

I hope that helps you out! Let me know if you have any questions.

Edit 10/20/14:

I see you're having trouble generating two line charts in the same page. In order to render multiple charts in a page, you must include multiple <div> elements with unique ids, with a call to the zingchart.render method for each chart that you want to generate.

Please take a look at this demo.

Notice that there are two <div> elements, one with id="myChartDiv" and another with id="myChartDiv2". We also included two calls to the zingchart.render method in the window.onload function. The first call tells ZingChart to render the chart at the location of the <div> element with id="myChartDiv" and the second call tells ZingChart to render the chart at the location of the <div> element with id="myChartDiv2".

It is possible to use the same array of data in both charts on the page, as seen with the myData variable in the demo provided.

Edit 10/21/14:

Hi again, Cael. I've used your code in my test environment, and it looks like the data isn't loading because you didn't pop off the empty element from the end of your myData1 array.

Simply add:

myData1.pop();

after the line:

myData.pop();

and you should be good to go!