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: Restricted input type for number only by using HTML5
<?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: Restricted input type for number only by using HTML5

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


Making an input type for checking valid email address in HTML

As a web design services, we often use input type="text" and input type="password" like below: For text: <input type="text" name="username" size="25" maxsize="15"> For password: <input type="password" name="password"> The syntax above ...


The usage of Bootstrap-DateTimePicker for Date Range

Hallo now, we as Web Design Surabaya wants to give the tutorial about the usage of bootstrap-datetimepicker for choosing check in and check out date. You do not only choose the date range but also the date and time range. To run the tutorial of date ...


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