, , , , , ,

Display Top Selling Items using PHP, MySQL, and Bootstrap 4

          No comments



Wondering if you can display all the top selling items from all of your sales with their total quantity and total amount.

Yes, in this article will show you a simple way for achieving that.

Prepare your tools for making this project. I am using the following below. (May differ from what you are using but you can use your alternatives as long as it does the same job)

  • XAMPP (Or any other Web Host with MySQL DB Local/Online).
  • Code/Text Editor (I am using Sublime 3)
  • Bootstrap and Font Awesome


Prepare your database ks_top_items and a table inside of it with the following fields.

CREATE TABLE `sales_items` (
`item_id` int(11) NOT NULL,
`product` varchar(255) NOT NULL,
`unit_price` decimal(19,2) NOT NULL,
`qty` int(11) NOT NULL,
`date_of_sale` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
view raw ks_top_items.sql hosted with ❤ by GitHub

Populate your database table so we can later fetch all the data and display in the main page.
Name your product and its unit price; declare the quantity that been sold or taken out, and the date of sale from the word itself. You can create this sample.



You can create rows as much as possible you can better compare the difference of each items from their quantities and total amount.



Create your project directory and start creating your configuration file, config.php.

<?php
/**
* Created by K-SOLUTIONS.
* User: kevin
* Date: 13/05/2018
* Time: 5:25 PM
*/
//DB Handler
$host = 'localhost';
$user = 'root';
$pass = '';
$database = 'ks_top_items';
$conn = mysqli_connect($host,$user,$pass,$database);
if (!$conn){
die("DB Handler Connection Failed: " . mysqli_connect_error());
}
view raw config.php hosted with ❤ by GitHub





Declare your $database with the database name you created earlier.
Your main page, index.php.
Do not forget to require your configuration file as follows.


<?php
//REQUIRE CONFIG FOR DB HANDLING
require 'config.php';
view raw index.php hosted with ❤ by GitHub


Write the html page. Include Bootstrap 4 required sources in head and before end of the html like from the sample. (I am using the downloaded sources of bootstrap).

view raw index-footer.php hosted with ❤ by GitHub
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link rel="icon" href="../assets/images/favicon.ico">
<title>Top Items | KSolutions PH</title>
<!-- Bootstrap CSS -->
<link rel="stylesheet" href="../assets/bootstrap/css/bootstrap.min.css">
<!-- Font Awesome CSS -->
<link rel="stylesheet" href="../assets/plugins/font-awesome/css/fontawesome-all.min.css">
<style>
html{
font-size: .875rem;
}
body{
margin-bottom: 3rem;
}
.main-footer {
border-top: 1px solid #b7b7b7;
position: fixed;
bottom: 0;
width: 100%;
line-height: 30px;
background-color: #f8f9fa!important;
}
</style>
</head>
view raw index-head.php hosted with ❤ by GitHub
Inside the html <body> we will be needing a form and a table elements. <form> for building the query of user and the <table> for showing the results.
Your form should have the date range and the count of rows to display. Look like the following.



Your <form> element contains this following code.

<form role="form" method="post" class="form-inline">
<label for="year">Select Date From: </label>&nbsp;&nbsp;
<input type="date" name="date_from" class="form-control">&nbsp;
<label for="year">To: </label>&nbsp;&nbsp;
<input type="date" name="date_to" class="form-control">&nbsp;
<label for="year">Top: </label>&nbsp;&nbsp;
<select name="top" class="form-control">
<option value="5">5</option>
<option value="10">10</option>
</select>&nbsp;
<button type="submit" name="submit" class="btn btn-primary"><i class="fas fa-search"></i></button>
</form>
view raw index.php hosted with ❤ by GitHub
The <table> element display the rank, product, unit price, total quantity, and total amount.

 Your <table> element contains this following code.

<table class="table table-bordered table-hover">
<thead>
<tr>
<th>RANK</th>
<th>PRODUCT</th>
<th>UNIT PRICE</th>
<th>QTY</th>
<th>TOTAL AMOUNT</th>
</tr>
</thead>
<tbody>
<?php
//FORM SUBMIT
if (isset($_POST['submit'])) {
$date_from = mysqli_escape_string($conn,$_POST['date_from']);
$date_to = mysqli_escape_string($conn,$_POST['date_to']);
$top = mysqli_escape_string($conn,$_POST['top']);
//FETCH
$sql = "SELECT
product,unit_price,SUM(qty) AS total_qty,(unit_price*SUM(qty)) AS total_amount
FROM `sales_items`
WHERE date_of_sale BETWEEN '{$date_from}' AND '{$date_to}'
GROUP BY product
ORDER BY total_amount DESC
LIMIT {$top}";
$result = mysqli_query($conn,$sql);
$rank = 1;
while ($row = mysqli_fetch_assoc($result)){
?>
<tr class="<?php echo ($rank==1)?'bg-primary text-white':'';?>">
<td><i class="fas fa-hashtag"></i> <strong><?php echo $rank;?></strong></td>
<td><?php echo $row['product'];?></td>
<td><?php echo $row['unit_price'];?></td>
<td><?php echo $row['total_qty'];?></td>
<td align="right" ><strong><?php echo $row['total_amount'];?></strong></td>
</tr>
<?php
$rank++;
}
}
?>
</tbody>
</table>
view raw index.php hosted with ❤ by GitHub
When user search with the date range and row limit, the final output of the project will be the following.



You can download the full source code below.

SOURCE CODE
1. Download the source code & place it on your host.
2. Create a database ‘ks_top_items’.
3. Import the sql file located at the source code's folder.
4. Test it on your host.

DOWNLOAD IT HERE -> DOWNLOAD

Enjoy !
Please share this if you find it helpful. Thank you.

Share:

Related Posts:

No comments:

Post a Comment

Popular Posts

Pages

Contact Form

Name

Email *

Message *