Bind Grid to MySql Database using PHP

In this help topic, we will show you how to connect our Grid to MySql Database using PHP. We will obtain the data from MySql Database.

To data bind the Grid to MySQL DB, you need to set its dataSource property.

dataSource: new Smart.DataAdapter({
	dataSource: 'grid_data.php',
	dataSourceType: 'json',
	dataFields: [
		'CompanyName: string',
		'ContactName: string',
		'ContactTitle: string',
		'Address: string',
		'City: string',
		'Country: string'
	]
})

The above code shows how to populate the Grid with data from MySQL server. grid_data.php

<?php
// Include the connect.php file
include ('../../../scripts/connect.php');

// Connect to the database
// connection String
$mysqli = new mysqli($hostname, $username, $password, $database);
/* check connection */
if (mysqli_connect_errno())
	{
	printf("Connect failed: %s\n", mysqli_connect_error());
	exit();
	}
// get data and store in a json array
$from = 0;
$to = 100;
$query = "SELECT CompanyName, ContactName, ContactTitle, Country, Address, City FROM Customers LIMIT ?,?";
$result = $mysqli->prepare($query);
$result->bind_param('ii', $from, $to);
$result->execute();
/* bind result variables */
$result->bind_result($CompanyName, $ContactName, $ContactTitle, $Country, $Address, $City);
/* fetch values */
while ($result->fetch())
	{
	$orders[] = array(
		'CompanyName' => $CompanyName,
		'ContactName' => $ContactName,
		'ContactTitle' => $ContactTitle,
		'Country' => $Country,
		'Address' => $Address,
		'City' => $City
	);
	}
echo json_encode($orders);
/* close statement */
$result->close();
/* close connection */
$mysqli->close();
?>

connect.php

<?php
# FileName="connect.php"
$hostname = ";
$database = "";
$username = "";
$password = "";
error_reporting(E_ERROR | E_PARSE);
?>

Let's extend the above example, by adding Filter, Sorting and Paging. That can be achieved by using the virtualDataSource property.

appearance: {
	alternationStart:0,
	alternationCount: 2
},
sorting: {
	enabled: true
},
filtering: {
	enabled: true
},
pager: {
	visible: true
},
paging: {
	enabled: true
},
dataSource: new Smart.DataAdapter( {
	virtualDataSource: function ( resultCallbackFunction: any, details: any ) {
		const sqlQuery = details.query;

		new Smart.Ajax({
			url: 'grid_data.php',
			dataSourceType: 'json',
			data: details.query
		}, (response: any) => {
			resultCallbackFunction( {
				dataSource: JSON.parse(response.data),
				virtualDataSourceLength: parseInt(response.length)
			} );
		});
	},
	dataFields: [
		'CompanyName: string',
		'ContactName: string',
		'ContactTitle: string',
		'Address: string',
		'City: string',
		'Country: string'
	]
} ),
columns: [
	{ label: 'Company Name', dataField: 'CompanyName' },
	{ label: 'Contact Name', dataField: 'ContactName' },
	{ label: 'Contact Title', dataField: 'ContactTitle' },
	{ label: 'Address', dataField: 'Address' },
	{ label: 'City', dataField: 'City' },
	{ label: 'Country', dataField: 'Country' }
]
};

In the above code, whenever the Grid needs data, it requests it by calling the the 'virtualDataSource' function. The function has two arguments - details which is a JSON object and 'resultCallbackFunction', which we call when we are ready to provide the new data to the Grid. Online example: Grid with Mysql on demand

<?php
// Include the connect.php file
include ('../../../scripts/connect.php');

// Connect to the database
// connection String
$mysqli = new mysqli($hostname, $username, $password, $database);
/* check connection */
if (mysqli_connect_errno())
	{
	printf("Connect failed: %s\n", mysqli_connect_error());
	exit();
}

// get data and store in a json array
$query = "SELECT CompanyName, ContactName, ContactTitle, Country, Address, City FROM Customers" . $_GET[ 'where' ] . $_GET[ 'groupBy' ] . $_GET[ 'orderBy' ] . $_GET[ 'limit' ];
$sqlCount = "SELECT COUNT(CustomerID) as length from Customers" . $_GET[ 'where' ];

if (!$_GET[ "groupBy" ]) {
//	$sqlCount = "SELECT COUNT(DISTINCT " .  $_GET[ "groupBy" ] . ") as length from ? " . $_GET[ "where" ];
}

$result = $mysqli->prepare($query);

$result->execute();
/* bind result variables */
$result->bind_result($CompanyName, $ContactName, $ContactTitle, $Country, $Address, $City);
/* fetch values */
while ($result->fetch())
	{
	$data[] = array(
		'CompanyName' => $CompanyName,
		'ContactName' => $ContactName,
		'ContactTitle' => $ContactTitle,
		'Country' => $Country,
		'Address' => $Address,
		'City' => $City
	);
	}

$result = $mysqli->prepare($sqlCount);

$result->execute();
/* bind result variables */
$result->bind_result($length);
/* fetch values */
$count = $result->fetch();

$serverData["data"] = json_encode($data);
$serverData["length"] = json_encode($length);

echo json_encode($serverData);

/* close statement */
$result->close();
/* close connection */
$mysqli->close();
?>

PHP & MySQL Example