Documentation
In this tutorial you load rows from a MySQL (or MariaDB) table into jqxGrid. A PHP 8.3 script reads the table with PDO and returns JSON, and the grid loads that JSON through jqx.dataAdapter.
You need PHP 8.3 with the pdo_mysql extension and MySQL 8 or MariaDB 10.11+. jQuery is not required: jqxcore.js provides the $ function the examples use.
The example uses the customers table of the Northwind sample database. If you do not have Northwind installed, this script creates the table with a few rows:
CREATE DATABASE IF NOT EXISTS northwind CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE northwind;
CREATE TABLE customers (
CustomerID CHAR(5) NOT NULL PRIMARY KEY,
CompanyName VARCHAR(40) NOT NULL,
ContactName VARCHAR(30),
ContactTitle VARCHAR(30),
Address VARCHAR(60),
City VARCHAR(15),
Country VARCHAR(15)
);
INSERT INTO customers VALUES
('ALFKI', 'Alfreds Futterkiste', 'Maria Anders', 'Sales Representative', 'Obere Str. 57', 'Berlin', 'Germany'),
('ANATR', 'Ana Trujillo Emparedados y helados', 'Ana Trujillo', 'Owner', 'Avda. de la Constitución 2222', 'México D.F.', 'Mexico'),
('AROUT', 'Around the Horn', 'Thomas Hardy', 'Sales Representative', '120 Hanover Sq.', 'London', 'UK'),
('BERGS', 'Berglunds snabbköp', 'Christina Berglund', 'Order Administrator', 'Berguvsvägen 8', 'Luleå', 'Sweden'),
('BLAUS', 'Blauer See Delikatessen', 'Hanna Moos', 'Sales Representative', 'Forsterstr. 57', 'Mannheim', 'Germany'),
('BONAP', 'Bon app''', 'Laurence Lebihan', 'Owner', '12, rue des Bouchers', 'Marseille', 'France');
Create a database user for the application instead of connecting as root, and give it only the rights it needs:
CREATE USER 'northwind_app'@'localhost' IDENTIFIED BY 'change-me';
GRANT SELECT ON northwind.* TO 'northwind_app'@'localhost';
Create a project folder, my-app, with three files:
db.php: the PDO connection and a JSON helper.customers.php: the JSON endpoint.index.html: the page with the grid.db.php opens one PDO connection per request. The credentials come from environment variables, so they are not stored in the code. Exceptions are logged on the server, and the client gets a generic JSON error.
<?php
declare(strict_types=1);
function db(): PDO
{
static $pdo = null;
if ($pdo === null) {
$dsn = sprintf(
'mysql:host=%s;dbname=%s;charset=utf8mb4',
getenv('DB_HOST') ?: '127.0.0.1',
getenv('DB_NAME') ?: 'northwind'
);
$pdo = new PDO($dsn, getenv('DB_USER') ?: 'northwind_app', getenv('DB_PASS') ?: '', [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
]);
}
return $pdo;
}
function send_json(mixed $data, int $status = 200): never
{
http_response_code($status);
header('Content-Type: application/json; charset=utf-8');
echo json_encode($data, JSON_THROW_ON_ERROR | JSON_UNESCAPED_UNICODE);
exit;
}
set_exception_handler(static function (Throwable $e): void {
error_log((string) $e);
send_json(['error' => 'Server error'], 500);
});
customers.php runs a fixed query and returns the result as a JSON array. The query has no user input, but it still goes through prepare() so that adding a parameter later stays safe.
<?php
declare(strict_types=1);
require __DIR__ . '/db.php';
if ($_SERVER['REQUEST_METHOD'] !== 'GET') {
send_json(['error' => 'Method not allowed'], 405);
}
$stmt = db()->prepare(
'SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Country
FROM customers
ORDER BY CompanyName'
);
$stmt->execute();
send_json($stmt->fetchAll());
Open http://localhost:8000/customers.php after step 5 to check the output. It looks like this:
[
{
"CustomerID": "ALFKI",
"CompanyName": "Alfreds Futterkiste",
"ContactName": "Maria Anders",
"ContactTitle": "Sales Representative",
"Address": "Obere Str. 57",
"City": "Berlin",
"Country": "Germany"
}
]
index.html loads the jqWidgets scripts from the jsDelivr CDN. If you installed the jqwidgets-scripts npm package, point the paths at node_modules/jqwidgets-scripts/jqwidgets/ instead.
The source object tells the data adapter where the data is (url), that it is JSON (datatype) and which fields each record has (datafields). The grid columns then refer to those fields.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Customers</title>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jqwidgets-scripts@27/jqwidgets/styles/jqx.base.css">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jqwidgets-scripts@27/jqwidgets/styles/jqx.material-purple.css">
<script src="https://cdn.jsdelivr.net/npm/jqwidgets-scripts@27/jqwidgets/jqxcore.js"></script>
<script src="https://cdn.jsdelivr.net/npm/jqwidgets-scripts@27/jqwidgets/jqxdata.js"></script>
<script src="https://cdn.jsdelivr.net/npm/jqwidgets-scripts@27/jqwidgets/jqxbuttons.js"></script>
<script src="https://cdn.jsdelivr.net/npm/jqwidgets-scripts@27/jqwidgets/jqxscrollbar.js"></script>
<script src="https://cdn.jsdelivr.net/npm/jqwidgets-scripts@27/jqwidgets/jqxmenu.js"></script>
<script src="https://cdn.jsdelivr.net/npm/jqwidgets-scripts@27/jqwidgets/jqxgrid.js"></script>
<script src="https://cdn.jsdelivr.net/npm/jqwidgets-scripts@27/jqwidgets/jqxgrid.selection.js"></script>
</head>
<body>
<h1>Customers</h1>
<div id="grid"></div>
<script>
const source = {
datatype: 'json',
url: 'customers.php',
id: 'CustomerID',
datafields: [
{ name: 'CustomerID', type: 'string' },
{ name: 'CompanyName', type: 'string' },
{ name: 'ContactName', type: 'string' },
{ name: 'ContactTitle', type: 'string' },
{ name: 'Address', type: 'string' },
{ name: 'City', type: 'string' },
{ name: 'Country', type: 'string' }
]
};
const dataAdapter = new jqx.dataAdapter(source, {
loadError: (xhr, status, error) => console.error('Could not load customers:', error)
});
$('#grid').jqxGrid({
theme: 'material-purple',
width: '100%',
autoheight: true,
source: dataAdapter,
columns: [
{ text: 'Company Name', datafield: 'CompanyName', width: 250 },
{ text: 'Contact Name', datafield: 'ContactName', width: 160 },
{ text: 'Contact Title', datafield: 'ContactTitle', width: 180 },
{ text: 'Address', datafield: 'Address', width: 200 },
{ text: 'City', datafield: 'City', width: 120 },
{ text: 'Country', datafield: 'Country' }
]
});
</script>
</body>
</html>
The inline script is at the end of <body>, so the #grid element already exists when it runs.
For development, PHP's built-in web server is enough. Set the database password and start it from the project folder:
cd my-app
DB_PASS=change-me php -S localhost:8000
In PowerShell, set the variable first: $env:DB_PASS = 'change-me'; php -S localhost:8000.
Open http://localhost:8000/. The grid requests customers.php, receives the JSON array and shows one row per customer. In production, serve the same files with Apache or nginx and PHP-FPM, and set the environment variables in the server configuration.