How can I convert this M-N mysql array in this other array in PHP? (examples inside)

Question

Well, is pretty common to get an array like this in relations M-N in mysql:

[0]
'book_id' => 1
'title' => 'title'
'author_id' => 1
'author_name' => 'name1'

[1]
'book_id' => 1
'title' => 'title'
'author_id' => 2
'author_name' => 'name2'

So, there is an elegant/easy way to convert this kind or arrays in something like this?

'book_id' => 1
'title' => 'title'
'authors' => array( 0 => array( 'author_id' => 1, 'author_name' => 'name1' ),
1 => array( 'author_id' => 2, 'author_name' => 'name2' ) )

I don't find any script or combinations of functions that made this... and is pretty common, maybe I have not searched correctly.. don't know how to call the problem...

Any ideas or experiences?

Thanks :)

PS: I don't want to use GROUP BY + GROUP CONCACT in MySQL, I found that pretty ugly solution...

EDIT: I'm working in something generic, not only to solve this specific problem..

sure, I'm trying something generic, to use it in any place... but I don't achieve :S — raul
– raul, Commented Jan 2, 2012 at 22:36

Bill Karwin · Accepted Answer · 2012-01-02 22:39:13Z

2

I would do the JOIN in SQL and the post-process the results into a nested array in PHP code.

$bookkeys = array_flip(array("book_id", "title"));
$authorkeys = array_flip(array("author_id", "author_name"));

$stmt = $dbh->query("...");

$books = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  if (!array_key_exists($row["book_id"], $books)) {
    $books[ $row["book_id"] ] = array_intersect_key($row, $bookkeys);
  }
  $books[ $row["book_id"] ]["authors"][] = array_intersect_key($row, $authorkeys);
}

If you want the final array to be an ordinal array instead of keyed by book_id, you can convert it:

$books = array_values($books);

answered Jan 2, 2012 at 22:39

Bill Karwin

567k87 gold badges710 silver badges870 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

raul Over a year ago

Pretty interesting solution... I believe I can do something generic with array_flip and array_interect_key.. I'm gonna try it, I will post later if I was success.. thanks!

Bill Karwin Over a year ago

PHP has a lot of builtin functions that can do sophisticated work for you, and since they're written in C, they (usually) run faster than PHP code you may write to do the same work.

Cal · Accepted Answer · 2012-01-03 18:48:13Z

2

A generic solution for multiple sub-keys:

$rows = collapse_rows($rows, 'book_id', array(
        'author_id' => array('author_name'),
    ));

function collapse_rows($rows, $key, $subkeys){

    # make a map of all fields we don't perform a simple copy on
    $skip_fields = array();
    foreach ($subkeys as $k => $v){
        $skip_fields[$k] = 1;
        foreach ($v as $v2) $skip_fields[$v2] = 1;
    }

    # now build our output
    $out = array();
    foreach ($rows as $row){
        $row2 =& $out[$row[$key]];

        # simple fields first
        foreach ($row as $k => $v){
            if (!$skip_fields[$k])){
                $row2[$k] = $v;
            }
        }

        # now subkeys
        foreach ($subkeys as $k => $v){
            $sub_row = array($k => $row[$k]);
            foreach ($v as $v2) $sub_row[$v2] = $row[$v2];
            $row2[$k][$sub_row[$k]] = $sub_row;
        }
    }

    return $out;
}

This lets you pass the primary key and a hash of sub keys and fields to aggregate by.

answered Jan 3, 2012 at 18:48

Cal

7,13227 silver badges28 bronze badges

1 Comment

raul Over a year ago

Hi, yeah it works perfectly. So much foreach scare me a little.. But let me study your suggestion, is also pretty interesting. Thanks for your time :)

raul · Accepted Answer · 2012-01-03 10:42:48Z

Well, I have a "solution" based in Bill Karwin suggestion:

$key    = 'authors';
$output = array();
if ( is_array( $elements ) )
{
    $all_keys           = array_keys( $elements[0] );
    $conflicted_keys    = call_user_func_array( 'array_diff_assoc', $elements );
    $conflicted_keys    = array_keys( $conflicted_keys );
    $good_keys          = array_diff( $all_keys, $conflicted_keys );

    $conflicted_keys_fliped     = array_flip( $conflicted_keys );
    $good_keys_fliped           = array_flip( $good_keys );

    foreach ( $elements as $row )
    {
        if ( !array_key_exists( $row[$good_keys[0]], $output ) ) {
            $output[ $row[$good_keys[0]] ]          = array_intersect_key($row, $good_keys_fliped);
        }
        $output[ $row[$good_keys[0]] ][ $key ][]    = array_intersect_key( $row, $conflicted_keys_fliped );
    }
}

$output     = array_values($output);
var_dump($output);

Don't know if is the most eficient/correct.. On other hand, this will not work if there are more than one type of conflict... patches will be good received :)

thanks to all!

Cal · Accepted Answer · 2012-01-02 22:32:36Z

0

Something simple like this would work:

$out = array();
foreach ($rows as $row){
    $out[$row['book_id']]['book_id'] = $row['book_id'];
    $out[$row['book_id']]['title'] = $row['title'];
    $out[$row['book_id']]['authors'][$row['author_id']] = array(
        'author_id' => $row['author_id'],
        'author_name'   => $row['author_name'],
    );
}

Rows are keyed by book ID and then author ID so we only have one output row per book and then per author inside that row.

answered Jan 2, 2012 at 22:32

Cal

7,13227 silver badges28 bronze badges

1 Comment

raul Over a year ago

Thanks for your suggestion, but I'm working in something generic, to use it in any place..

raul · Accepted Answer · 2012-01-04 15:16:45Z

Well, this is the funcion that finally I will use, it is based completly in the idea of Cal but trying to use native functions as said Bill Karvin:

protected function collapse_rows( $rows, $key, $subkeys ) {
if ( is_array( $rows ) )
{
    // make a map of all fields we don't perform a simple copy on
    $skip_fields = array();
    foreach( $subkeys as $sub ) {
        $skip_fields = array_merge( $skip_fields, array_reverse( $sub ) );
    }        
    $skip_fields = array_flip( $skip_fields );

    // now build our output
    $out = array();
    foreach ( $rows as $row ) {

        // simple fields first
        if ( !array_key_exists( $row[$key], $out ) ) {
            $out[ $row[$key] ]  = array_diff_key( $row, $skip_fields );
        }

        // now subkeys
        foreach ( $subkeys as $k => $v ) {
            $value      = array_intersect_key( $row, array_flip( $subkeys[$k] ) );
            if ( !empty( $value ) ) {
                $out[ $row[$key] ][ $k ][]  = $value;   
            }
        } 
    }

    return array_values( $out );
}
return $rows;

}

Once done, I don't think it has a too much good performance... I wonder how other people solve this kind of problems...

Whatever, thanks to both!

Collectives™ on Stack Overflow

How can I convert this M-N mysql array in this other array in PHP? (examples inside)

5 Answers 5

2 Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related