Getting results from database and count results

PHP
1

Hi,

I have a table with column names " time_close, orderprofit ".

What i need to do is to display a min and max order profit by month and display it in 2 values a, b.

Example :

January, max, min
February, max, min
September, max, min

I got this but it shows me only a last “orderprofit” value

$data1 = array();

$query1 = mysqli_query($conn, "SELECT DISTINCT MONTH(time_close), orderprofit FROM trade_log WHERE status = 'Close' AND uid = '23' GROUP BY MONTH(time_close) ORDER BY time_close ASC");
$bb = mysqli_fetch_all($query1, MYSQLI_ASSOC);

$a = 0;
$b = 0;

foreach ($bb as $key) {

	if ($key['orderprofit'] > 0) {
		$a = $key['orderprofit'];
	}
	if ($key['orderprofit'] < 0) {
		$b = $key['orderprofit'];
	}

	$data1[] = array(
		'y' => $key['MONTH(time_close)'],
		'a' => $a,
		'b' => $b
	);
}

//print_r($bb);
$json_data1 = json_encode($data1); // output data for chart

Here is an image that shows data in db https://image.prntscr.com/image/L-SaDIt_R2OUq1udPqhQyQ.png

PS.

This data is i need for Morris charts like this https://image.prntscr.com/image/ql52jZ5HQvquSmpKOsFwyQ.png
where y = month, a = value + , b = value -

EDIT:

I found solution for graph since it need to show min and max data for each month

query :

SELECT DISTINCT MONTH(time_close), SUM(orderprofit) as total, MIN(orderprofit) as min, MAX(orderprofit) as max FROM trade_log WHERE status = 'Close' AND uid = '23' GROUP BY MONTH(time_close) ORDER BY time_close ASC"

usage:

	$data1[] = array(
		'y' => $key['MONTH(time_close)'],
		'a' => $key['max'],
		'b' => $key['min']
	);
2

How about posting an sql dump of your DB with a handful of records. Mysql has a really hard time importing data from a picture. The whole thing can be done in SQL without all the code gymnastics.

3

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.