Docs

Retrieving Data from MySQL

In the examples below, we assume that you have MySQL installed and have your data stored in a database. If not, refer to the MySQL Documentation.

For our example, we have a database mydb which contains the table user.

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

Using PHP

In our PHP file, we create a connection to MySQL.

<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 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 convert the PHP array so that we can use it in our JavaScript code, and then we can close the connection to MySQL.

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

Now we create atag and set its data.

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

Result

Top

Using Node/Express

In our server.js file, we are going to create a connection to the server and our database mydb.

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

We want to be able to fetch the entries in the user table to use in our grid. To do so, we will 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.

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

We run our server from the command line with:

node server.js

Result

https://app.zingsoft.com/demos/embed/KQ6ODK7N
https://app.zingsoft.com/demos/embed/KQ6ODK7N
Top