Very Complex SQL Data Result Merge via PHP (Multi Dimentional Arrays)

Question

Sorry, I have a hard time to write proper thread title for this problem. Here's my question:

In short: How to merge array item and calculate.

Here's the full explanation

1) I have a (WP custom) database to track statistics: visits, unique visits, etc, and it's stored per post per date.

To make it easier to understand. Here's the screenshot of the table:

2) This is the example data when I queried it:

https://gist.github.com/turtlepod/8e7dc93bae7f0b665fd5aea8a9694998

So in this example we have multiple post ID: "90", "121", & "231"

We have multiple date in db: "2017-03-20", "2017-03-21", "2017-03-22"

We have multiple stats: "visits", and "unique_visits"

We also have a "stat_value" for each item.

Each item have unique ID.

All data is dynamically created when an event happen. so not all post_id have 2 stats or the above date.

Note: keep in mind that in real code, we have a lot more data and variations than the example above.

3) I need to merge the data:

The post_id "121" is the same as post "231", so we need to merge and add the "stat_value" into one data and remove "231" entry.

What is the best way to do this (dynamically) via PHP ?

I have this data:

$raw_data = array( ... ); // the one in github gist
$post_groups = array(
   '121' => array( '121', '231' ), // main post_id => array of alias.
);

It need to return the same data format as $raw_data, but remove the data of "231" and include/sum the "stat_value" of "231" to "121".

Thank you.

Besides changing the post_id, there is no change that you want in the example data. Right? — Parantap Parashar
– Parantap Parashar, Commented Mar 23, 2017 at 5:44
I also need to calculate/sum/add the alias post_id(s) to post_id (basically merging the stat value) if it's the same stat_id and date — David Chandra Purnama
– David Chandra Purnama, Commented Mar 23, 2017 at 12:48

OriginalEXE · Accepted Answer · 2017-03-23 15:37:20Z

Try it with this:

function david_transform_data($data, $groups) {
  if (empty($groups) === true) {
    return $data;
  }

  // Transform groups into a more useful format
  $transformed_groups = array();
  foreach ($groups as $post_id => $aliases) {
    foreach ($aliases as $alias) {
      if (absint($post_id) === absint($alias)) {
        continue;
      }

      $transformed_groups[absint($alias)] = $post_id;
    }
  }

  // Replace aliases with the real post id
  foreach ($data as $index => $stat) {
    if (isset($transformed_groups[absint($stat->post_id)]) === false) {
      continue;
    }

    $data[$index]->post_id = $transformed_groups[absint($stat->post_id)];
  }

  // Go through stats and merge those with the same post_id, stat_id
  // and stat_date
  $merged_stats = array();
  $index_tracker = 0;
  $stats_hash = array();

  foreach ($data as $index => $stat) {
    $hash_key = sprintf(
      '%s-%s-%s',
      $stat->post_id,
      $stat->stat_id,
      $stat->stat_date
    );
    if (isset($stats_hash[$hash_key]) === true) {
      $merged_stats[$stats_hash[$hash_key]]->stat_value += absint($stat->stat_value);
      continue;
    }

    $merged_stats[] = $stat;
    $stats_hash[$hash_key] = $index_tracker;
    $index_tracker++;
  }

  return $merged_stats;
}

var_dump(david_transform_data($raw_data, $post_groups));

There might be a faster solution but this is the first thing that came to my mind.

Collectives™ on Stack Overflow

Very Complex SQL Data Result Merge via PHP (Multi Dimentional Arrays)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related