How to simulate the SQL LEFT JOIN operation using

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] =>

        )
)

Answer 1

This function simulates the left join operation

   //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;
    }

//the function can be used like so assuming the column name in the left array is the same name as the right array

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

//the function can be used like so assuming the column name in the left array "are different but has the same corresponding value"

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

Answer 2

Try to avoid this kind of operations. If you can, try to put all databases to same server and use SQL JOIN-s. PHP is not MYSQL. PHP is not dedicated for (left, right, etc) join tables. e.g. if you have huge numbers of records in left table returned by mysql and placed as result in array you can face big troubles with memory issue. Do MYSQL jobs using MYSQL, do PHP jobs using PHP. This way you will save: time, performances and neurons. :)

Answer 3

Try this (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;
        }
    }
}
//And here, we have to 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;
    }
}

Answer 4

a simple solution,

worked for me use this,

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

Thanks all

Answer 5

hi You can try this piece of code this will give you a merged array similar to 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 right array with prefix 'right_'