In this tutorial, we will learn how to get sales to report month-wise and year-wise in PHP from the database.
In this example, we will guide you through a step-by-step procedure for sales records.
Sales report data in PHP and MySQL will look as follows:
Step1: First create a database with name “salesmydb” where order data is stored.
Step2: Second is to create a table with the name “tblproduct” and insert the products data.
| 12345678 | CREATE TABLE`tblproduct`( `ID`int(10)NOTNULL, `ProductName`varchar(250)DEFAULTNULL, `MRP`decimal(10,0)DEFAULTNULL, `SellingPrice`decimal(10,0)DEFAULTNULL, `CreationDate`timestamp NULLDEFAULTcurrent_timestamp())ENGINE=InnoDB DEFAULTCHARSET=latin1; |
Step3: Third step is to create a table with the name “tblorder” where order details data is stored.
| 123456789 | CREATE TABLE`tblorder`( `ID`int(5)NOTNULL, `CustomerName`varchar(250)DEFAULTNULL, `MobileNumber`bigint(20)DEFAULTNULL, `ProductID`int(5)DEFAULTNULL, `Quantity`int(10)DEFAULTNULL, `OrderDate`timestamp NULLDEFAULTcurrent_timestamp())ENGINE=InnoDB DEFAULTCHARSET=latin1; |
Step4: Create a database connection file and save this file with the name “config.php”. Below is the code of database connection.
| 123456789 | <?php$con=mysqli_connect(“localhost”,”root”,””,” salesmydb”);// Check connectionif(mysqli_connect_errno()){echo”Failed to connect to MySQL: “.mysqli_connect_error();}?> |
Step5: Create a form with three fields which is “From Date”, “To Date” and “Request Type”
| 123456789101112131415161718192021222324252627 | <form name=”bwdatesdata”action=””method=”post”action=””> <table width=”100%”height=”117″ border=”0″><tr> <th width=”27%”height=”63″scope=”row”>From Date:</th> <td width=”73%”><input type=”date”name=”fdate”class=”form-control”id=”fdate”> </td> </tr> <tr> <th width=”27%”height=”63″scope=”row”>ToDate:</th> <td width=”73%”> <input type=”date”name=”tdate”class=”form-control”id=”tdate”></td> </tr> <tr> <th width=”27%”height=”63″scope=”row”>Request Type:</th> <td width=”73%”> <input type=”radio”name=”requesttype”value=”mtwise”checked=”true”>Month wise <input type=”radio”name=”requesttype”value=”yrwise”>Year wise</td> </tr><tr> <th width=”27%”height=”63″scope=”row”></th> <td width=”73%”> <button class=”btn-primary btn”type=”submit”name=”submit”>Submit</button> </tr></table> </form> |
Step 6: PHP code for fetching data from the database on the basis of from date, to date and request type.
To Get Full Details : https://phpgurukul.com/how-to-get-sales-reports-from-the-database-using-php/
| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107 | <div class=”row”> <div class=”col-xs-12″> <?php if(isset($_POST[‘submit’])){$fdate=$_POST[‘fdate’];$tdate=$_POST[‘tdate’];$rtype=$_POST[‘requesttype’];?><?phpif($rtype==’mtwise’){$month1=strtotime($fdate);$month2=strtotime($tdate);$m1=date(“F”,$month1);$m2=date(“F”,$month2);$y1=date(“Y”,$month1);$y2=date(“Y”,$month2); ?><h4 class=”header-title m-t-0 m-b-30″>Sales Report Month Wise</h4><h4 align=”center”style=”color:blue”>SalesReport from<?phpecho$m1.”-“.$y1;?>to<?phpecho$m2.”-“.$y2;?></h4><hr><div class=”row”><table class=”table table-bordered”width=”100%” border=”0″style=”padding-left:40px”><thead><tr><th>S.NO</th><th>Month/Year</th><th>Sales</th></tr></thead><?php$ret=mysqli_query($con,”select month(OrderDate) as lmonth,year(OrderDate) as lyear, tblproduct.SellingPrice,tblorder.Quantity from tblorder join tblproduct on tblproduct.ID=tblorder.ProductID where date(tblorder.OrderDate) between ‘$fdate’ and ‘$tdate’ group by lmonth,lyear “);$num=mysqli_num_rows($ret);if($num>0){$cnt=1;while($row=mysqli_fetch_array($ret)){?><tbody><tr> <td><?phpecho$cnt;?></td> <td><?php echo$row[‘lmonth’].”/”.$row[‘lyear’];?></td> <td><?php echo$total=$row[‘SellingPrice’]*$row[‘Quantity’];?></td></tr><?php$ftotal+=$total;$cnt++;}?><tr> <td colspan=”2″align=”center”>Total</td> <td><?php echo$ftotal;?></td> </tr> </tbody></table><?php}}else{$year1=strtotime($fdate);$year2=strtotime($tdate);$y1=date(“Y”,$year1);$y2=date(“Y”,$year2);?><h4 class=”header-title m-t-0 m-b-30″>Sales Report Year Wise</h4><h4 align=”center”style=”color:blue”>SalesReport from<?phpecho$y1;?>to<?phpecho$y2;?></h4> <hr><div class=”row”><table class=”table table-bordered”width=”100%” border=”0″style=”padding-left:40px”><thead><tr><th>S.NO</th><th>Year</th><th>Sales</th></tr></thead><?php$ret=mysqli_query($con,”select month(OrderDate) as lmonth,year(OrderDate) as lyear, tblproduct.SellingPrice,tblorder.Quantity from tblorder join tblproduct on tblproduct.ID=tblorder.ProductID where date(tblorder.OrderDate) between ‘$fdate’ and ‘$tdate’ group by lyear “);$num=mysqli_num_rows($ret);if($num>0){$cnt=1;while($row=mysqli_fetch_array($ret)){?><tbody><tr><td><?phpecho$cnt;?></td><td><?php echo$row[‘lyear’];?></td><td><?php echo$total=$row[‘SellingPrice’]*$row[‘Quantity’];?></td></tr><?php$ftotal+=$total;$cnt++;}?><tr><td colspan=”2″align=”center”>Total</td><td><?php echo$ftotal;?></td></tr> </tbody></table> <?php}}}?> </div> </div> </div> </div> |
Here is the full code that we have written for this tutorial:
| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179 | <?phperror_reporting(0);require_once(“config.php”);?><!DOCTYPE html><html lang=”en”><head><meta http-equiv=”content-type”content=”text/html; charset=UTF-8″><meta charset=”utf-8″><title>PHP GURUKUL|DEMO</title><meta name=”generator”content=”Bootply”/><meta name=”viewport”content=”width=device-width, initial-scale=1, maximum-scale=1″><link href=”css/bootstrap.min.css”rel=”stylesheet”><!–[ifltIE9]><script src=”//html5shim.googlecode.com/svn/trunk/html5.js”></script><![endif]–><link href=”css/styles.css”rel=”stylesheet”></head><body><nav class=”navbar navbar-default navbar-fixed-top”role=”navigation”><div class=”navbar-header”><h4 style=”padding-left: 100px;padding-top: 20px;”>PHP GURUKUL|Programming Blog</h4></div></nav><div class=”container-fluid”><!–center–><div class=”col-sm-8″><div class=”row”><div class=”col-xs-12″><h3 style=”padding-left: 100px;”>How toget sales report from database between two dates inphp andMySQL</h3><hr><form name=”bwdatesdata”action=””method=”post”action=””><table width=”100%”height=”117″border=”0″><tr><th width=”27%”height=”63″scope=”row”>From Date:</th><td width=”73%”><input type=”date”name=”fdate”class=”form-control”id=”fdate”></td></tr><tr><th width=”27%”height=”63″scope=”row”>ToDate:</th><td width=”73%”><input type=”date”name=”tdate”class=”form-control”id=”tdate”></td></tr><tr><th width=”27%”height=”63″scope=”row”>Request Type:</th><td width=”73%”><input type=”radio”name=”requesttype”value=”mtwise”checked=”true”>Month wise<input type=”radio”name=”requesttype”value=”yrwise”>Year wise</td></tr><tr><th width=”27%”height=”63″scope=”row”></th><td width=”73%”><button class=”btn-primary btn”type=”submit”name=”submit”>Submit</button></tr></table></form></div></div><hr><div class=”row”><div class=”col-xs-12″><?phpif(isset($_POST[‘submit’])){$fdate=$_POST[‘fdate’];$tdate=$_POST[‘tdate’];$rtype=$_POST[‘requesttype’];?><?phpif($rtype==’mtwise’){$month1=strtotime($fdate);$month2=strtotime($tdate);$m1=date(“F”,$month1);$m2=date(“F”,$month2);$y1=date(“Y”,$month1);$y2=date(“Y”,$month2);?><h4 class=”header-title m-t-0 m-b-30″>Sales Report Month Wise</h4><h4 align=”center”style=”color:blue”>SalesReportfrom<?phpecho$m1.”-“.$y1;?>to<?phpecho$m2.”-“.$y2;?></h4><hr><div class=”row”><table class=”table table-bordered”width=”100%”border=”0″style=”padding-left:40px”><thead><tr><th>S.NO</th><th>Month/Year</th><th>Sales</th></tr></thead><?php$ret=mysqli_query($con,”select month(OrderDate) as lmonth,year(OrderDate) as lyear, tblproduct.SellingPrice,tblorder.Quantity from tblorder join tblproduct on tblproduct.ID=tblorder.ProductID where date(tblorder.OrderDate) between ‘$fdate’ and ‘$tdate’ group by lmonth,lyear “);$num=mysqli_num_rows($ret);if($num>0){$cnt=1;while($row=mysqli_fetch_array($ret)){?><tbody><tr><td><?phpecho$cnt;?></td><td><?phpecho$row[‘lmonth’].”/”.$row[‘lyear’];?></td><td><?phpecho$total=$row[‘SellingPrice’]*$row[‘Quantity’];?></td></tr><?php$ftotal+=$total;$cnt++;}?><tr><td colspan=”2″align=”center”>Total</td><td><?phpecho$ftotal;?></td></tr></tbody></table><?php}}else{$year1=strtotime($fdate);$year2=strtotime($tdate);$y1=date(“Y”,$year1);$y2=date(“Y”,$year2);?><h4 class=”header-title m-t-0 m-b-30″>Sales Report Year Wise</h4><h4 align=”center”style=”color:blue”>SalesReportfrom<?phpecho$y1;?>to<?phpecho$y2;?></h4><hr><div class=”row”><table class=”table table-bordered”width=”100%”border=”0″style=”padding-left:40px”><thead><tr><th>S.NO</th><th>Year</th><th>Sales</th></tr></thead><?php$ret=mysqli_query($con,”select month(OrderDate) as lmonth,year(OrderDate) as lyear, tblproduct.SellingPrice,tblorder.Quantity from tblorder join tblproduct on tblproduct.ID=tblorder.ProductID where date(tblorder.OrderDate) between ‘$fdate’ and ‘$tdate’ group by lyear “);$num=mysqli_num_rows($ret);if($num>0){$cnt=1;while($row=mysqli_fetch_array($ret)){?><tbody><tr><td><?phpecho$cnt;?></td><td><?phpecho$row[‘lyear’];?></td><td><?phpecho$total=$row[‘SellingPrice’]*$row[‘Quantity’];?></td></tr><?php$ftotal+=$total;$cnt++;}?><tr><td colspan=”2″align=”center”>Total</td><td><?phpecho$ftotal;?></td></tr></tbody></table><?php}}}?></div></div></div></div><!–/center–><hr></div><!–/container-fluid–><!–scriptreferences–><script src=”//ajax.googleapis.com/ajax/libs/jquery/2.0.2/jquery.min.js”></script><script src=”js/bootstrap.min.js”></script></body></html> |
Latest Blog : https://phpgurukul.com/garbage-management-system-using-php-and-mysql/
About Us
Welcome to PHPGurukul. We are a web development team striving our best to provide you with an unusual experience with PHP. Some technologies never fade, and PHP is one of them. From the time it has been introduced, the demand for PHP Projects and PHP developers is growing since 1994. We are here to make your PHP journey more exciting and useful.
Website : https://phpgurukul.com