Create an application with Bootstrap on the front end, PHP on the back-end.
Build the UI
I have here used a free Bootstrap 4 admin template called Star Admin. It is available to download on GitHub. The template has a well-written code and all the right components for me to work with. The template has a neat, elegant and simple design as well. I intend to customize the template a little bit to make it my own unique web application.
The application is a simple one, for customer management. It contains a dashboard where I can see the customer stats and another page where I can maintain the data about the customers and add new customers as well.
The dashboard displays the user and business stats, in cards and charts. These data are taken from the customer_details table and business_stats table.
The ‘Customer Details’ page presents the data taken from the database in tables. There are two tables in the page. One table shows the details of the customer and the other shows the details about the business statistics.
The table Customer Details has customer information like the customers’ country, their email ID, a unique customer ID, and the customers’ name. The ‘Add Customer’ button is to add a new customer. The details of the customer are entered into the customer_details table in the database.
The table Monthly Statistics shows the number of visitors, the number of sales, the number of pending orders, the revenue for every month. The details are taken from the database table business stats. You can check out How to Use Bootstrap with PHP
To create a local server I use WampServer. Create a new PHP project ‘customer-management’. Add the necessary Bootstrap files into the project folder. From the template, remove the part of the code that you do not need, and keep the ones you need. It is easy to locate and modify the components from a well-commented template.
Validate the Forms
We have a form that inputs the data of a customer and store into the database table. The form looks like this.
In the back-end PHP, let’s begin with validating the forms. Each input is identified by a name attribute in the backend. The validation includes whether an input field is empty or not, the customer id field should be unique for each customer, the email ID should be of the proper format etc.
The validation for the form when no input is given looks something like this.
The PHP code for validation is as follows:
<?php// include(“db_connect.php”);
$errEmail = $errID = $errName= $errCountry =””;$email = $name = $customerId = $country = “”;$success =””;
if(isset($_POST[“submit”])) {$email = $_POST[‘email’];$name = $_POST[‘user’];$customerId = $_POST[‘customerId’];$country = $_POST[‘country’];$valid=true;// Check if name has been enteredif(empty($_POST[‘user’])){$errName= ‘Please enter your user name’;$valid=false;}// Check if email has been entered and is validif(empty($_POST[‘email’])){$errEmail = ‘Please enter a valid email address’;$valid=false;}// check if a valid password has been enteredif(empty($_POST[‘customerId’])) {$errID = ‘Please enter a unique customer ID’;$valid=false;}// check id a country has been selectedif($_POST[‘country’]==’ — Select — ‘){$errCountry = ‘Please select a country’;$valid = false;}if($valid){$success = “The form has been submitted”;}// create a database connection$servername = “localhost”;$username = “root”;$password = “”;$dbname = “customer_management”;
// Create connection$conn = mysqli_connect($servername, $username, $password, $dbname);// Check connectionif (!$conn) {die(“Connection failed: “ . mysqli_connect_error());}
if($valid){$sql = “INSERT INTO customer_details (customer_name, customer_email, customer_id, customer_country) VALUES (‘$name’, ‘$email’, ‘$customerId’, ‘$country’)”;
if (mysqli_query($conn, $sql)) {echo “New record created successfully”;} else {echo “Error: “ . $sql . “<br>” . mysqli_error($conn);}
}
}
?>
Create the Database
To store these values a database has to be created. From the localhost open phpmyadmin.
Create a database with name customer_management. There are two tables in the database, one named customer_details and the other named business_stats.
The table customer_details contains the details of the customers. When a new customer is added, the details about the customer is added into the table customer_details. There are 5 fields in the table. The first one is the UID, which is auto-generated and is the primary key. The others are — customer_name, customer_email, customer_id, and customer_country. The names of the fields are self-explanatory.
The next table is the business_stats table in which all the data related to sales, revenue etc. are stored. The following is the table business_stats in the database.
Creating a table is not enough. Now we need to connect the database to the front end and write SQL queries so that the data entered are stored into the table and can be retrieved when required. Add the following code to do that.
<?php// create a database connection$servername = “localhost”;$username = “root”;$password = “”;$dbname = “customer_management”;
// Create connection$conn = mysqli_connect($servername, $username, $password, $dbname);// Check connectionif (!$conn) {die(“Connection failed: “ . mysqli_connect_error());}?>
Visualize the Data
Also, we need to populate the HTML tables from the corresponding tables in the database. Connect the tables page to the database. There are two tables to be populated. For the ‘Customer Details’ table, the data shown is the data entered. For the table ‘Business Statistics’ the data shown is the data taken directly from the database.
The following code can be used to populate the HTML tables.
The code for populating the customer details table.
<! — statistics table →<div class=”col-lg-10 offset-lg-2 content-wrapper”><h3 class=”text-primary”>Customer Details</h3><div class=”row”><div class=”col-lg-12"><div class=”card”><div class=”card-block”><! — <h5 class=”card-title”>Hoverable Table</h5> →<table class=”table table-hover”><thead><tr class=”text-primary”><th>#</th><th>Name</th><th>Email ID</th><th>Customer ID</th><th>Country</th></tr></thead><?php$sql = “SELECT * from customer_details”;
if (mysqli_query($conn, $sql)) {echo “”;} else {echo “Error: “ . $sql . “<br>” . mysqli_error($conn);}$count=1;$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {// output data of each rowwhile($row = mysqli_fetch_assoc($result)) { ?><tbody><tr><th scope=”row”><?php echo $count; ?></th><td><?php echo $row[‘customer_name’]; ?></td><td><?php echo $row[‘customer_email’]; ?></td><td><?php echo $row[‘customer_id’]; ?></td><td><?php echo $row[‘customer_country’]; ?></td></tr></tbody><?php$count++;}} else {echo “0 results”;}?></table></div></div></div></div>
We can populate the data table for business statistics in a similar manner.
<! — customer details table →<h3 class=”text-primary mt-5">Monthly statistics</h3><div class=”row mb-2"><div class=”col-lg-12"><div class=”card”><div class=”card-block”><! — <h5 class=”card-title”>Hoverable Table</h5> →<table class=”table table-hover”><thead><tr class=”text-primary”><th>#</th><th>Month</th><th>Visitors</th><th>Sales</th><th>Orders</th><th>Revenue</th></tr></thead><?php$sql = “SELECT * from business_stats”;
if (mysqli_query($conn, $sql)) {echo “”;} else {echo “Error: “ . $sql . “<br>” . mysqli_error($conn);}$count=1;$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {// output data of each rowwhile($row = mysqli_fetch_assoc($result)) { ?><tbody><tr><th scope=”row”><?php echo $count; ?></th><td><?php echo $row[‘month’]; ?></td><td><?php echo $row[‘visitors’]; ?></td><td><?php echo $row[‘sales’]; ?></td><td><?php echo $row[‘pending_orders’]; ?></td><td><?php echo $row[‘revenue’]; ?></td></tr></tbody><?php$count++;}} else {echo “0 results”;}mysqli_close($conn);?></table></div></div></div></div>
Now, If a new customer is added, the data will be added to the database and also reflected in the user interface also.
This is the table that we have prior to adding a new customer.
Let’s add a customer in the database named Lucy through the Add Customer form.
Upon clicking the Add button, this data will be saved to the database in the corresponding table, customer_details.
Now, this data will be reflected in the tables in the application as well.
Next, what we need to do is use the data in the database and visualize them in the dashboard. The data are used in the line chart, the cards, and the doughnut chart.
The first in the dashboard are the cards. There are four cards, each displaying values for Monthly visitors, Registered Users, Open Orders, and Revenue. These are the data taken from the database. To display these data a simple SELECT query is required.
<?php $sql = “SELECT visitors from business_stats WHERE month=’Jul’”;
if (mysqli_query($conn, $sql)) {echo “”;} else {echo “Error: “ . $sql . “<br>” . mysqli_error($conn);}
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {// output data of each rowwhile($row = mysqli_fetch_assoc($result)) { ?><div class=”row”><div class=”col-xl-3 col-lg-3 col-md-3 col-sm-6 mb-4"><div class=”card card-success”><div class=”card-block text-white”><h4 class=”card-title font-weight-normal”><?php echo $row[‘visitors’]; ?></h4><p class=”card-text”>Monthly Visitors</p><div class=”progress”><div class=”progress-bar progress-bar-striped bg-warning” role=”progressbar” style=”width: 75%” aria-valuenow=”75" aria-valuemin=”0" aria-valuemax=”100">75%</div></div></div></div></div><?php}} else {echo “0 results”;}?>
The query for the rest of the cards is the same except for the number of Registered Users. It is taken from the table customer_details.
$sql1 = “SELECT * FROM customer_details”;$result = mysqli_query($conn, $sql1);
if ($result) {// var_dump($result);} else {echo “Error: “ . $sql . “<br>” . mysqli_error($conn);}
$count=0;if (mysqli_num_rows($result) > 0) {// output data of each rowwhile($row = mysqli_fetch_assoc($result)) {$count++;} ?>
<div class=”col-xl-3 col-lg-3 col-md-3 col-sm-6 mb-4"><div class=”card card-danger”><div class=”card-block text-white”><h4 class=”card-title font-weight-normal”><?php echo $count; ?></h4><p class=”card-text”>Registered Users</p><div class=”progress”><div class=”progress-bar progress-bar-striped bg-success” role=”progressbar” style=”width: 40%” aria-valuenow=”40" aria-valuemin=”0" aria-valuemax=”100">40%</div></div></div></div></div><?php
} else {echo “0 results”;}?>
Next, we need to take the values from the database and then show it in the line chart and the doughnut chart.
Let’s take a look at the SQL query and the PHP code to populate the data into the line chart.
<! — line graph →<?php$sql = “SELECT sales from business_stats”;
if (mysqli_query($conn, $sql)) {echo “”;} else {echo “Error: “ . $sql . “<br>” . mysqli_error($conn);}
$result = mysqli_query($conn, $sql);$number=array();if (mysqli_num_rows($result) > 0) {// output data of each rowwhile($row = mysqli_fetch_assoc($result)) {$number[]=$row[‘sales’];}} else {echo “0 results”;}
$number_formated= “[“.implode(“,”,$number).”]”;
?><script type=”text/javascript”>window.dataf= <?php echo $number_formated; ?></script>
<div class=”row”><div class=”col-lg-6 mb-4"><div class=”card”><div class=”card-block”><h5 class=”card-title”>Sales Report</h5><canvas id=”lineChart” style=”height:250px”></canvas></div></div></div><! — end line chart →
The variable dataf is used to pass the result of the query in an array format accepted by the data variable in JavaScript file for the chart using Chart.js
Next up is the donut chart. Here we need two data from the table customer_details, all the countries and the number of people from each of the country. These two data are stored in two different variables and then passed to the Javascript file. The code looks something like this.
<! — doughnut chart →
<?php$sql= “select * from customer_details order by customer_country”;$result= mysqli_query($conn, $sql);
$country = array();$previous = “”;$count=0;while($raw = mysqli_fetch_array($result)){$current = $raw[‘customer_country’];if($current != $previous ){$previous = $raw[‘customer_country’];$count=0;}$count++;$country[$raw[‘customer_country’]]=$count;}$count_entry = array() ;$country_name = array() ;foreach ($country as $key => $value) {$country_name[] = $key;$count_entry[] = $value;}
$count_entry= “[“.implode(“,”,$count_entry).”]”;//$country_name= “[“.implode(“,”,$country_name).”]”;$country_name = json_encode($country_name);//var_dump($count_entry);//var_dump($country_name); ?>
<script type=”text/javascript”>window.datag= <?php echo $country_name; ?>;window.datah= <?php echo $count_entry; ?>;</script>
<div class=”col-lg-6 mb-4"><div class=”card”><div class=”card-block”><h5 class=”card-title”>Customer per Country</h5><canvas id=”doughnutChart” style=”height:250px”></canvas></div></div></div></div><! — end doughnut chart →
So that’s all we need to create a small backend application using Bootstrap 4 and PHP.
Conclusion
Now we have an application with Bootstrap and PHP that you can use to add customers into a database. All the data are maintained in the tables in the Customer Details page, and the specific important data are shown in cards and charts.
This application can also be done using an API where you don’t have to mix HTML and PHP code. The data will be added or retrieved from the database using the API.