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: All You Need to Know About JavaScript
<?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: All You Need to Know About JavaScript

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

Restricted input type for number only by using HTML5

How to make the input type to be valid for number only? We do not need to use javascript, because we can do that easily with HTML5. Let say we want to make an input type for temperature with the following rule: minimum 30o and maximum 50o, but allow ...


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


How to Learn PHP for Beginners? Here Are Some Useful Tips

PHP which is short for Hypertext Preprocessor is a programming language that has been used by so many websites all over the globe since its creation in 1994. Even after almost three decades, some of the best and biggest websites are still using it su ...


Working Process

Web Design - Wedding Photography - Wedding Videography

Our Location at Surabaya and Bali

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