desainer website di surabaya

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:

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

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

Checking Password Strength with pStrength jQuery Plugin

When we make a web design, we often make registration form for the visitor. One of the field is password. One thing we need is password strength to ensure the better security for login access.. To fulfill that we can use pStrength jQuery Plugin.We j ...


CSS Trunk Text

In the web design development, we often show the last article in our site or our customer's site.By using CSS Bootstrap, it is easy because we can make the column easily. The HTML sample that we often use is like below: [adsense] <div class="co ...


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


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