Databases
StackOverflow2013
Postsdbo
POhow can I consolidate arrays from sql
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POhow can I consolidate arrays from sql
    primarykey
    data
    text
    <p>I have a query that gets results from my db.</p> <p>Results set looks like this (when printed to browser):</p> <pre><code>Array ([experimentid] =&gt; 56 [optimizationtype] =&gt; mvt [primarykpilift] =&gt; 22.26 [variableid] =&gt; 36 [variabletype] =&gt; 9 [valueid] =&gt; 133 [20] =&gt; 22.24 [valuelift] =&gt; 22.24 [valuesignificant] =&gt; 1 ) Array ([experimentid] =&gt; 57 [optimizationtype] =&gt; mvt [primarykpilift] =&gt; 22.23 [variableid] =&gt; 37 [variabletype] =&gt; 7 [valueid] =&gt; 135 [valuelift] =&gt; 77.74 [valuesignificant] =&gt; 1) Array ([experimentid] =&gt; 57 [optimizationtype] =&gt; mvt [primarykpilift] =&gt; 22.23 [variableid] =&gt; 38 [variabletype] =&gt; 15 [valueid] =&gt; 136 [valuelift] =&gt; 224.36 [valuesignificant] =&gt; 1) Array ([experimentid] =&gt; 57 [optimizationtype] =&gt; mvt [primarykpilift] =&gt; 22.23 [variableid] =&gt; 39 [variabletype] =&gt; 4 [valueid] =&gt; 138 [valuelift] =&gt; 77.78 [valuesignificant] =&gt; 1) Array ([experimentid] =&gt; 57 [optimizationtype] =&gt; mvt [primarykpilift] =&gt; 22.23 [variableid] =&gt; 39 [variabletype] =&gt; 4 [valueid] =&gt; 137 [valuelift] =&gt; 77.77 [valuesignificant] =&gt; 1) </code></pre> <p>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.<br> What I am trying to do is consolidate these arrays into one array when applicable.</p> <p>For example,</p> <p>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.</p> <p>Here's the logic for what I am looking for.</p> <p>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.</p> <p>for example - If I we're to consolidate the 4 array's that share the same experimentid, they'd look something like this:</p> <pre><code>([experimentid] =&gt; 57 [optimizationtype] =&gt; mvt [primarykpilift] =&gt; 22.23 [variableid] =&gt; 37 [variabletype] =&gt; 7 [valueid] =&gt; 135 [valuelift] =&gt; 77.74 [valuesignificant] =&gt; 1 [variableid] =&gt; 38 [variabletype] =&gt; 15 [valueid] =&gt; 136 [valuelift] =&gt; 224.36 [valuesignificant] =&gt; 1 [variableid] =&gt; 39 [variabletype] =&gt; 4 [valueid] =&gt; 138 [valuelift] =&gt; 77.78 [valuesignificant] =&gt; 1 [valueid] =&gt; 137 [valuelift] =&gt; 77.77 [valuesignificant] =&gt; 1) </code></pre> <p>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?</p> <p>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.</p> <p>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...</p> <pre><code>$previousexpid = blah; $previousvarid = blahs; while ($row = mysqli_fetch_array($result)) { print_r($row); if ($row['experimentid'] === $previousexpid &amp;&amp; $row['variableid'] === $previousvarid) { echo "experiment &amp; variable"; } elseif ($row['experimentid'] === $previousexpid) { echo "experiment only"; } else { echo "do not match"; } echo "&lt;br&gt;&lt;br&gt;"; $previousexpid = $row['experimentid']; $previousvarid = $row['variableid']; } </code></pre> <p><br><br><br><br><br><br></p> <p><strong>update 10/21</strong></p> <p>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.</p> <pre><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']; </code></pre> <p>One of the arrays it outputs is:</p> <pre><code>Array ( [59] =&gt; Array ( [experimentid] =&gt; 59 [optimizationtype] =&gt; mvt [primarykpilift] =&gt; 72.61 [variables] =&gt; Array ( [42] =&gt; Array ( [variableid] =&gt; 42 [variabletype] =&gt; Contact Module [controlimage] =&gt; -nsvsuc-control-contactmodule-headline.jpg [values] =&gt; Array ( [142] =&gt; Array ( [valueid] =&gt; 142 [valueimage] =&gt; -nsvsuc-contactspecialist-contactmodule-headline.jpg [valuelift] =&gt; 21.65 [valuesignificant] =&gt; 1 ) [141] =&gt; Array ( [valueid] =&gt; 141 [valueimage] =&gt; -nsvsuc-getstartedtoday-contactmodule-headline.jpg [valuelift] =&gt; 4.12 [valuesignificant] =&gt; ) ) ) [43] =&gt; Array ( [variableid] =&gt; 43 [variabletype] =&gt; Contact Module [controlimage] =&gt; -nsvsuc-control-contactmodule-cta.jpg [values] =&gt; Array ( [144] =&gt; Array ( [valueid] =&gt; 144 [valueimage] =&gt; -nsvsuc-haveuscall-contactmodule-cta.jpg [valuelift] =&gt; 24.74 [valuesignificant] =&gt; 1 ) [143] =&gt; Array ( [valueid] =&gt; 143 [valueimage] =&gt; -nsvsuc-requestconsultation-contactmodule-cta.jpg [valuelift] =&gt; 1.03 [valuesignificant] =&gt; ) ) ) [44] =&gt; Array ( [variableid] =&gt; 44 [variabletype] =&gt; Contact Module [controlimage] =&gt; -nsvsuc-control-contactmodule-presence.jpg [values] =&gt; Array ( [145] =&gt; Array ( [valueid] =&gt; 145 [valueimage] =&gt; -nsvsuc-footerl-contactmodule-presence.jpg [valuelift] =&gt; 37.08 [valuesignificant] =&gt; 1 ) ) ) ) ) </code></pre> <p>This is essentially what I want it to look like:</p> <pre><code>&lt;div&gt; &lt;table&gt; &lt;tr&gt;&lt;td&gt;MVT&lt;/td&gt;&lt;/tr&gt; &lt;tr&gt;&lt;td&gt;72.61&lt;/td&gt;&lt;/tr&gt; &lt;tr&gt; &lt;table&gt; &lt;tr&gt; &lt;td&gt;Contact Module&lt;/td&gt;&lt;td&gt;Contact Module&lt;/td&gt;&lt;td&gt;Contact Module&lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td&gt;-nsvsuc-control-contactmodule-headline.jpg&lt;/td&gt;&lt;td&gt;-nsvsuc-control-contactmodule-cta.jpg&lt;/td&gt;&lt;td&gt;nsvsuc-control-contactmodule-presence.jpg&lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td&gt;-nsvsuc-contactspecialist-contactmodule-headline.jpg&lt;/td&gt;&lt;td&gt;-nsvsuc-haveuscall-contactmodule-cta.jpg&lt;/td&gt;&lt;td&gt;-nsvsuc-footerl-contactmodule-presence.jpg&lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td&gt;21.65&lt;/td&gt;&lt;td&gt;24.74&lt;/td&gt;&lt;td&gt;37.08&lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td&gt;-nsvsuc-getstartedtoday-contactmodule-headline.jpg&lt;/td&gt;&lt;td&gt;-nsvsuc-requestconsultation-contactmodule-cta.jpg&lt;/td&gt;&lt;td&gt;null&lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td&gt;4.12&lt;/td&gt;&lt;td&gt;1.03&lt;/td&gt;&lt;td&gt;null&lt;/td&gt; &lt;/tr&gt; &lt;/table&gt; &lt;/tr&gt; &lt;/table&gt; &lt;/div&gt; </code></pre> <p>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.</p> <p>At the end of the day, variables are the row headings and values are placed underneath the associated values.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser2828701
    1. USuser2828701
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COFYI, the output of `print_r()` is a LOT easier to read if you wrap it with `<pre>` tags **[like this](http://mikebranski.com/blog/utilizing-phps-print_r-function/)**. And it's more helpful for others to read as well.
      singulars
      1. POhow can I consolidate arrays from sql
      1. UShargobind
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload