如何使用PHP数组模拟SQL LEFT JOIN操作? [英] How to simulate the SQL LEFT JOIN operation using PHP arrays?

问题描述

我有一个连接到多台服务器的应用程序.其中一台服务器的ID是到另一台服务器上的表的外键.这里的问题是MySQL不支持链接服务器，因此我无法运行左查询将左连接位于单独服务器上的2个表.

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.

因此，我必须使用PHP从2个不同的服务器中提取2个单独的查询，并且它们使用PHP左联接它们.

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

请注意，下面列出的阵列键必须是动态的.我不能使用固定名称，因为不同的查询将具有不同的列名称.下面的示例使用phone_call_id作为它们用来连接两个数组的键，它结合了列名.如果$ right_array有更多列，则需要将这些列添加到最终数组中.

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.

所以我有2个数组

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

正确的数组将如下所示

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

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

结果必须像这样的数组

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

        )
)

推荐答案

此函数模拟左联接操作

   //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');

这篇关于如何使用PHP数组模拟SQL LEFT JOIN操作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！