PHP ldap and mysql results merge

[localzuk]

I am currently trying to write a quick page for use with our phones, to centralise phonebook results.

 

I have all the staff phone extensions, and email addresses in one place - our active directory. This is kept up to date, as it is a nice place to do so.

 

I also have the freepbx 'users' table, which contains a list of all the phone extensions on the system. This includes all the phone numbers from the active directory, but not email addresses. It also has extra extensions such as 'Year 5 block' etc...

 

So neither list contains everything.

 

Now, rather than having to create Active Directory objects for the non-people extensions, is there a way I can merge the results from a php ldap_search and a mysql_query?

 

I made the initial mistake of iterating through each line of the ldap array and if they matched, output one thing, if they didn't then output another. But this meant that every single row contained every single record from the mysql array...

 

I can think of one way of dealing with it, this being a third array which I add the index values to when a match is made, which is checked by the above loops, and if that row has already been output then it isn't output again, but this seems very messy.

 

So any ideas? I can post snippets of code if that will help.

[SYNACK]

How do the two data sources return results? arrays?

 

The easiest way that I can think of if it uses arrays if grabbing both of the arrays and adding them to a seporate mySQL table then doing a UNIQUE select on the table, if all of the data is included in each of the data sources then a UNIQUE query will grab the required data without any extra codeing by you as the DB engine will take care of that for you.

 

Looks like you can also do it in code with something like this:

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_23564673.html

o i found the solution. actually here i didnt had to merge arrays

 

foreach ($group_id as $m)

{

$r = qqq("SELECT cust_id_array FROM customers_groups WHERE group_id='$m'");

$var .= ",{$r[0]['cust_id_array']}";

}// end first foreach

$var_= substr($var, 1);

echo "$var_";

$mergedUnicArray = array_unique(explode(',' , $var_));

Edited November 17, 2008 by SYNACK

[lightinthedark]

From my experience, doing any query in a loop has potential to be very very slow / process intensive.

 

I'd get all the items into 2 arrays (as it sounds like you're already doing), and make sure they're both indexed on a common field (the extension number?). Loop through the AD one, output its results and any matching result from the pbx array, then unset that matching result in the pbx array. At the end, the pbx array will only have the results that haven't already been displayed, so loop through and output them.

// these functions should each get you an array indexed on extension,
// containing an array of the data you want to display from that source
$ad = getTheADDataSomehow();
$pbx = getThePbxDataSomehow();

echo '</pre><table>';
foreach($ad as $ext=>dataRow) {
if( isset($pbx[$ext]) ) {
	$dataRow2 = $pbx[$ext];
}

echo '';
echo ''.$dataRow['ext'].'';
echo ''.$dataRow['description'].'';
echo ''.$dataRow['email'].'';
echo ''.$dataRow2['whateverelse'].'';
echo '';

@unset($pbx[$ext]);
}

foreach($pbx as $ext=>dataRow) {
echo '';
echo ''.$dataRow['ext'].'';
echo ''.$dataRow['description'].'';
echo '--';
echo ''.$dataRow['whateverelse'].'';
echo '';
}

echo '</table>';<br

 

Something like that. The initial 2 functions called you'll probably have to write, but should be very simple to create arrays from query results. This is an easy-but-dumb approach.

 

You possibly do better by only constructing one array from your two result sets and then just looping through that, depending on your exact requirements and how the results come out. I'm not particularly familiar with getting and parsing results from ldap queries.

I expect you'd do something along the lines of:

 

// as per the example on php.net
$dn = "o=My Company, c=US";
$filter="(|(sn=$person*)(givenname=$person*))";
$justthese = array("ou", "sn", "givenname", "mail");

$sr=ldap_search($ds, $dn, $filter, $justthese);

$info = ldap_get_entries($ds, $sr);
// end copied example

// re-format the $info array to be more useable
foreach( $info as $row ) {
   $id = $row['attribute'][1]; // assuming this is where the extension is stored
   $niceInfo[$id] = $row['attribute'][2]; // assuming this is where the email is stored
}


$conn = mysql_connect("localhost", "mysql_user", "mysql_password");
mysql_select_db("mydbname");

$sql = "SELECT ext, description
       FROM   freepbx_table";
$result = mysql_query($sql);

$allData = array();
$id = 0;
while ($row = mysql_fetch_assoc($result)) {
   $id++;
   $allData[$id]['ext'] = $row['ext'];
   $allData[$id]['description'] = $row['description'];
   $allData[$id]['email'] = (isset($niceInfo[$row['ext']]) ? $niceInfo[$row['ext']] : '--');
}

mysql_free_result($result);

echo '</pre><table>';
foreach($allData as $row) {
   echo '';
   echo ''.$row['ext'].'';
   echo ''.$row['description'].'';
   echo ''.$row['email'].'';
   echo '';
}
echo '</table>

 

but be aware that that has a certain amount of overhead by parsing the ldap results to re-format them.

 

All code untested and un-guaranteed, but I hope it gives you some ideas.

 

Hello

:-Dave

[spikeinin]

the initial mistake of iterating through each line of the ldap array and if they matched? then..

 

_________________

Ip pbx