how can I consolidate arrays from sql

Question

I have a query that gets results from my db.

Results set looks like this (when printed to browser):

Array ([experimentid] => 56  [optimizationtype] => mvt [primarykpilift] => 22.26 [variableid] => 36 [variabletype] => 9 [valueid] => 133 [20] => 22.24 [valuelift] => 22.24 [valuesignificant] => 1 ) 

Array ([experimentid] => 57 [optimizationtype] => mvt [primarykpilift] => 22.23 [variableid] => 37 [variabletype] => 7 [valueid] => 135 [valuelift] => 77.74 [valuesignificant] => 1)

Array ([experimentid] => 57 [optimizationtype] => mvt [primarykpilift] => 22.23 [variableid] => 38 [variabletype] => 15 [valueid] => 136 [valuelift] => 224.36 [valuesignificant] => 1) 

Array ([experimentid] => 57 [optimizationtype] => mvt [primarykpilift] => 22.23 [variableid] => 39 [variabletype] => 4 [valueid] => 138 [valuelift] => 77.78 [valuesignificant] => 1) 

Array ([experimentid] => 57 [optimizationtype] => mvt [primarykpilift] => 22.23 [variableid] => 39 [variabletype] => 4 [valueid] => 137 [valuelift] => 77.77 [valuesignificant] => 1)

As you will notice, some of results can look exactly the same, but with little difference. This is becasause the data is sitting in 3 tables joined by INNER JOIN statements.
What I am trying to do is consolidate these arrays into one array when applicable.

For example,

Notice there are 4 arrays here that have 57 as the experimentid? Those are all records I want to consolidate. A simple merge or intersect doesn't appear as though it will work.

Here's the logic for what I am looking for.

If experiment id's are the same between the current and previous id consolidate the array so the matching information is only displayed once and all of the unique information should be merged.

for example - If I we're to consolidate the 4 array's that share the same experimentid, they'd look something like this:

([experimentid] => 57 [optimizationtype] => mvt [primarykpilift] => 22.23 [variableid] => 37 [variabletype] => 7 [valueid] => 135 [valuelift] => 77.74 [valuesignificant] => 1 [variableid] => 38 [variabletype] => 15 [valueid] => 136 [valuelift] => 224.36 [valuesignificant] => 1 [variableid] => 39 [variabletype] => 4 [valueid] => 138 [valuelift] => 77.78 [valuesignificant] => 1 [valueid] => 137 [valuelift] => 77.77 [valuesignificant] => 1)

Notice how if the variableid matched, the redundant information was not added to the array? Also, notice how there is only one instance of the experimentid?

The reason I am doing this is because of how I want to display my results to the screen. If I just displayed the results as is, the end user would see 4 instances of the same experiment, just with subtle differences. I want them to get the idea that they are looking at 1 experiment but with multiple details (variableid and valueid related), which is why i want to consolidate my array's based on experiment (and at times variableid) id's.

So far, I've written some code to check to see if the array's match, but I am getting stuck on how to consolidate similar arrays...

$previousexpid = blah;
$previousvarid = blahs;
while ($row = mysqli_fetch_array($result))
{
print_r($row);

if ($row['experimentid'] === $previousexpid  && $row['variableid'] === $previousvarid) 
     {
    echo "experiment & variable";
     }
elseif ($row['experimentid'] === $previousexpid) 
     {
   echo "experiment only"; 
     }
else
     {
    echo "do not match";
     }
  echo "<br><br>"; 
$previousexpid = $row['experimentid'];
$previousvarid = $row['variableid']; 
}

update 10/21

I modified your code a bit to account for fields that I did not include previously to try and simplify things. Here is the updated code.

$rows[$row['experimentid']]['experimentid'] = $row['experimentid'];
$rows[$row['experimentid']]['optimizationtype'] = $row['optimizationtype'];
$rows[$row['experimentid']]['primarykpilift'] = $row['primarykpilift'];
$rows[$row['experimentid']]['variables'][$row['variableid']]['variableid'] =      $row['variableid'];
$rows[$row['experimentid']]['variables'][$row['variableid']]['variabletype'] =   $row['variabletype'];
$rows[$row['experimentid']]['variables'][$row['variableid']]['controlimage'] = $row['controlimage'];
$rows[$row['experimentid']]['variables'][$row['variableid']]['values'][$row['valueid']]['valueid'] = $row['valueid'];
$rows[$row['experimentid']]['variables'][$row['variableid']]['values'][$row['valueid']]['valueimage'] = $row['valueimage'];
$rows[$row['experimentid']]['variables'][$row['variableid']]['values'][$row['valueid']]['valuelift'] = $row['valuelift'];
$rows[$row['experimentid']]['variables'][$row['variableid']]['values'][$row['valueid']]['valuesignificant'] = $row['valuesignificant'];

One of the arrays it outputs is:

Array ( [59] => Array ( [experimentid] => 59 [optimizationtype] => mvt [primarykpilift] => 72.61 [variables] => Array ( [42] => Array ( [variableid] => 42 [variabletype] => Contact Module [controlimage] => -nsvsuc-control-contactmodule-headline.jpg [values] => Array ( [142] => Array ( [valueid] => 142 [valueimage] => -nsvsuc-contactspecialist-contactmodule-headline.jpg [valuelift] => 21.65 [valuesignificant] => 1 ) [141] => Array ( [valueid] => 141 [valueimage] => -nsvsuc-getstartedtoday-contactmodule-headline.jpg [valuelift] => 4.12 [valuesignificant] => ) ) ) [43] => Array ( [variableid] => 43 [variabletype] => Contact Module [controlimage] => -nsvsuc-control-contactmodule-cta.jpg [values] => Array ( [144] => Array ( [valueid] => 144 [valueimage] => -nsvsuc-haveuscall-contactmodule-cta.jpg [valuelift] => 24.74 [valuesignificant] => 1 ) [143] => Array ( [valueid] => 143 [valueimage] => -nsvsuc-requestconsultation-contactmodule-cta.jpg [valuelift] => 1.03 [valuesignificant] => ) ) ) [44] => Array ( [variableid] => 44 [variabletype] => Contact Module [controlimage] => -nsvsuc-control-contactmodule-presence.jpg [values] => Array ( [145] => Array ( [valueid] => 145 [valueimage] => -nsvsuc-footerl-contactmodule-presence.jpg [valuelift] => 37.08 [valuesignificant] => 1 ) ) ) ) )

This is essentially what I want it to look like:

<div>
<table>
<tr><td>MVT</td></tr>
<tr><td>72.61</td></tr>
<tr>
<table>
<tr>
<td>Contact Module</td><td>Contact Module</td><td>Contact Module</td>
</tr>
<tr>
<td>-nsvsuc-control-contactmodule-headline.jpg</td><td>-nsvsuc-control-contactmodule-cta.jpg</td><td>nsvsuc-control-contactmodule-presence.jpg</td>
</tr>
<tr>
<td>-nsvsuc-contactspecialist-contactmodule-headline.jpg</td><td>-nsvsuc-haveuscall-contactmodule-cta.jpg</td><td>-nsvsuc-footerl-contactmodule-presence.jpg</td>
</tr>
<tr>
<td>21.65</td><td>24.74</td><td>37.08</td>
</tr>
<tr>
<td>-nsvsuc-getstartedtoday-contactmodule-headline.jpg</td><td>-nsvsuc-requestconsultation-contactmodule-cta.jpg</td><td>null</td>
</tr>
<tr>
<td>4.12</td><td>1.03</td><td>null</td>
</tr>
</table>
</tr>
</table>
</div>

There can be multiple values per variable, so while in this example variables [42] and [43] have 2 values per variable, some variables could have n values, so somehow I will need to loop through things to repeat the output.

At the end of the day, variables are the row headings and values are placed underneath the associated values.

FYI, the output of print_r() is a LOT easier to read if you wrap it with <pre> tags like this. And it's more helpful for others to read as well. — hargobind
– hargobind, Commented Oct 26, 2013 at 7:30

hargobind · Accepted Answer · 2013-10-25 15:19:18Z

Given that the structure of your data is a relational hierarchy, I think you are just asking for headaches if you try to join all tables and then parse out the data. Instead, I would recommend creating nested loops that iterates over each table (referencing the value of the parent table) in order to output the data you want.

I'm sure there are considerably cleaner ways to write this code, but it gives you the gist of what you are trying to accomplish:

<?php
// Helper functions to output table HTML.
function p_table($rows) {
  global $tb, $indent;
  if (is_array($rows)) {
    $rows = implode("", $rows);
  }
  return "\n<table>\n" . $rows . "</table>\n";
}
function p_tr($cells) {
  if (is_array($cells)) {
    $cells = implode("", $cells);
  }
  return "<tr>\n" . $cells . "</tr>\n";
}
function p_td($cell) {
  $cell = $cell . "\n";
  return "<td valign=\"top\">\n" . $cell . "</td>\n";
}

// Start iterating over each table.
$exp_table = array();
$exp_results = mysql_query("SELECT * FROM experiments");
while ($exp_row = mysql_fetch_array($exp_results, MYSQL_ASSOC)) {

  // Output the experiment data rows.
  $exp_table[] = p_tr(p_td($exp_row['optimizationtype']));
  $exp_table[] = p_tr(p_td($exp_row['primarykpilift']));

  // Create a placeholder that the variables columns will fit into.
  $exp_var_columns = array();

  $var_results = mysql_query("SELECT * FROM `variables` WHERE experimentid = " . $exp_row['experimentid']);
  while ($var_row = mysql_fetch_array($var_results, MYSQL_ASSOC)) {

    // Create a new table to hold variables data and associated values data.
    $var_table = array();
    $var_table[] = p_tr(p_td($var_row['variabletype']));
    $var_table[] = p_tr(p_td($var_row['controlimage']));

      // Output the values as consecutive rows.
      $val_table = array();
      $val_results = mysql_query("SELECT * FROM `values` WHERE variableid = " . $var_row['variableid']);
      while ($val_row = mysql_fetch_array($val_results, MYSQL_ASSOC)) {

        // Output the valiable data.
        $val_table[] = p_tr(p_td('IM' . $val_row['valueimage']));
        $val_table[] = p_tr(p_td('VL' . $val_row['valuelift']));

      }
      $val_table = p_table($val_table);

    // Include the values table as a row beneath the variables data.
    $var_table[] = p_tr(p_td($val_table));

    $var_table = p_table($var_table);

    // Add the var table as a column
    $exp_var_columns[] = p_td($var_table);
  }

  // Output the var columns.
  $exp_table[] = p_tr($exp_var_columns);
}

$page_data[] = p_table($exp_table);

print implode("<hr>", $page_data);
?>

this is EXACTLY what I was looking for. I would have never figured this out on my own. Oh My...so overcome with joy right now. A million thanks!
for something like this $exp_table[] = p_tr(p_td($exp_row['optimizationtype'])); , how could I possibly add another <td> to a row (<tr>)if I wanted to? I dont think I want to add it to the function itself because then it will be applied each time p_td is called...

hargobind · Accepted Answer · 2013-10-22 00:56:57Z

0

If I understand the organization of your database table correctly, you have three "keys" that make the results of each row unique -- experimentid, variableid, and valueid. You need a way to nest the unique values. Here's some code that consolidates the different values into subarrays.

<?php
$rows = array();
while ($row = mysqli_fetch_array($result)) {
  $rows[$row['experimentid']]['experimentid'] = $row['experimentid'];
  $rows[$row['experimentid']]['optimizationtype'] = $row['optimizationtype'];
  $rows[$row['experimentid']]['primarykpilift'] = $row['primarykpilift'];
  $rows[$row['experimentid']]['variables'][$row['variableid']]['variableid'] = $row['variableid'];
  $rows[$row['experimentid']]['variables'][$row['variableid']]['variabletype'] = $row['variabletype'];
  $rows[$row['experimentid']]['variables'][$row['variableid']]['values'][$row['valueid']]['valueid'] = $row['valueid'];
  $rows[$row['experimentid']]['variables'][$row['variableid']]['values'][$row['valueid']]['valuelift'] = $row['valuelift'];
  $rows[$row['experimentid']]['variables'][$row['variableid']]['values'][$row['valueid']]['valuesignificant'] = $row['valuesignificant'];
}
?>

Although the code above is very ugly and could be rewritten in a much more readable format, it gives you an idea of how the data might be nested.

You can use print_r($rows); (or print '<pre>'.print_r($rows,true).'</pre>';) to see the nesting of the data.

edited Oct 22, 2013 at 0:56

answered Oct 20, 2013 at 14:02

hargobind

5922 silver badges20 bronze badges

5 Comments

user2828701 Over a year ago

this is good...this is getting close to what I'd like. Since these are all nested, I am getting a bit confused on how to format the output. Let's say for example, I want to out put the valuelift, how would I go about doing that? I am assuming I need a foreach loop...but something isn't working right. foreach ($rows as $tacos) { $tacos['valuelift']; }

hargobind Over a year ago

Please edit the original question and add some examples of the formatted output you are hoping to see.

user2828701 Over a year ago

just updated the original request as you requested. Any assistance from here would be MUCH APPRECIATED.

hargobind Over a year ago

I'm not 100% clear about your layout, especially with your last sentence "At the end of the day, variables are the row headings and values are placed underneath the associated values." By "row headings" do you mean "columns", and "underneath the associated values" are the "rows for each column"?

user2828701 Over a year ago

Notice you nested the information using 'variables' for the variable information and 'values' for the value information? To lay this out, there r 2 pieces of information per variable (variabletype & controlimage - note my array output in my update) and you have X possible variables. So the number of columns in the table will be dynamic based on the number of variables, and the first two rows of the table will hold variabletype and controlimage. As for the values, each value will have valueimage & valuelift. Rows are dynamic based total # of values, ie. 4values= 8rows. hope it help

Collectives™ on Stack Overflow

how can I consolidate arrays from sql

2 Answers 2

2 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related