How to get Sales reports from the database using PHP

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.

CREATE TABLE `tblproduct` (

`ID` int(10) NOT NULL,

`ProductName` varchar(250) DEFAULT NULL,

`MRP` decimal(10,0) DEFAULT NULL,

`SellingPrice` decimal(10,0) DEFAULT NULL,

`CreationDate` timestamp NULL DEFAULT current_timestamp()

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Step3: Third step is to create a table with the name “tblorder” where order details data is stored.

CREATE TABLE `tblorder` (

`ID` int(5) NOT NULL,

`CustomerName` varchar(250) DEFAULT NULL,

`MobileNumber` bigint(20) DEFAULT NULL,

`ProductID` int(5) DEFAULT NULL,

`Quantity` int(10) DEFAULT NULL,

`OrderDate` timestamp NULL DEFAULT current_timestamp()

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Step4: Create a database connection file and save this file with the name “config.php”. Below is the code of database connection.

<?php

$con = mysqli_connect("localhost","root",""," salesmydb");

// Check connection

if (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”

<tr>

</td>

</tr>

<tr>

</tr>

<tr>

<th width="27%" height="63" scope="row">Request Type :</th>

<input type="radio" name="requesttype" value="mtwise" checked="true">Month wise

</tr>

<tr>

<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/

100

101

102

103

104

105

106

107

<?php

if(isset($_POST['submit']))

{

$fdate=$_POST['fdate'];

$tdate=$_POST['tdate'];

$rtype=$_POST['requesttype'];

<?php if($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">Sales Report from <?php echo $m1."-".$y1;?> to <?php echo $m2."-".$y2;?></h4>

<hr >

<thead>

<tr>

<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>

</tr>

<?php

$ftotal+=$total;

$cnt++;

}?>

<tr>

<td colspan="2" align="center">Total </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">Sales Report from <?php echo $y1;?> to <?php echo $y2;?></h4>

<hr >

<thead>

<tr>

<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>

</tr>

<?php

$ftotal+=$total;

$cnt++;

}?>

<tr>

<td colspan="2" align="center">Total </td>

</tr>

</tbody>

</table> <?php } } }?>

</div>

Here is the full code that we have written for this tutorial:

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

<?php

error_reporting(0);

require_once("config.php");

<!DOCTYPE html>

<head>

<title>PHP GURUKUL | DEMO</title>

<!--[if lt IE 9]>

<![endif]-->

</head>

<body>

<h4 style="padding-left: 100px;padding-top: 20px;">PHP GURUKUL | Programming Blog</h4>

</div>

</nav>

<h3 style="padding-left: 100px;">How to get sales report from database between two dates in php and MySQL</h3>

<hr >

<tr>

</td>

</tr>

<tr>

</tr>

<tr>

<th width="27%" height="63" scope="row">Request Type :</th>

<input type="radio" name="requesttype" value="mtwise" checked="true">Month wise

</tr>

<tr>

<button class="btn-primary btn" type="submit" name="submit">Submit</button>

</tr>

</table>

</form>

</div>

<hr>

<?php

if(isset($_POST['submit']))

{

$fdate=$_POST['fdate'];

$tdate=$_POST['tdate'];

$rtype=$_POST['requesttype'];

<?php if($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">Sales Reportfrom <?php echo $m1."-".$y1;?> to <?php echo $m2."-".$y2;?></h4>

<hr >

<thead>

<tr>

<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>

</tr>

<?php

$ftotal+=$total;

$cnt++;

}?>

<tr>

<td colspan="2" align="center">Total </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">Sales Reportfrom <?php echo $y1;?> to <?php echo $y2;?></h4>

<hr >

<thead>

<tr>

<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>

</tr>

<?php

$ftotal+=$total;

$cnt++;

}?>

<tr>

<td colspan="2" align="center">Total </td>

</tr>

</tbody>

</table><?php } } }?>

</div>

</div>

<hr>

</div>

</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

Search This Blog

PHPGurukul10

How to get Sales reports from the database using PHP | PHP Gurukul

Comments

Post a Comment

Popular posts from this blog

Global Food Ordering System Using PHP and MySQL | PHP Gurukul

Pre-School Enrollment System using PHP and MySQL | PHP Gurukul

Doctor Appointment Management System Using PHP and MySQL | PHP Gurukul