Store all data in an array to remove duplicate record when using LEFT JOIN in MySql

Sometime you’d stuck like that you’ll need all other rows coming with left join as a duplicate row for all record, where you would need to have all these inside an array variable. For example if you have an tbl_emp and another tbl_contact, in tbl_contact you would have one foreign key emp_id.

You made a query to fetch all employee record with their contact nos, when you left join the query such like LEFT JOIN tbl_contact ON tbl_contact.emp_id=tbl_emp.emp_id, you will get record in associative array something like this.

array (size=3)
  0 => 
    array (size=3)
      'name' => string 'test1' (length=5)
      'age' => int 23
      'email' => string 'Good' (length=4)
  1 => 
    array (size=3)
      'name' => string 'test2' (length=5)
      'age' => int 24
      'email' => string 'Good' (length=4)
  2 => 
    array (size=3)
      'name' => string 'test2' (length=5)
      'age' => int 24
      'email' => string 'Best' (length=4)

 Here you are getting two record for test2 employee, because this employee has two email as a contact.

So, to simplify this you will need to have all contact nos inside a single variable key and the duplicate record to be removed, then here is the logic to do that.

 

 

  1. <?php
  2. $total_size=sizeof($arr);
  3. for($i=0;$i<$total_size;$i++){
  4. 	$arr[$i]['emails']=array();
  5. 	$arr[$i]['emails'][]=$arr[$i]['email'];
  6. 	$unset=0;
  7. 	foreach($arr[$i] as $key=>$value){
  8. 		if($key=='name' && $arr[$i]['name']==$arr[$i+1]['name']){
  9. 			$arr[$i]['emails'][]=$arr[$i+1]['email'];
  10. 			$unset=1;
  11. 		}
  12. 	}
  13. 	unset($arr[$i]['email']);
  14. 	if($unset==1){
  15. 		unset($arr[$i+1]);
  16. 		$i++;
  17. 	}
  18. }
  19. var_dump($arr);
  20. ?>

 

 

Now you’ll get the result something like that

array (size=2)
  0 => 
    array (size=3)
      'name' => string 'test1' (length=5)
      'age' => int 23
      'emails' => 
        array (size=1)
          0 => string 'Good' (length=4)
  1 => 
    array (size=3)
      'name' => string 'test2' (length=5)
      'age' => int 24
      'emails' => 
        array (size=2)
          0 => string 'Good' (length=4)
          1 => string 'Best' (length=4)

 

 

Posted in Algorithm, Array, Function, Method, MySQL, PHP, Programming, Sorting by Leave a comment