Getting current total and previous one from MySQL

best webdesign - mysql total - mysql query

working as best webdesign we give tutorial to get total amount now and previous date with mysql query



Getting current total and previous one from MySQL

As the part of the Best Webdesign, we ever get the request from our customer to show the total selling in a month including previous selling to count the percentage of profit..

To do that, we often use a very simple way, namely using two queris of MySQL. It works well as we need, but it is not too effective.
The way we often use is like below:

Read: Here's Why You Should Learn PHP
<?php
$result=doQuery("select t1.total,t1.tanggal from total_realisasi t1
where LEFT(t1.tanggal,7)=? order by t1.tanggal DESC", Array('2019-07'));

foreach(doArray($result) as $row)
	{
		$result2=doQuery("SELECT t2.total FROM total_realisasi t2 WHERE t2.tanggal < ? order by t2.tanggal DESC limit 1", Array($row['tanggal']));
	}
?>

doQuery and doArray is sql query according to mysql class we us.

Actually, the same result can be get just with one MySQL query.
To do so, we can run query as follows:

<?php
$result=doQuery("select 
t1.total,t1.tanggal,
IFNULL((SELECT t2.total FROM total_realisasi t2 WHERE t2.tanggal < t1.tanggal order by t2.tanggal DESC limit 1), 0) as prevjml
from total_realisasi t1
where LEFT(t1.tanggal,7)=? order by t1.tanggal DESC", Array('2019-07'));
?>

IFNULL((SELECT t2.total FROM total_realisasi t2 WHERE t2.tanggal < t1.tanggal order by t2.tanggal DESC limit 1), 0) as prevjml means that if there is no previous date found, the result will be 0.
LEFT(t1.tanggal,7) is MySQL function to get 7characters from the left.

From the query above, we will get the result as follows:

total  |  tanggal  |  prevjml
6000000   |  2019-07-30   |  84000000
84000000   |  2019-07-29   |  39000000
39000000   |  2019-07-27   |  50000000
50000000   |  2019-07-26   |  5000000
5000000   |  2019-07-18   |  6000000
6000000   |  2019-07-16   |  10000000

Read: Here's Why You Should Learn PHP

We can see that the value of the column of prevjml is the same as the value of the column tanggal at previous date.
For example, the value of the column of prevjml at 2019-07-30 is the value of the column total at  2019-07-29. By using the result above, we can calculate the percentage easily with PHP.

Good luck.



Related Article

Web Design Services - Best Webdesign - Best SEO Services

7 Advantages of Using Web Programming With PHP

PHP is known as a popular web programming language in the world. It is used by millions of website developers and provides many advantages that are the main options for web developers in Indonesia. A Glimpse of PHP PHP is an open source programming ...


4 Ways to Optimize MySQL Database

Database optimization is a way to improve performance. You will make an effort to improve the data structure, index, and query. In an optimization process, speed from a transaction becomes a repair focus. Thus, an optimization process is crucial beca ...


What Is MySQL: The Definition and Functions

MySQL is an open-source system of database management using basic commands or a programming language with structured query language. It is fairly popular in the technology world. MySQL is useful as a database. It has been used by more than 60 million ...


We create RESPONSIVE WEBSITE

Responsive web is special program code, so the website can be read in all devices. It is very suitable for better page rank in search engine like google

graphic design in indonesia

Website Design in Indonesia

Graphic Design in Indonesia

Contact

+628113638531 / +628113865464

info@tatamedia.com

Webdesigner in Indonesia

Videographer in Bali

Office

Tatamedia Solusindo Surabaya

Dekkophotography Bali

Fine Art Photography in Bali

Web Design

Support

Send your request to

info@tatamedia.com