Joining an Array like MySQL LEFT JOIN

How do you join a PHP array like MySQL joins using LEFT JOIN

[sc0ttkclark]

Okay, time to rack your brains. I'm in the process of creating a new function (I couldn't find one online) to join an array based on an index. Why? There are two separate MySQL databases on two separate servers. I need to join the results from the first with the second since MySQL doesn't have a native way to connect the two.

1. Two "result" arrays are passed to function, the values of each array contain an array of "columns" being the key and the "values" being the value.

2. One "join" array is passed to function, containing an array of values for which keys to "LEFT JOIN" the arrays on.

3. The results are sent back combined, just as the "LEFT JOIN" does with MySQL.

4. Assume (for now) that this JOIN will only be joined by one value.

5. Example MySQL Code if they were on the same server:

mysql_query("SELECT one.this_id, one.this_info, one.approved, two.that_id, two.that_info
FROM table_one AS one LEFT JOIN table_two AS two ON two.this_id = one.this_id WHERE one.approved=1");

[edited by: coopster at 4:05 pm (utc) on Feb. 26, 2009]
[edit reason] fixed sidescroll [/edit]

[sc0ttkclark]

BTW, if anyone knows of an existing PHP function that can do this, let me know!

Here is what I have so far:

function my_search_array($array,$index,$value)
{
$ret = array();
foreach($array as $k=>$v)
{
$check = array();
if(is_array($v))
{
$check = my_search_array($v,$index,$value);
}
else
{
if($k==$index&&$v==$value)
{
$check = $v;
}
}
if(!empty($check))
{
$ret[] = $check;
}
}
return $ret;
}
function my_record_left_join ($join,$result_1,$result_2)
{
$ret = array();
foreach($result_1 as $row)
{
$new_1 = $row;
$new_2 = array();
// search the array and return the value
if(isset($new_1[$join]))
{
$new_2 = my_search_array($result_2,$join,$new_1[$join]);
}
// put them together now
$ret[] = array_merge($new_1,$new_2);
}
return $ret;
}
// Next is Sample Data
// As you can see here each array is already "ordered" by their respective "info" column
$result_1 = array(array('this_id'=>2,'this_info'=>'bam!'),array('this_id'=>1,'this_info'=>'blah'),array('this_id'=>3,'this_info'=>'brut'));
$result_2 = array(array('that_id'=>5,'this_id'=>2,'that_info'=>'bert'),array('that_id'=>4,'this_id'=>1,'that_info'=>'boom'));
$join = 'this_id';
// Now LEFT JOIN the Arrays together
$big_result = my_record_left_join($join,$result_1,$result_2);
 

[sc0ttkclark]

Ouch, sorry about the formatting being all weird!

[sc0ttkclark]

Looks like I've found solution of one, getting it to join on one column! I'll post the final function when I'm done for everyone to see. I figured I'd put this on Webmaster World since everytime I'm searching for how to do things, this is the place that usually has the answers :-)

function my_search_array($array,$index,$value)
{
$ret = array();
foreach($array as $k=>$v)
{
if(is_array($v))
{
foreach($v as $k2=>$v2)
{
if($k2==$index&&$v2==$value)
{
$ret[] = $v;
break;
}
}
}
else
{
if($k==$index&&$v==$value)
{
$ret[] = $v;
}
}
}
return $ret;
}
function my_record_left_join ($join,$result_1,$result_2)
{
$ret = array();
foreach($result_1 as $new_1)
{
$new_2 = array();
// search the array and return the value
if(isset($new_1[$join]))
{
$new_2 = my_search_array($result_2,$join,$new_1[$join]);
}
// put them together now
if(!empty($new_2))
{
foreach($new_2 as $item)
{
$ret[] = array_merge($new_1,$item);
}
}
else
{
$ret[] = $new_1;
}
}
return $ret;
}
// Next is Sample Data
// As you can see here each array is already "ordered" by their respective "info" column
$result_1 = array(array('this_id'=>2,'this_info'=>'bam!'),array('this_id'=>1,'this_info'=>'blah'),array('this_id'=>3,'this_info'=>'brut'));
$result_2 = array(array('that_id'=>5,'this_id'=>2,'that_info'=>'bert'),array('that_id'=>4,'this_id'=>1,'that_info'=>'boom'));
$join = 'this_id';
// Now LEFT JOIN the Arrays together
$big_result = my_record_left_join($join,$result_1,$result_2);
 

[sc0ttkclark]

Getting a better picture of it now, I've updated a bunch of the options, and VERY sorry ahead of time for the very confusing wording I'm using.

PM me if you want the formatted code and I'll send it.

Here are my latest changes to the code:

1. The join now accepts one result array which contains an array of result arrays :-)

2. There is a select variable now that will setup the default variables to NULL incase there are no results to LEFT JOIN on or nothing matches the criteria

3. The Join variable can now be an array, and if you want to set the value to be joined to ONLY be joined on then you can set that join value as a key and pass an array with "value" as the key and your criteria into that value

function my_search_array($array,$index,$value)
{
$ret = array();
foreach($array as $k=>$v)
{
if(is_array($v))
{
foreach($v as $k2=>$v2)
{
if($k2==$index&&$v2==$value)
{
$ret[] = $v;
break;
}
}
}
else
{
if($k==$index&&$v==$value)
{
$ret[] = $v;
}
}
}
return $ret;
}
function my_record_left_join ($select,$join,$resultset)
{
if(!is_array($resultset))
{
return false;
}
$ret = array();
if(!is_array($select)){$select = explode(',',$select);}
if(!is_array($join)){$join = array($join);}
$default = array();
foreach($select as $sel)
{
$default[$sel] = NULL;
}
foreach($resultset[0] as $key=>$result)
{
$new = array_merge($default,$result);
$go = array();
// set the default index for the join
$x=0;
// search the array and return the value
foreach($join as $jkey=>$j)
{
$thejoin = $j;
$value_go=0;
if(is_array($j))
{
$thejoin = $jkey;
if(isset($j['value']))
{
$value = $j['value'];
$value_go=1;
}
}
if($value_go==0&&isset($new[$thejoin]))
{
$value = $new[$thejoin];
}
foreach($resultset as $result_key=>$new_result)
{
if($result_key==0){continue;}
$go[] = my_search_array($new_result,$thejoin,$value);
}
}
// put them together now
if(!empty($go))
{
$go = array_filter($go);
foreach($go as $item)
{
$new = array_merge($new,$item[0]);
}
}
$ret[] = $new;
}
return $ret;
}
// Next is Sample Data
// As you can see here each result array is already "ordered" by their respective "info" column
$select = 'this_id,this_info,that_id,that_info,there_id,there_info';
$join = array('this_id'=>array('value'=>'2'),'there_id');
$result_1 = array(array('that_id'=>5,'there_id'=>1,'this_id'=>2,'that_info'=>'bert'),
array('that_id'=>8,'this_id'=>3,'there_id'=>2,'that_info'=>'bird'),
array('that_id'=>4,'this_id'=>2,'there_id'=>1,'that_info'=>'boom'));
$result_2 = array(array('this_id'=>2,'this_info'=>'bam!'),
array('this_id'=>1,'this_info'=>'blah'),
array('this_id'=>3,'this_info'=>'brut'));
$result_3 = array(array('there_id'=>1,'there_info'=>'crow'),
array('there_id'=>2,'there_info'=>'claw'));
$resultset = array($result_1,$result_2,$result_3);
// Now LEFT JOIN the Arrays together
$big_result = my_record_left_join($select,$join,$resultset);
 

[edited by: coopster at 4:06 pm (utc) on Feb. 26, 2009]
[edit reason] fixed sidescroll [/edit]