PHP MySQL one-to-many database return removing duplicate entries when outputting to a HTML table

Question

I'm returning an array much like the simplified example below:

  0 => 
    object(stdClass)[9]
      public 'crew_chief' => string 'Alan' (length=4)
      public 'location' => string '1a' (length=2)
  1 => 
    object(stdClass)[22]
      public 'crew_chief' => string 'Alan' (length=4)
      public 'location' => string '2a' (length=2)

I would like my loop over my array and remove certain duplicate info (in this case "Alan")

Desired output would look like the below in a HTML table row <tr>:

Alan
1a
2a

I have tried:

foreach($records as $r) {
  <tr>
    <td>
      $r->crew_chief;
    </td>

    <td>
      $r->location . <br />;
    </td>
  </tr>
}

why not just do it in your query instead? any particular reason you want to use PHP for this? — Funk Forty Niner, Jan 13 '16 at 17:27
@Fred If you have enough server memory, manipulating data with PHP is faster than using SQL directly. I've found that running one broad query and then sorting through the results with PHP is _much_ faster than running multiple more precise queries. It's not a small difference either. I've seen 1-hour runs turned into 10-second runs. — Typel, Jan 13 '16 at 17:46
Can you point me to some good documentation that would explain how a would write a sql query that would accomplish this. I'm not sure I completely understand how this works when I'm using multiple tables and removing duplicates "Alan" (one to many relationships) — user1040259, Jan 13 '16 at 17:50
@user1040259 See the second part of Xorifelse's answer below, it has the basic concept. Loop through your array, using the value 'crew_chief' as the array's index. this way, duplicates are naturally eliminated as the duplicate entries simply overwrite each other. — Typel, Jan 13 '16 at 17:54
@Typeless Sometimes this is the case yes, but overall its better to run fewer querys then more distinct ones. And then there is also the issue of the branch prediction: http://stackoverflow.com/questions/11227809/why-is-processing-a-sorted-array-faster-than-an-unsorted-array — Xorifelse, Jan 13 '16 at 18:36

Xorifelse · Accepted Answer · 2016-01-13T19:08:48.487

3

The best way to do this is from the MySQL query directly by using the statement SELECT DISTINCT col FROM table WHERE data='data' GROUP BY row

However if you want to use your code you would be able to do this with storing each name in a separate array array['Alan'] = 'data' and reloop over this array to output the data, cause the index Alan already exists and gets overwritten.

edited Jan 13 '16 at 19:08

answered Jan 13 '16 at 17:36

Xorifelse

7,878
1
27
38

Typel · Answer 2 · 2016-01-13T18:09:07.913

Whenever you'd like to eliminate duplicate entries in an array, you can simply loop through the array, placing each element into a new array which uses the value of the duplicated index as the new array's index. This way, duplicates are naturally eliminated as they just overwrite each other.

Take this, for example. This takes into account that you have an array of objects, but the concept is the same for an array of arrays as well.

$condensed = array();
foreach( $records as $index=>$r ) {
  $condensed[$r->crew_chief] = $r;
  // optionally, you can save the original index, so that you can rebuild 
  // the original array with the same original index structure again
  $condensed[$r->crew_chief]['original_index'] = $index;
  }

// that eliminated all the duplicates, but if you want your array to 
// have the same structure (with same indexes) as the original, let's 
// use our saved indexes above to rebuild things as they were
$deduped = array();
foreach( $condensed as $c ) {
  $deduped[$c['original_index']] = $c;
  unset($deduped[$c['original_index']]['original_index']); // be clean!
  }

// don't forget memory management
unset($condensed);

PHP MySQL one-to-many database return removing duplicate entries when outputting to a HTML table

2 Answers2