Merge sub array with same keys values

vincekaribusana · September 1, 2019, 7:57am

I’ve got an array with some sub arrays, what I’m trying to achieve is merging subarrays of they have the same value for a specific key

This is my array:

Array
(
    [0] => Array
        (
            [Building ID] => 57
            [Tabella palazzina uno] => 4.470
        )

    [1] => Array
        (
            [Building ID] => 56
            [Tabella palazzina uno] => 45.000
        )

    [2] => Array
        (
            [Building ID] => 74
            [Tabella gruppo A] => 79.000
        )

    [3] => Array
        (
            [Building ID] => 75
            [Tabella gruppo A] => 26.000
        )
    [4] => Array
        (
            [Building ID] => 74
            [Tabella gruppo B] => 201.000
        )
)

And this is how I would like it look like:

Array
(
    [0] => Array
        (
            [Building ID] => 57
            [Tabella palazzina uno] => 4.470
        )

    [1] => Array
        (
            [Building ID] => 56
            [Tabella palazzina uno] => 45.000
        )

    [2] => Array
        (
            [Building ID] => 74
            [Tabella gruppo A] => 79.000
            [Tabella gruppo B] => 201.000
        )

    [3] => Array
        (
            [Building ID] => 75
            [Tabella gruppo A] => 26.000
        )

)

As you can see for building ID 74 I need to merge the two sub arrays into one sub array.

This is my SQL query:

$sql = "SELECT km_kondomanager_millesimal_table_value_building_id,
                               km_kondomanager_millesimal_table_value_table_id,
                               km_kondomanager_millesimal_table_millesimal_value,
                               km_kondomanager_millesimal_table_name

                               FROM km_kondomanager_millesimal_table_values

                               LEFT JOIN  km_kondomanager_millesimal_table
                               ON km_kondomanager_millesimal_table_values . km_kondomanager_millesimal_table_value_table_id = km_kondomanager_millesimal_table . km_kondomanager_millesimal_table_id";

                $query= mysqli_prepare($db_user_conn, $sql);
                // mysqli_stmt_bind_param($query, 'i', $km_group_id);
                mysqli_stmt_execute($query);
                mysqli_stmt_bind_result($query, $km_building_id, $km_table_id, $millesimal_value, $millesimal_table_name);
                mysqli_stmt_store_result($query);

                while(mysqli_stmt_fetch($query)) { 


                    $responses[] = array(

                        'Building ID' => $km_building_id,
                        $millesimal_table_name => $millesimal_value

                      );

                }

vincekaribusana · September 1, 2019, 9:05am

This is what I’ve tried so far:

$output = array();

foreach ($responses as $values) {
    $key = $values['Building ID'];
    $output[$key][] = $values;

}

// Don't want the referenceUid in the keys? Reset them:
$output = array_values($output);

And it returns the following array:

Array
(
    [0] => Array
        (
            [0] => Array
                (
                    [Building ID] => 74
                    [Tabella gruppo A] => 79.000
                )

            [1] => Array
                (
                    [Building ID] => 74
                    [Tabella gruppo B] => 201.000
                )

        )
)

vincekaribusana · September 1, 2019, 3:24pm

Hi @benanamen how do I do it? Sorry never came across this. Do I need to group the building IDs first and then execute a foreach loop and a new mysql query?

benanamen · September 1, 2019, 4:54pm

PM me an SQL Dump of your DB with at least a few sample records. Also, a description of the real problem you are trying to solve will be helpful. What you have provided is your attempted solution to the real problem. What I am asking for is a high level overview of what you are doing.

vincekaribusana · September 1, 2019, 5:27pm

Hi @benanamen thanks for your help, I’ll send you the database dump soon. In the meantime I’ve found a possible solution with this code:

    $arrDest = array();
    $cnt = count( $responses );
    for( $i = 0; $i < $cnt; $i++ ) {

        $curArr = $responses[ $i ];
        $idValue = "";
        $sumKey = "";
        $sumValue = "";
        foreach( $curArr as $key => $value ){
            
            if( $key == "Building ID" ){
                $idValue = $value;
            }if($key == "Proprietario immobile"){
                $prova = $value;
            }else{
                $sumKey = $key;
                $sumValue = $value;
            }
        }

        $arrDest[ $idValue ][ 'Building ID' ] = $idValue;
        $arrDest[ $idValue ][ 'Proprietario immobile' ] = $prova;

        if( isset( $arrDest[ $idValue ] ) == false ){
            $arrDest[ $idValue ] = array();
        }
        if( isset( $arrDest[ $idValue ][ $sumKey ] ) == false )
        {
            $arrDest[ $idValue ][ $sumKey ] = 0.0;
        }

        $arrDest[ $idValue ][ $sumKey ] += $sumValue;
    }

I get the following table elaborating the array with twig which is what i want to achieve

igor_g · September 1, 2019, 8:30pm

https://www.php.net/manual/de/function.array-replace-recursive.php

SamuelCalifornia · September 2, 2019, 5:34pm

If you are reading the data from the database then creating the array from that data and you are now asking how to further process the data then it will be better to do it all in SQL if possible and I think that is what benanamen is asking. I know that for about two decades I have seen developers attempting to solve a fundamental problem (real problem as benanamen says) and then they ask for help with the solution they have thought of. Often there are better solutions to the fundamental problem.

But to answer the question as asked, I think it is simply a matter of creating an array with the building id as the key and another array as the value.

vincekaribusana · September 2, 2019, 7:29pm

Hi @SamuelCalifornia thanks for your replay, I really appreciate the support and advices I get in this forum. I agree with you that sometimes there are several approaches to solve the problem and some of them are better then others. I read several articles and posts online and I understand that is better to prefer mysql over php. My problem here is that I can create the html table with the datas I have from the query but I need to group them before they are rendered into the table. That is why I decided to edit the array. I tried to group them using mysql but either my database structure is not correct or is me that still haven’t understood how to use a query with group by.

SamuelCalifornia · September 2, 2019, 7:48pm

Yes, sometimes it is difficult to think of how to do things in SQL. But that is where the forums can help.

Note that I added to my previous reply after I posted it, I hope you can at least use the array with the building id as the key.

benanamen · September 3, 2019, 12:11am

@SamuelCalifornia, you understand correctly where I am coming from.

OP, the first place to start is making sure your DB design is correct and then going from there. So again, I would ask that you provide an SQL dump of your DB.

benanamen · September 3, 2019, 10:15pm

After speaking with OP on Skype I was able to get to the “Real Problem”.

What the OP needed was an SQL Pivot Table detailing shared expense portions for individual apartments/flats in a particular building.

Example Output

+-----------+-------------+----------+
| owner     | electricity | cleaning |
+-----------+-------------+----------+
| Owner One | 45.000      | 4.470    |
| Owner Two | 79.000      | 0.000    |
+-----------+-------------+----------+

vincekaribusana · September 7, 2019, 11:13pm

Hi @benanamen thanks to your help I’ve managed to do some progress, I wasn’t aware of pivot table in mysql so I’ve done some researches and also discovered mysql stored procedures, this is my final code:

DELIMITER ;;
DROP PROCEDURE IF EXISTS pippo;;
CREATE PROCEDURE pippo()
BEGIN
	SELECT
    
    GROUP_CONCAT(
	CONCAT("MAX(IF(km_kondomanager_millesimal_table_value_table_id='", km_kondomanager_millesimal_table_value_table_id, "',km_kondomanager_millesimal_table_millesimal_value ,NULL)) AS '", km_kondomanager_millesimal_table_name, "'"), "\n"
	  )INTO @answers
	FROM (
	  SELECT DISTINCT km_kondomanager_millesimal_table_value_table_id, km_kondomanager_millesimal_table_name FROM km_kondomanager_millesimal_table_values INNER JOIN km_kondomanager_millesimal_table
                 ON km_kondomanager_millesimal_table_values . km_kondomanager_millesimal_table_value_table_id = km_kondomanager_millesimal_table. km_kondomanager_millesimal_table_id WHERE km_kondomanager_millesimal_table_value_group_id = 13
	) A;

	SET @query := 
	  CONCAT(
	    'SELECT km_kondomanager_millesimal_table_value_building_id, ', @answers, 
	    ' FROM km_kondomanager_millesimal_table_values WHERE km_kondomanager_millesimal_table_value_group_id = 13 GROUP BY km_kondomanager_millesimal_table_value_building_id'
	  );

	PREPARE statement FROM @query;
	EXECUTE statement;
END;;
DELIMITER ;

Now normally when I call a query in PHP I use prepared statement, the parameters in the queue are specificed by “?” and I pass them with the function bind paramenters. Is it possible to do the same with a stored procedure?

system · December 8, 2019, 6:13am

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

Topic		Replies	Views
Merge arrays PHP	4	959	October 23, 2021
How can i merge multi arrays without using foreach PHP	57	3299	June 25, 2023
Need to merge arrays PHP	4	503	October 8, 2014
PHP Loop If same key then merge value PHP	5	9712	January 3, 2019
Merge array question PHP	2	753	December 14, 2011

Merge sub array with same keys values

Related topics