{"id":11032,"date":"2020-08-04T04:30:26","date_gmt":"2020-08-04T04:30:26","guid":{"rendered":"https:\/\/www.anychart.com\/blog\/?p=11032"},"modified":"2022-08-13T11:01:24","modified_gmt":"2022-08-13T11:01:24","slug":"mysql-database-php-js-chart","status":"publish","type":"post","link":"https:\/\/www.anychart.com\/blog\/2020\/08\/04\/mysql-database-php-js-chart\/","title":{"rendered":"Connecting MySQL Database to JS Chart Using PHP"},"content":{"rendered":"

\"ConnectingWhen using a charting library<\/a> like AnyChart<\/a>, which makes visualizing data so quick and easy, often the most challenging step for beginners is loading data. I think this is largely a result of the fact that while the ways we output the data are quite similar, the input data is commonly very different. The data may differ in many ways including the number of features in the dataset, what type these features may be, the format of the data, and how the data is stored<\/strong>. It is this final issue which we will be addressing today.<\/p>\n

There is a lot of coverage in the documentation<\/a> of using a data object declared within the code as well as importing your data from a file using AnyChart\u2019s data loader. But what about importing the data from an SQL<\/a> database? This is an easily done but often misunderstood approach to handling and loading data for data visualizations.<\/p>\n

Unfortunately, it is not possible to use JavaScript<\/a> to query a database because JavaScript is a client side solution and querying a database requires a server side approach<\/strong> due to security concerns \u2014 you just can\u2019t store database login and password in your JavaScript code<\/strong>. There are many workarounds for this problem including approaches involving PHP, Ajax, Node.js, and many more. Today we will focus on using PHP<\/a> to handle this task for us when our data is stored in MySQL<\/a>.<\/p>\n

In this tutorial, we will be covering how to query a MySQL database using PHP<\/strong>. We will describe the steps involved and reveal that it is in fact not challenging at all, just different!<\/p>\n

<\/p>\n

How to Access MySQL Database Using PHP<\/h2>\n

Our process can be broken down into two clearly defined steps. First, we will use PHP to access our MySQL database before querying the table and appending the result into a JSON<\/a> object. The second step simply draws the chart in the same way you\u2019ve been used to.<\/p>\n

1. Create a PHP script<\/h3>\n

The first step is to create a PHP script. This can be done by using the following opening and closing tags. All code written within these tags are read by the server as PHP.<\/p>\n

<?php\r\n  \/\/ code goes here.\r\n?><\/code><\/pre>\n
2. Declare MySQL database variables<\/h3>\n
Next, we declare our MySQL database variables required to access our database. We need the username, password, host, and database name. In the code below we have added dummy information for the purpose of showing you how this is done. Please replace these strings with the necessary information for your own database.<\/p>\n
Note that if you have set up a local database, the host name is \u201clocalhost\u201d and often the default username is \u201croot\u201d.<\/p>\n
$username = \"anychart_user\"; \/\/ use your username  \r\n$password = \"password\";      \/\/ use your password\r\n$host = \"localhost\";         \/\/ use your host name or address \r\n$database=\"local_db\";        \/\/ use your database name<\/code><\/pre>\n
3. Connect to database<\/h3>\n
In the third step, we will connect to our database using the previously defined variables. We do so with the following code. The first line creates the connection to the server while the second line connects us to the correct database.<\/p>\n
\/\/ connect to database\r\n$server = mysql_connect($host, $username, $password);\r\n$connection = mysql_select_db($database, $server);<\/code><\/pre>\n
4. Perform query<\/h3>\n
Once our connection is made, we can perform a query. For those of you new to using SQL, a query is effectively an enquiry to a database that returns the requested data. Below, we do a simple query that fetches all the fields and all the values from an example table. You should replace this with the appropriate query for your needs. The first 3 lines create the query string while the final line performs the query.<\/p>\n
\/\/ perform query\r\n\/\/ you may need to tune the query if your database is different\r\n$myquery = \"\r\n  SELECT * FROM  `data`\r\n  \";\r\n$query = mysql_query($myquery);<\/code><\/pre>\n
We then add the following code to let us know if the query returns no data:<\/p>\n
if ( ! $query ) {\r\n  echo mysql_error();\r\n  die;\r\n}<\/code><\/pre>\n
5. Create data from the query<\/h3>\n
In the fifth step, we declare \u2018data\u2019 variable as an empty array before populating this array with the results of our returned query. We do this by iterating over the returned query data with a for loop.<\/p>\n
\/\/ create data object\r\n$data = array();\r\n\r\nfor ($x = 0; $x < mysql_num_rows($query); $x++) {\r\n  $data[] = mysql_fetch_assoc($query);\r\n}<\/code><\/pre>\n
6. Encode data to JSON format<\/h3>\n
Now that we have our data in an array we need to transform it into a format that can be used with AnyChart. AnyChart works with a variety of formats<\/a>, including CSV, JSON, XML, and even Google Spreadsheets.\u00a0This time,\u00a0we will encode our data as a JSON. This is easily done with the following code:<\/p>\n
\/\/ encode data to json format\r\necho json_encode($data);<\/code><\/pre>\n
7. Close connection<\/h3>\n
The final step in our PHP script is to close the connection we have with our server.<\/p>\n
\/\/ close connection\r\nmysql_close($server);<\/code><\/pre>\n
With everything put together we have the following PHP script:<\/p>\n
<?php\r\n  \/\/ declare database variables\r\n  \/\/ change to the information relevant\r\n  \/\/ to your database\r\n  $username = \"anychart_user\";\r\n  $password = \"password\";  \r\n  $host = \"localhost\";\r\n  $database=\"local_db\";\r\n \r\n  \/\/ connect to database\r\n  $server = mysql_connect($host, $username, $password);\r\n  $connection = mysql_select_db($database, $server);\r\n \r\n  \/\/ perform query\r\n  \/\/ change the query to one relevant to your database\r\n  $myquery = \"\r\n    SELECT  * FROM  `data`\r\n    \";\r\n  $query = mysql_query($myquery);\r\n \r\n  if ( ! $query ) {\r\n    echo mysql_error();\r\n    die;\r\n  }\r\n \r\n  \/\/ encode data to json format\r\n  echo json_encode($data);  \r\n \r\n  \/\/ close connection\r\n  mysql_close($server);\r\n?><\/code><\/pre>\n
The above code connects to our MySQL database, queries it, and returns data which it then exposes in a JSON format. With our data all prepared we can get onto the fun part: JS charting<\/a>!<\/p>\n
8. Draw Chart<\/h3>\n
Once we have our data imported using PHP it is very easy to use with AnyChart. All we need to do is use the same data adapter module we\u2019ve used for importing files. We can do this with the following script:<\/p>\n
<script src=\"https:\/\/cdn.anychart.com\/releases\/8.8.0\/js\/anychart-data-adapter.min.js\"><\/script><\/code><\/pre>\n
Once that is done we simply refer to our\u00a0PHP script when loading the data. Like this:<\/p>\n
anychart.data.loadJsonFile(\"php\/data.php\", function (data) {\r\n  \/\/ chart code goes here\r\n})\r\n<\/code><\/pre>\n
And otherwise, it is business as usual with the entire HTML\/CSS\/JavaScript code below:<\/p>\n
<html>\r\n<head>\r\n  <script src=\"https:\/\/cdn.anychart.com\/releases\/8.8.0\/js\/anychart-base.min.js\"><\/script>\r\n  <script src=\"https:\/\/cdn.anychart.com\/releases\/8.8.0\/js\/anychart-data-adapter.min.js\"><\/script>\r\n  <style type=\"text\/css\">\r\n    html,\r\n    body,\r\n    #container {\r\n      width: 100%;\r\n      height: 100%;\r\n      margin: 0;\r\n      padding: 0;\r\n    }\r\n  <\/style>\r\n<\/head>\r\n<body>\r\n  <div id=\"container\"><\/div>\r\n  <script>\r\n    anychart.onDocumentReady(function () {\r\n      anychart.data.loadJsonFile(\"php\/data.php\", function (data) {\r\n        \/\/ create a chart and set loaded data\r\n        chart = anychart.bar(data);\r\n        chart.container(\"container\");\r\n        chart.draw();\r\n      });\r\n    });\r\n  <\/script>\r\n<\/body>\r\n<\/html><\/code><\/pre>\n
Conclusion<\/h2>\n
As you can see this approach to handling data for data visualizations is a lot more accessible than you would think. By following these clearly defined steps we can easily use PHP to query our database, retrieve the data as a JSON object before visualizing it using AnyChart.<\/p>\n
The example\u00a0of\u00a0connecting a MySQL database\u00a0to\u00a0JS charts created with AnyChart\u00a0described in this tutorial is pretty basic. If you are a more experienced developer and feel ready for more complicated integrations, you may also be interested in checking out PHP, Symfony and MySQL<\/a>, PHP, Laravel and MySQL<\/a>, PHP, Slim and MySQL<\/a>, and other\u00a0PHP integration templates<\/a>,\u00a0as well as MySQL integration templates<\/a> for other languages.<\/p>\n
Although we focused on PHP in this tutorial, it must be noted that the other approaches are viable, easy, and may be better suited to your use case. These include using WebSockets, Comet, Ajax, Node.js, and more. If you are in need of tutorials on any of these technologies please feel free to reach out to me and\/or the AnyChart team and I\u2019m sure we can help you out!<\/p>\n
You can also add more data in JSON output and map the data<\/a> for more complex charts.<\/p>\n