[Record:IT][PHP][SQLite] Using PHP return SQLite in JSON for JavaScript at HTML

Using SQLite as the database of a project, and as some unexpected issues, need to draw the data from the SQLite for showing at the webpage.

For quick completion, with the previous experiences, the webpage to be adopted HTML and using JavaScript to get the data from the server with PHP.

How to send the SQLite to HTML? After surfing a while, the JSON format is use for the data exchange between the server and the client.

Flow: First, the bare frame of the webpage will render with basic HTML5 <table>, <thead> is used to set the head of the columns.

Then, using XMLHTTPREQUEST to get the JSON via the PHP to encode the json data from SQLite file.

After pulled the JSON, use looping to generate the HTML code with the data and store in a variable. When all data had been processed, the concatenated string will insert to the <tbody> and finish the table generation.

PHP:

<?php
	$db_file = “path/to/file.db”;
	$db = new SQLITE3($db_file);
	$sql = “SELECT * FROM table_name WHERE conditions”;
	$result = $db->query($sql);
	$jsonArray = [];
	while($row = $result->fetchArray(SQLITE3_ASSOC)) {
		array_push($jsonArray, $row);
	}
	echo json_encode($jsonArray);
?>

HTML:

(TN-20240403)