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: The usage of Bootstrap-DateTimePicker for Date Range
<?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: The usage of Bootstrap-DateTimePicker for Date Range

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


Looking for birthday with PHP and MySQL

We often experience difficulty to find the birthday between two dates inside MySQL database. It is caused by the storage we use in MySQL, namely brith date format like Y-m-d, for example 2000-12-21. The problem occurs when we try to find the birthda ...


How to make website loads faster

As the Best Webdesign, we often face website loads slowly, because it has to wait for long time to download whole contents such as CSS, Javascript, and images. This content must be downloaded first before the browser shows the content. Now, we will ...


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