create nested array with periods as first node and data in next nodes

[phn221181]

hi, i need help cause i can't go on. since 3 days and nights i try to create a nested array. i have saved some accounting entries in my database and i want to display some values with php.

i made a function that calls for different periods the values.

in this function i want to create a nested array with the structure:

Array("month" => Array ("sales" => 1000), Array ("costs" => Array ("rent" => 500, "advertising" => 100, "connection" => 30)))

this is my code:

<?php

/*
type  costelement amount 
costs NK          100.00
costs Werbung     349.99
sales NULL        725.56

==> RESULT SQL QUERY 
==> costelement is the key for assoc array 
==> foreach row the information should be saved in an array
*/

$month = 6;
$year = 2018;
$yearbefore = $year - 1;
$periods = array('month', 'monthlastyear', 'year', 'lastyear');

function get_data ($get_month, $get_year, $periods) {
	$result = array();
	for ($i = 0; $i < count($periods); $i++) {
		switch ($periods[$i]) {
			case 'month':
			$query = mysql_query("SELECT type, costelement, SUM(amount) AS amount FROM ind_finance WHERE month = '$get_month' AND year = '$get_year' GROUP BY type, costelement");
			break;
			case 'monthlastyear':
			$query = mysql_query("SELECT type, costelement, SUM(amount) AS amount FROM ind_finance WHERE month = '$get_month' AND year = '$yearbefore' GROUP BY type, costelement");
			break;
			case 'year';
			$query = mysql_query("SELECT type, costelement, SUM(amount) AS amount FROM ind_finance WHERE month <= '$get_month' AND year = '$get_year' GROUP BY type, costelement");
			break;
			case 'lastyear':
			$query = mysql_query("SELECT type, costelement, SUM(amount) AS amount FROM ind_finance WHERE month <= '$get_month' AND year = '$yearbefore' GROUP BY type, costelement");
			break;
		}
		echo $periods[$i]."<br>";
		
		while ($row = mysql_fetch_array($query, MYSQL_ASSOC)) {
			$type = $row['type'];
			if ($type == 'sales') {
				$result[] = array($periods[$i] => array( $type =>  $row['amount']));
			} else if ($type == 'costs') {		
				$element = $row['costelement'];
				$result[] = array($periods[$i] => array( $type =>  array($element => $row['amount'])));
			}			
		}
	}
	return $result;	
}

$result = get_data($month,$year,$periods); 
$obj = json_encode($result);
echo $obj."<br>";			

?>

this code returns the following json object:

[{"month":{"costs":{"Miete":"500.00"}}},{"month":{"costs":{"NK":"100.00"}}},{"month":{"costs":{"Werbung":"349.99"}}},{"month":{"sales":"725.56"}},{"year":{"costs":{"Miete":"1000.00"}}},{"year":{"costs":{"NK":"100.00"}}},{"year":{"costs":{"Werbung":"349.99"}}},{"year":{"sales":"5325.56"}}]

"monthlastyear" and "lastyear" are not in the array, i don't know why?

i need an object that looks like (array follows later on):

{"month":{"sales":1000,"costs":{"NK":200,"Miete":500}},"month year before":{"sales":2000,"costs":{"NK":250,"Miete":500,"Werbung":200}},"year":{"sales":5000,"costs":{"NK":1000,"Miete":2500,"Werbung":300,"sonst.":400}}}

the period is on the first layer, the second layer should always be sales & value, third layer costs, 4th layer costelements.

in array_structure it should be like this:

$result = array("month" => array( "sales" => 1000,
								  "costs" => array("NK" => 200,
												 "Miete" => 500
											)
								  ),
				"month year before" => array("sales" => 2000,	
										 "costs" => array("NK" => 250,
										                  "Miete" => 500,
														  "Werbung" => 200
														  )
										),
				"year" => array("sales" => 5000,
                                          "costs" => array ("NK" => 1000,
				                                            "Miete" => 2500,
															"Werbung" => 300,
															"sonst." =>400
															)
									     )
				);

i hope someone can help me to clean the mistake in my while loop.

a) fill in the data from month last year

b) get the right structure of the array

thanks a lot 🙂

Edited June 17, 2018 by phn221181

[justsomeguy]

"monthlastyear" and "lastyear" are not in the array, i don't know why?

I'm going to guess it's because those queries didn't return any data, and you don't do anything if that happens.

It sounds like you want to create an array for each period, so you should create that regardless of whether or not the query returned data.  You should initialize each period in the array with empty costs and sales values.  Then, when you're looping through the results from the query, you add things to the costs array for that period, and total up the sales value.

Also, you should really convert your code to use prepared statements for SQL.  Your code isn't going to run at all on the most recent version of PHP because you're using a database library that was replaced in 2004.

[phn221181]

Thanks someguy, that's right, montyearbefore and yearbefore didn't return any date cause i didn't transfer them into the function. $yearbefore was just empty. Now i calculate $yearbefore in the function.

Also i converted the code for prepared statsments for SQL. It works fine 🙂

function get_data ($get_month, $get_year, $periods, $connection) {
	$yearbefore = $get_year - 1;
	$result = array();
	for ($i = 0; $i < count($periods); $i++) {
		switch ($periods[$i]) {
			case 'month':
			$sql = "SELECT type, costelement, SUM(amount) AS amount FROM ind_finance WHERE month = '$get_month' AND year = '$get_year' GROUP BY type, costelement";
			break;
			case 'monthlastyear':
			$sql = "SELECT type, costelement, SUM(amount) AS amount FROM ind_finance WHERE month = '$get_month' AND year = '$yearbefore' GROUP BY type, costelement";
			break;
			case 'year';
			$sql = "SELECT type, costelement, SUM(amount) AS amount FROM ind_finance WHERE month <= '$get_month' AND year = '$get_year' GROUP BY type, costelement";
			break;
			case 'lastyear':
			$sql = "SELECT type, costelement, SUM(amount) AS amount FROM ind_finance WHERE month <= '$get_month' AND year = '$yearbefore' GROUP BY type, costelement";
			break;
		}
		// echo $sql."<br>";
		$request = $connection->query($sql);
		while ($row = $request->fetch_assoc()) {
			$type = $row['type'];
			if ($type == 'sales') {
				$result[] = array($periods[$i] => array( $type =>  $row['amount']));
			} else if ($type == 'costs') {		
				$element = $row['costelement'];
				$result[] = array($periods[$i] => array( $type =>  array($element => $row['amount'])));
			}
		}
		
	}
	return $result;
	$connection->close();	
}

At least i want to keep the periods dynamically. so i do not want to create 4 arrays for periods and 2 for sales and costs. maybe some time i want to add quarter years, or half years i have to add more arrays. i wish one array for the periods could be enough. it would be more nice to have all the data in on array.

Can someone help me to create it?

Edited June 18, 2018 by phn221181

[justsomeguy]

Those aren't prepared statements, when you use prepared statements you do not put PHP variables in the middle of a SQL query.  You prepare it with placeholders, then send the data separately.

http://php.net/manual/en/pdo.prepare.php

You need to initialize the array so that later you can add values to it:

for ($i = 0; $i < count($periods); $i++) {
  $result[$periods[$i]] = ['sales' => 0, 'costs' => []];

 

Then when you get database results you add to sales, and add items to costs.

[phn221181]

thanks so much justsomeguy...

with your hint in the brackets [$result[$periods[$i]] i solved the problem 🙂

now the while loop looks like:

while ($row = $request->fetch_assoc()) {
	$type = $row['type'];
	if ($type == 'sales') {
		$result[$periods[$i]][$type] = $row['amount'];
	} else if ($type == 'costs') {		
	$element = $row['costelement'];
	$result[$periods[$i]][$type][$element] = $row['amount'];
}

and it works great. the array looks like i wished to get it 🙂

Quote

Array ( [month] => Array ( [costs] => Array ( [Miete] => 500.00 [NK] => 100.00 [Werbung] => 349.99 ) [sales] => 725.56 ) [monthlastyear] => Array ( [costs] => Array ( [Ankauf] => 10400.00 [NK] => 750.00 ) ) [year] => Array ( [costs] => Array ( [Bewirtung] => 34.00 [Miete] => 1000.00 [NK] => 100.00 [Werbung] => 369.99 ) [sales] => 5325.56 ) [lastyear] => Array ( [costs] => Array ( [Ankauf] => 10400.00 [Miete] => 500.00 [NK] => 750.00 ) ) )

with: 

echo $result['month']['sales'] or     
echo $result['monthlastyear']['costs']['NK']

i exactly can point the values. GREAT!!!

 

the other hint to change the querys to prepared statements i didn't got to work. i tried to set "?" instead of the variables and i started $sql->bind_param("ss",$get_month,$get_year); $sql->execute(); but i only receive errors. i hope there will be no SQL injection 😕