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.