Retrieving Data from MySQL

In this guide, we'll walk you through how to retrieve data from MySQL with PHP or Node/Express and incorporate it in your grid.

For our example, we set up a database mydb, which contains the table user:

mysql> SELECT * FROM user;
+-------+------+
| name  | age  |
+-------+------+
| Tim   |   21 |
| Alice |   30 |
| Henry |   45 |
+-------+------+

Using PHP

Connecting to the Database

To retrieve our data using PHP, we'll start by creating a connection to MySQL in our PHP file, like so:

<script>
  <?php
    $mysqli = new mysqli("localhost", "user", "password", "mydb");

    if($mysqli->connect_error) {
      die('Connect Error (' . $myssqli->connect_errno . ')' . $mysqli->connect_error);
    }
  ?>
</script>

Then, we'll execute a query to get the rows we want from our database. In this case, we are getting all rows from the user table:

<script>
  <?php
    ...
    $mydata = []; /* will be used to store result array */

    if ($result = $mysqli->query("SELECT * FROM user")) {
      $mydata = $result->fetch_all(MYSQLI_ASSOC);
      $result->close();
    }
  ?>
</script>

Populating the Grid

We'll need to convert the PHP array so that we can use it in our JavaScript code. Then, we can close the connection to MySQL:

<script>
    ...
    var dataValues = <?php echo json_encode($mydata) ?>;
    <?php
      $mysqli->close();
    ?>
</script>

Now, we'll create a <zing-grid> tag and set its data:

<script>
  window.onload = function() {
    document.querySelector('zing-grid').data = dataValues;
  }
</script>
<zing-grid></zing-grid>

MySQL and PHP Grid

Here is our complete grid pulling in data from MySQL with PHP:

Using Node/Express

Connecting to the Database

To retrieve our data using Node/Express, we'll start by creating a connection to the server and our database mydb in our server.js file:

var mysql = require('mysql');
var express = require('express');
var app = express();

var connection = mysql.createConnection({
  host: 'localhost',
  port: 3306,
  user: 'user',
  password: 'password',
  database: 'mydb'
})

Populating the Grid

To fetch the entries in the user table to use in our grid, we'll create a route path at /mydb, which will then display the table contents.

app.get('/mydb', function(req, res) {
  connection.query(queryString, function(err, rows, fields) {
    if (err) throw err;
    res.send(rows);
  });
});

Now in our HTML file, we can create our grid and set the src attribute to this URL to read from the database.

<zing-grid src="http://localhost:3000/mydb"></zing-grid>

Then, we'll run our server from the command line with:

node server.js

MySQL and Node/Express Grid

Here is our complete grid pulling in data from MySQL with Node/Express: