How to simulate the SQL LEFT JOIN operation using PHP arrays?

Question

I have an application that connects to multiple server. where one server will have ID that are foreign key to a table that is located on a different server. The issue here is that MySQL does not support linked servers so I can't run a left query that will LEFT join 2 tables located on separate servers.

So I have to pull 2 separate queries from 2 different server using PHP and they LEFT JOINing them using PHP.

Please note that the array keys listed below needs to be dynamic. I can't use a fixed names are different queries will have different column name. The example below use the phone_call_id as they key to use to join both arrays and it combines the column name. if $right_array has more columns then these columns need to be added to the final array.

so I have 2 array

$left_array = 
Array
(
    [0] => Array
        (
            [id] => 1
            [start_on] => 2014-09-14 19:50:00
            [end_on] => 2014-09-14 19:51:00
            [subject] => This is a new event
            [client_id] => 
            [all_day_event] => 0
            [event_type] => Event
            [phone_call_id] => 122
        )

    [1] => Array
        (
            [id] => 2
            [start_on] => 2014-09-15 05:53:00
            [end_on] => 2014-09-15 06:53:00
            [subject] => This is a new event
            [client_id] => 
            [all_day_event] => 0
            [event_type] => Event
            [phone_call_id] => 123
        )

    [2] => Array
        (
            [id] => 3
            [start_on] => 2014-09-15 05:53:00
            [end_on] => 2014-09-15 06:53:00
            [subject] => This is a new event
            [client_id] => 
            [all_day_event] => 0
            [event_type] => Event
            [phone_call_id] => 
        )
)

The right array will look like this

$right_array = 
Array
(
    [0] => Array
        (
            [account_id] => 1
            [phone_call_id] => 122
        )

    [1] => Array
        (
            [account_id] => 2
            [phone_call_id] => 123
        )
)

the results needs to be like this array

$joined_array = 
Array
(
    [0] => Array
        (
            [id] => 1
            [start_on] => 2014-09-14 19:50:00
            [end_on] => 2014-09-14 19:51:00
            [subject] => This is a new event
            [client_id] => 
            [all_day_event] => 0
            [event_type] => Event
            [phone_call_id] => 122
            [account_id] => 1
        )

    [1] => Array
        (
            [id] => 2
            [start_on] => 2014-09-15 05:53:00
            [end_on] => 2014-09-15 06:53:00
            [subject] => This is a new event
            [client_id] => 
            [all_day_event] => 0
            [event_type] => Event
            [phone_call_id] => 123
            [account_id] => 2
        )

    [2] => Array
        (
            [id] => 3
            [start_on] => 2014-09-15 05:53:00
            [end_on] => 2014-09-15 06:53:00
            [subject] => This is a new event
            [client_id] => 
            [all_day_event] => 0
            [event_type] => Event
            [phone_call_id] => 
            [account_id] =>

        )
)

philipxy · Accepted Answer · 2025-08-16 11:12:22Z

6

function left_join_array($left, $right, $left_join_on, $right_join_on = NULL){
    $final= array();
    
    if(empty($right_join_on))
        $right_join_on = $left_join_on;
            
    foreach($left AS $k => $v){
        $final[$k] = $v;
        foreach($right AS $kk => $vv){
            if($v[$left_join_on] == $vv[$right_join_on]){
                foreach($vv AS $key => $val)
                    $final[$k][$key] = $val; 
            } else {
                foreach($vv AS $key => $val)
                    $final[$k][$key] = NULL;            
            }
        }
    }
    return $final;
}

The column name in the left array is the same as in the right array:

$final_array = left_join_array($left, $right, 'phone_call_id');

The column names in the arrays are different:

$final_array = left_join_array($left, $right, 'phone_call_id', 'p_c_id');

edited Aug 16 at 11:12

philipxy

15.2k6 gold badges44 silver badges101 bronze badges

answered Sep 14, 2014 at 19:56

Jaylen

40.5k43 gold badges140 silver badges228 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

philipxy · Accepted Answer · 2025-08-16 11:15:13Z

2

For a merged array similar to a left outer join:

foreach($left_array as $k => $v){
   foreach($right_array as $kk => $vv){
     if($v['id'] == $vv['account_id']){
         foreach($vv as $key => $val){
             $left_array[$k]['right_'.$key] = $val; 
         }
     }
   }
}

You will get fields from the right array with prefix right_.

edited Aug 16 at 11:15

philipxy

15.2k6 gold badges44 silver badges101 bronze badges

answered Sep 14, 2014 at 18:42

Pulkit

1375 bronze badges

3 Comments

Jaylen Over a year ago

Hi, I took your code a modified it a little to make it fit my need. but the issue that I am running into now is that when the phone_call_id is null then I will have a missing cell in the array. I will put the code that I currently have in my post so you can take a look at it.

Pulkit Over a year ago

Sorry I think I didn't get what you are trying to say. According to logic if some field is null or empty it will return same only, correct? Please put your code. I will check that and try to give suitable answer. :)

Jaylen Over a year ago

I fixed the function and posted it as an answer to help out others. thank you for giving me the needed hits to solve the problem

philipxy · Accepted Answer · 2025-08-16 11:29:29Z

In my scenario:

I applied another answer on this page:

//function to simulate the left join
function left_join_array($left, $right, $left_join_on, $right_join_on = NULL){
    $final= array();

    if(empty($right_join_on))
        $right_join_on = $left_join_on;

    foreach($left AS $k => $v){
        $final[$k] = $v;
        foreach($right AS $kk => $vv){
            if($v[$left_join_on] == $vv[$right_join_on]){
                foreach($vv AS $key => $val)
                    $final[$k][$key] = $val; 
            } else {
                foreach($vv AS $key => $val)
                    $final[$k][$key] = NULL;            
            }
        }
    }
   return $final;
}

Result:

That's not the result that I desired, because everything is null even though there's matching data on the right table, and it was because of the next iteration of foreach replacing the right value to null.

I added a break on the last foreach, also adding a prefix on the appended value to avoid replacing the left table (in case the columns have the same name in the two tables):

//function to simulate the left join
function left_join_array($left, $right, $left_join_on, $right_join_on = NULL)
{
  $final = array();

  if (empty($right_join_on))
    $right_join_on = $left_join_on;

  foreach ($left as $k => $v) {
    $final[$k] = $v;
    foreach ($right as $kk => $vv) {
      if ($v[$left_join_on] == $vv[$right_join_on]) {
        foreach ($vv as $key => $val)
          $final[$k]['_' . $key] = $val; /* add prefix on the key */
        break; /* add break here */
      } else {
        foreach ($vv as $key => $val)
          $final[$k]['_' . $key] = NULL; /* add prefix on the key */
      }
    }
  }
  return $final;
}

Result (desired):

philipxy · Accepted Answer · 2025-08-16 10:31:18Z

0

function arrayLeftJoin($left,$right){
    foreach ($left as $lkey => $lvalue) {
        foreach ($right as $rkey => $rvalue) {
            if($lkey==$rkey){
                unset($left[$lkey]);
            }
        }
    }
    return $left;
}

edited Aug 16 at 10:31

philipxy

15.2k6 gold badges44 silver badges101 bronze badges

answered Jan 20, 2018 at 7:48

Dhanu Kanakala

12.5k6 gold badges27 silver badges42 bronze badges

1 Comment

mickmackusa Aug 18 at 3:12

This answer is missing its educational explanation.

philipxy · Accepted Answer · 2025-08-16 10:38:08Z

0

Update to Jaylen solution:

function left_join_array($left, $right, $left_join_on, $right_join_on = NULL){
     $final= array();

     if(empty($right_join_on))
         $right_join_on = $left_join_on;

     foreach($left AS $k => $v){
         $final[$k] = $v;
         foreach($right AS $kk => $vv){
             if($v[$left_join_on] === $vv[$right_join_on] ){
                 foreach($vv AS $key => $val)
                     $final[$k][$key] = $val; 
             } else if(!isset($final[$k][$right_join_on])) {
                 foreach($vv AS $key => $val)
                     $final[$k][$key] = NULL;            
             }
         }
     }
    return $final;
 }

edited Aug 16 at 10:38

philipxy

15.2k6 gold badges44 silver badges101 bronze badges

answered Sep 25, 2020 at 9:11

genio

1

Comments

philipxy · Accepted Answer · 2025-08-16 10:41:46Z

0

Try to avoid this kind of operation. Try to put all databases on the same server and use SQL joins.

PHP is not MySQL. PHP is not intended for left, right, etc. table joins. E.g. if you have a huge number of records in the left table returned by MySQL and placed in an array you can face big memory issues. Do MYSQL jobs using MYSQL; do PHP jobs using PHP. You will save time, performance and neurons.

edited Aug 16 at 10:41

philipxy

15.2k6 gold badges44 silver badges101 bronze badges

answered May 11, 2018 at 16:55

Levac Onlajn

2812 silver badges7 bronze badges

2 Comments

Renan C Over a year ago

And what if you're working with a distributed system that have tables on different servers?

Levac Onlajn Over a year ago

You might say that in that case you are in trouble. :( In that case, you will have to use PHP to simulate JOIN ... However, you will very soon face the problems I have described earlier. Especially in LEFT JOIN cases over tables that contain very many rows (if you have such tables). In some cases, you could meet problems that can not be solved.

philipxy · Accepted Answer · 2025-08-16 11:16:52Z

0

Assuming that your empty 'phone_call_id' and 'accout_id' values are null:

$joined_array = array();
foreach($right_array as $right_row){
    $phone_call_id = $right_row['phone_call_id'];
    $account_id    = $right_row['account_id'];
    foreach($left_array as $i => $left_row){
        if($left_row['phone_call_id'] === $phone_call_id){
            $left_row['account_id'] = $account_id;
            $joined_array[]         = $left_row;
        }
    }
}
// identify rows with empty 'phone_call_id'
foreach($left_array as $left_row){
    if($left_row['phone_call_id'] === null){
        $left_row['account_id'] = null;
        $joined_array[]         = $left_row;
    }
}

edited Aug 16 at 11:16

philipxy

15.2k6 gold badges44 silver badges101 bronze badges

answered Sep 14, 2014 at 18:42

Facundo Fasciolo

4924 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to simulate the SQL LEFT JOIN operation using PHP arrays?

7 Answers 7

Comments

3 Comments

Comments

1 Comment

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

3 Comments

Comments

1 Comment

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related