+ Post New Thread
Results 1 to 4 of 4
Web Development Thread, PHP ldap and mysql results merge in Coding and Web Development; I am currently trying to write a quick page for use with our phones, to centralise phonebook results. I have ...
  1. #1

    localzuk's Avatar
    Join Date
    Dec 2006
    Location
    Minehead
    Posts
    17,639
    Thank Post
    514
    Thanked 2,443 Times in 1,891 Posts
    Blog Entries
    24
    Rep Power
    831

    PHP ldap and mysql results merge

    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.

  2. #2

    SYNACK's Avatar
    Join Date
    Oct 2007
    Posts
    11,072
    Thank Post
    853
    Thanked 2,676 Times in 2,270 Posts
    Blog Entries
    9
    Rep Power
    769
    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_..._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_));
    Last edited by SYNACK; 17th November 2008 at 10:36 AM.

  3. #3

    Join Date
    Oct 2008
    Location
    Gosport
    Posts
    64
    Thank Post
    1
    Thanked 18 Times in 13 Posts
    Rep Power
    15
    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.
    PHP Code:
    <?php
    // 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 
    '<table>';
    foreach(
    $ad as $ext=>dataRow) {
        if( isset(
    $pbx[$ext]) ) {
            
    $dataRow2 $pbx[$ext];
        }
        
        echo 
    '<tr>';
        echo 
    '<td>'.$dataRow['ext'].'</td>';
        echo 
    '<td>'.$dataRow['description'].'</td>';
        echo 
    '<td>'.$dataRow['email'].'</td>';
        echo 
    '<td>'.$dataRow2['whateverelse'].'</td>';
        echo 
    '</tr>';
        
        @unset(
    $pbx[$ext]);
    }

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

    echo 
    '</table>';
    ?>
    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:

    PHP Code:
    // 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 
    '<table>';
    foreach(
    $allData as $row) {
        echo 
    '<tr>';
        echo 
    '<td>'.$row['ext'].'</td>';
        echo 
    '<td>'.$row['description'].'</td>';
        echo 
    '<td>'.$row['email'].'</td>';
        echo 
    '</tr>';
    }
    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

  4. #4

    Join Date
    Jul 2009
    Posts
    2
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    the initial mistake of iterating through each line of the ldap array and if they matched? then..

    _________________
    Ip pbx

SHARE:
+ Post New Thread

Similar Threads

  1. Best Site to learn php and mysql?
    By My220x in forum Coding
    Replies: 11
    Last Post: 23rd April 2008, 08:58 AM
  2. Can't get mysql and PHP to play together on IIS
    By dagza in forum Web Development
    Replies: 5
    Last Post: 20th January 2008, 03:38 PM
  3. ldap on php
    By browolf in forum Web Development
    Replies: 11
    Last Post: 8th May 2007, 09:33 AM
  4. Php/MySQL Web Design
    By Adam in forum How do you do....it?
    Replies: 8
    Last Post: 18th July 2005, 04:13 PM
  5. php MySql
    By iking in forum Books and Manuals
    Replies: 0
    Last Post: 11th July 2005, 03:35 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •