When PHP was the only way to exchange with a database, there was no question about which technology to use: one is to simply use PHP code combined with SQL queries. However, since 2006, many frameworks have emerged from the previously neglected Javascript. The most famous ones being jQuery, React, Angular, Vue, among others…
This is when Javascript experienced its greatest expansion and is now one of the most used languages to boost a website and create an application while keeping the same code base (framework agnostic).
In this article you will find the basic code knowledges to display and update your database asynchronously. Everything is developed in Javascript, jQuery, JSON, PHP and SQL. Using a Framework here would have combined these technologies directly, but the detail of the procedure is much more transparent.
Be aware that for this example, the folder structure matters. All procedures and PHP files should be in a “server” folder and the HTML/JS/CSS code in a client folder.
Pre-requisites and installation
Pre-requisites are needed in Javascript and PHP/SQL to understand the entire example explained below. The first step is to first create a “MyProject” folder and add an index.html file there. You should also have a local server and create your database on Phpmyadmin. Include a post table with: an ID (primary key – auto-increment), a title (varChar), a description and a timeStamp (auto completed).
To give a clean basic structure, avoid writing the CSS by hand and keep a good visibility on browser, I use Bootstrap here. Make sure you integrate it well on your pages. However, it is not necessary to use it.
The most important things here before moving forward are to import to your project are the jQuery and GetUri files. Also make sure that you have your database created with a proper structure on PhpMyAdmin. Without these it will be impossible to exchange datas with your database.
In your HTML header, add the following:
<!-- Bootstrap -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">
<!-- Intégration des librairies jQuery et getUri -->
<script src="jquery.js"></script>
<script src="geturi.js"></script>
<!-- jQuery - Link -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js">You can also integrate the Bootstrap library into your HTML (not necessary though):
<!-- Latest compiled and minified CSS --> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous"> <!-- Optional theme --> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" integrity="sha384-rHyoN1iRsVXV4nD0JutlnGaslCJuC7uwjduW9SVrLvRYooPp2bWYgmgJQIXwl/Sp" crossorigin="anonymous"> <!-- Latest compiled and minified JavaScript --> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script>Create a connection file.php to connect to the database.
try{
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "db_name";
$bdd = new PDO('mysql:host='.$servername.';dbname='.$dbname, $username, $password);
$bdd->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch (PDOException $e) {
die('Erreur : ' . $e->getMessage());
}Without the above steps, it will be completely impossible to proceed further. The jQuery library is essential to transmit data by interpreting JSON; getUri will allow you to play with your variables from the url; and finally the connection to your database which is obviously essential in order to connect your database to the client interface.
Display datas
When your environment is ready and all the prerequisites described above are met; you can then continue with the creation of a find-posts.js file that will allow you to retrieve and display your queries on the index.html.
Declare the script in the header of your index.html
<script type="text/javascript" src="find-posts.js"></script>Simply add a div with id in the body of your index.html.
<div id="posts-feed"></div>
Then, at the level of your find-posts.js file; here the Javascript (jQuery code among others) makes an Ajax request on the server. This request then retrieves the result returned by the find-post.php file (detailed soon) and returns the result instantly in the HTML element with id = posts-feed.
var url = '../server/find-posts.php'; $(document).ready(function(){
load_data();
function load_data(page)
{
$.ajax({
url: url,
method:'POST',
data:{page:page},
success:function(data){
$('#posts-feed').html(data);
}
})
}
});Your file The PHP does the search for posts on the database. The result is then retrieved and dynamically embedded in the HTML. Finally, the whole thing is echo-ed back to be captured by the find-posts.js file.
Next at the server level, you will need to create a find-post.php file that will be responsible for making the query to your database and returning the results already formatted in HTML. Here it will have been possible (certainly better) to return the data in JSON format. However here, given that this API can be used for web platforms or mobile applications, we will also define the whole HTML structure.
// Connexion to the database
require 'connexion.php';
// Select all the content from the table "post"
$q="SELECT * FROM post";
$reponse = $bdd->query($q);
// DISPLAY RESULT
while ($row = $reponse->fetch()) {
// HTML EXPORT
$output .= '
<article class="col-md-3 col-sm-6 col-xs-12" style="padding:0px 20px 20px 20px;">
<h1>
'.$row["title"].'
</h1>
<small>Post id: '.$row["id"].' | Date: '.$row["date"].'</small>
<p>
'.$row["description"].'
</p>
<a href="post-edit.html?id='.$row["id"].'" class="btn btn-primary"> Edit Post </a>
</article>';
}
// CLOSE HTML
$output .= '</div>';
// RETURN RESULTS
echo $output;Be aware that HTML and JS files are client-side interpreted files. PHP files are integrated into the server. It is therefore preferable to segment into 2 sub-folders /client and /server to have a correct file structure if you want to compile your code into a mobile application. The code that can be emulated is the one contained in the client folder only. PHP is unfortunately not an interpretable/compilable code.
Update the database
- In the client folder: A file update-post.html that will be the used for the update form.
- In the cliend side: A file find-post-by-id.js will retrieve the selected post and integrate the data to the form. And a file update-post-by-id.js that will send the information to your database once the form is validated.
- Finally on the server side :a file find-post-by-id.php and update-post-by-id.php will act as a bridge between the Javascript files and the database.
First things first, the informations to add in the HTML header:
<script src="jquery.js"></script>
<!-- Get Uri > For Embed App -->
<script type="text/javascript" src="geturi.js"></script>
<script type="text/javascript">
var post_id = decodeURI(getUrlVars()["id"]);
var title = "";
var description = "";
</script>
<script type="text/javascript" src="find-post-by-id.js"></script>Then in terms of HTML, it is very minimal. Your form (input fields in particular) will be integrated into your queries.
<section class="container">
<div class="row">
<h1>Posts Edit</h1>
</div>
<div class="row">
<div class="form-signin" id="post-edit"></div>
</div>
</section>Then on the find-post-by-id.js file.
var url = '../server/find-post-by-id.php?id='+post_id;
$(document).ready(function(){
load_data();
function load_data(page)
{
$.ajax({
url: url,
method:'POST',
data:{page:page},
success:function(data){
$('#post-edit').html(data);
}
})
}
});For the update-post-by-id.js file:
function updatePost(){
// Variables
title = document.getElementById("title").value;
description = document.getElementById("descr").value;
var url = "../server/update-post-by-id.php";
// alert("id:"+post_id+" | title:"+title+" | descr:"+description+" | url:"+url);
$.ajax({
type: "GET",
url: url,
dataType:'json',
data: {
'id': post_id,
'title': title,
'description': description,
},
crossDomain: true,
cache: false
});
setTimeout(function() {
document.location.href="index.html";
}, 50);
}On the server side for the find-post-by-id.php file:
// Connexion to the database
require 'connexion.php';
$Post_id = $_GET['id'];
// Select all the content from the table "post"
$q="SELECT * FROM post WHERE id='$Post_id' LIMIT 1";
$reponse = $bdd->query($q);
// DISPLAY RESULT
while ($row = $reponse->fetch()) {
// HTML EXPORT
$output .= '
<small>Post id :'.$row["id"].' | Date : '.$row["date"].' </small>
<p>
<input name="title" id="title" value="'.$row["title"].'" type="text" class="form-control ">
<input name="descr" id="descr" value="'.$row["description"].'" type="text" class="form-control">
</p>
<button onclick="updatePost();" class="btn btn-primary">
Update Post
</button>
<br>';
}
// RETURN RESULTS
echo $output;Finally the update-post-by-id.php file
require 'connexion.php';
// Grab the variables form the JSON
$Post_id= $_GET['id'];
$Post_title= $_GET['title'];
$Post_description= $_GET['description'];
// SQL request to the Database
$request = "UPDATE post SET title='$Post_title', description='$Post_description' WHERE id='$Post_id'";
$result = $bdd->query($request);Add and delete datas
Following the procedure previously made for the update, the implementation for adding or deleting datas is pretty much the same. By simply copy pasting the code above from the JS and PHP files, renaming them with different meaningful names to the actions you want, the only thing to modify are the SQL requests on the server side. The form for adding articles doesn’t even need to be generated in JS and for the deletion there is simply a button where you need to make sure that it’s pointing to the right article id.
Upon a decent amount of request if you are getting stuck on the way, I will update this article with the related code. However, if you understood and applied correctly the previous steps, the implementation of adding and deleting datas should be a piece of cake.
