Bootstrap & PHP Demo
By Mark Foyster
How it works!
We can now discuss how the MySQL demo functions. To summarise before we get into depth, there are a number of technologies working here. On the browser (client side), we are using HTML to display the information on the screen. This is styled with CSS which in our case for the most part is handled by Bootstrap. In the case of the second demo where the content of the data is rendered dynamically (without re-directing to a new page) within the browser window, JavaScript plays the important role of handling the interaction with the user and the server.
The server uses PHP to communicate with the JavaScript from the client. In turn, it interacts with the MySQL database, extracting the necessary infomration and returning it to the JavaScript on the client side. I will explain the design pattern we have implemented in more detail below.
The Structure
The easiest way to illustrate the design pattern used is with a diagram:
We can say that the design pattern roughly aligns to MVC (Model View Controller). The View is rendered in the browser window with our HTML and Bootstrap. This provides the necessary features and facets for the user to interact with the data.
Back on the server, we have the MySQL database that stores all the data we wish the user to have access to. This is our MODEL. The view and the model have no direct interaction whatsoever, this not only simplifies the user experience, it provides the necessary abstraction to preserve data security.
However, we still need a way to connect the data to the user in the appropriate format. This is where the CONTROLLER comes in. In our case, I consider this role to be split over client and server side. The JavaScript on the client machine acts to all intents and purposes like an API (Application Programming Interface) It allows the REQUEST made within the HTML DOM (Document Object Model) to be passed to the PHP application running on the server. In a similar manner, the PHP is able to send a RESPONSEback to the JavaScript containing the appropriate data which is then sent back to the HTML DOM.
In a similar manner, again loosely speaking, the PHP on the server acts as our server based API, enabling bi-directional communication between the MySQL database and the JavaScript running on the client machine.
NOTE: In respect of security, the only worthwhile abstraction is provided by PHP, NOT JavaScript.
Let's talk code!
That's the theory over with, let's get our hands dirty with some code. I am not going to put every line of code on here, If you want to see the whole project line by line, it is available on this GitHub repository. It's public, so anyone can have a look. Disclaimer, this is just an exercise and there is significant room for improvement, but it works!
Let's start with the JavaScript that communicates with the server. For this we use the fetch() API:
function getData(opts, stringToQuery="", anotherStringToQuery="") {
var formData = new FormData();
formData.append('queryType', opts);
formData.append('queryString', stringToQuery);
formData.append('queryString2', anotherStringToQuery);
var searchString = searchBox.value;
formData.append ('searchString', searchString);
fetch('includes/modelcarsall.php', {
method: 'post',
body: formData
}).then(function(response) {
return response.json();
}).then(function(response) {
switch (opts){
case "productLine":
dBaseProductLines = response;
populateProductLineListBox();
break;
case "productVendor":
dBaseProductVendor = response;
populateProductVendorListBox();
break;
case "byID":
thisRecord = response;
showItem();
break;
case "all":
case "allByProductLine":
case "allByProductVendor":
case "allByLineAndVendor":
dBaseResult = response;
Validate();
break;
}
}).catch (function (error){
noData();
});
}
The main thing to notice here is that at NO stage do I use SQL statements within the JavaScript. This makes it a little harder to know exactly what's going on server side. There are technologies nowadays that can tell the curious what is running on the backend, but I have no wish to volunteer that information.
The fetch() API is very useful. Not only does it make handling the response asynchronously much easier by automatically obtaining a Promise(), It provides us with an easy way to receive the data in the form of a JSON (JavaScript Object Notation) which we can now store locally within the client to access and render in the DOM when we are ready.
Before any of this can happen of course, we need to communicate the kind of information we wish to request. We do this by building a post request into a FormData() object. This gives us a 'virtual' form which we can use to send POST data to the server in the usual name: value pairs. As you can see, I use queryType, queryString, queryString2 and searchString names to send this information.
The code that can only be resolved when the data is successfully received is handled in the LAST .then() statement. The former one simply receives the JSON and allows us to store it.
The final noteworthy part of the code here is the .catch() segment. Here, I simply call a function (noData()) that enters one result to the display, prompting the user that no data was received, like this:
function noData(){
displayString = "<div class=\"row py-2\"><div class=\"col p-2 m-2 border bg-primary\"> SORRY, NO MATCH</div></div>";
responseBox.innerHTML = displayString;
numResults.innerHTML= "0";
}
The rest of the code either listens to the DOM for user inputs to resolve the nature of the request or serves to render the response received.
The PHP Code
Now, let's consider the server side. We need to receive the post request and then send back a response, we'll get the post data first:
$queryType = $_POST['queryType'];
$queryString = $_POST['queryString'];
$anotherQueryString = $_POST['queryString2'];
$searchString = $_POST['searchString'];
We do need to connect to the database, so I have a separate PHP file to do that. I use require_once() to import it and start setting up some other connection details next:
require_once("dbasefunctions.php");
$driver = new mysqli_driver();
$dbase = "classicmodels";
$driver->report_mode = MYSQLI_REPORT_STRICT | MYSQLI_REPORT_ERROR;
This file handles a bulk of the connection AND provides a mechanism to escape any strings sent by POST that will be embedded directly into a SQL statement. Something, you will note I keep to the minimum. I will discuss that file more later. The rest of the code sets up our mysqli_driver(). This is a nice and relatively secure API within PHP for connecting to MySQL databases. Next we set a variable for the database name and finally provide some more configuration information for the mysqli API.
Now for the rest of the code. I'll display that in one go, it's easier to explain that way:
try
{
//set up our connection
$conn = SetUpConnection($dbase);
$error = ""; //Variable for storing our errors.
$sql="";
switch($queryType){
case "all":
$sql = "select productCode, productName, productLine, productScale, buyPrice from products";
break;
case "allByProductLine":
$queryString = EscapeInput ($conn,$queryString); //escape input
$sql = "select productCode, productName, productLine, productScale, buyPrice from products where productLine like '$queryString'";
break;
case "allByProductVendor":
$queryString = EscapeInput ($conn,$queryString); //escape input
$sql = "select productCode, productName, productLine, productScale, buyPrice from products where productVendor like '$queryString'";
break;
case "allByLineAndVendor":
$queryString = EscapeInput ($conn,$queryString); //escape input
$anotherQueryString = EscapeInput ($conn,$anotherQueryString); //escape input
$sql = "select productCode, productName, productLine, productScale, buyPrice from products where productLine like '$queryString' and productVendor like '$anotherQueryString'";
break;
}
//add our search box
if ($searchString !=""){
$searchString = EscapeInput ($conn,$searchString); //escape input
if ($queryType == "all") $sql .= " where productName like '%$searchString%';";
else $sql .= " and productName like '%$searchString%';";
}
else $sql .= ";";
//now our clickable result (here so it's CLEAN without the search string)
if ($queryType == "byID"){
$queryString = EscapeInput ($conn,$queryString); //escape input
$sql = "select * from products where productCode like '$queryString';";
}
//We do these two here to save having to worry about them being altered by the search query.
switch($queryType){
case "productLine":
$sql = "select distinct productLine from products;";
break;
case "productVendor":
$sql = "select distinct productVendor from products;";
break;
}
$result = mysqli_query($conn,$sql); //do the query
if ($result = mysqli_query($conn,$sql)) //if a valid search
{
while($row = $result->fetch_assoc())
{
$rows[] = $row;
}
//header('Content-type: application/json');
echo json_encode($rows);
}
else
{
return false;
}
}
catch (mysqli_sql_exception $e)
{
echo "Sorry, an Error occurred
";
//echo $e; //*******REMOVE BEFORE UPLOADING*****/
return false;
}
NOTE: This whole last section is encapsulated within a try(), catch() block. The catch() block allows us to stop PHP from crashing entirely and displaying information we do not want the user to see. Note, I have a commented out echo "" statement that I used for debugging purposes to display any errors during development.
within the try() block, first we use our imported SetUpConnection() function to make a connection to the database. Next a switch() block followed by some if() statements help build up a SQL query statement for us to use later. Where we embed our variables obtained through a POST, we use another function imported, EscapeInput ().
Drawing to a close, we make our query, again using the mysqli API, this time mysqli_query() that we then build into an associated array using a while() loop. The last stop is to use json_encode() to turn our associated array into a JSON which we echo out. The echo statement quite nicely gives us a response which back in our JavaScript our fetch() API can handle.
The dbasefunctions file
This is very simple really:
function SetUpConnection($dbase)
{
$server = "localhost";
$user = "root";
$password = "";
$driver = new mysqli_driver();
//set report mode for errors
$driver->report_mode = MYSQLI_REPORT_STRICT | MYSQLI_REPORT_ERROR;
try
{
$conn = mysqli_connect($server, $user, $password, $dbase);
}
catch (mysqli_sql_exception $e)
{
//echo "Sorry, an Error occurred
";
return false;
}
return $conn;
}
function EscapeInput($conn, $input)
{
$escapedInput = $conn->real_escape_string($input);
return $escapedInput;
}
It just specifies the connection detalis like username and password for the database (a good reason to have it 'twice removed' from the client!) and makes the connection. Note, as I write this, I see I have some duplicated code which I can remove from the main file. The $driver is set up twice at present, as is the report mode. That will have to be corrected.
The second function (escapeInput()) just takes a string and uses the mysqli API function to escape the input thus helping avoid any SQL Injection attacks.
That covers the bulk of what is happening here.