How can I combine two queries from LDAP and MYSQL?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jhurd8025
Forum Newbie
Posts: 3
Joined: Wed Apr 16, 2008 3:49 pm
Location: Dallas, Texas
Contact:

How can I combine two queries from LDAP and MYSQL?

Post by jhurd8025 »

~pickle | Please use [ code=html ], [ code=php ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.


Sorry for the PHP Newb Questions.

The first php code chunk below grabs a list of computers from our mysql database based on the system_os_name, contains 'server'.

Code: Select all

 
<?php
 
$query_array=array("headline"=>__("List all Windows Servers"),
                   "sql"=>"SELECT * FROM `system` WHERE (system_os_name LIKE '%Server%')",
                   "sort"=>"system_name",
                   "dir"=>"ASC",
                   "get"=>array("file"=>"system.php",
                                "title"=>__("Go to System"),
                                "var"=>array("pc"=>"%system_uuid",
                                             "view"=>"summary",
                                            ),
                               ),
                   "fields"=>array("10"=>array("name"=>"system_uuid",
                                               "head"=>__("UUID"),
                                               "show"=>"n",
                                              ),
                                   "20"=>array("name"=>"net_ip_address",
                                               "head"=>__("IP"),
                                               "show"=>"y",
                                               "link"=>"y",
                                              ),
                                   "30"=>array("name"=>"system_name",
                                               "head"=>__("Hostname"),
                                               "show"=>"y",
                                               "link"=>"y",
                                              ),
                                    "40"=>array("name"=>"net_domain",
                                               "head"=>"Domain",
                                               "show"=>$show_domain,
                                              ),
                                   "50"=>array("name"=>"system_os_name",
                                               "head"=>__("OS"),
                                               "show"=>$show_os,
                                              ),
                                    "60"=>array("name"=>"system_service_pack",
                                               "head"=>"Servicepack",
                                               "show"=>$show_service_pack,
                                              ),
                                   "70"=>array("name"=>"system_timestamp",
                                               "head"=>__("Date Audited"),
                                               "show"=>$show_date_audited,
                                              ),
                                   "80"=>array("name"=>"system_system_type",
                                               "head"=>__("System Type"),
                                               "show"=>$show_type,
                                               "align"=>"center",
                                              ),
                                   "90"=>array("name"=>"system_description",
                                               "head"=>"Description",
                                               "show"=>$show_description,
                                              ),
 
 
                                  ),
                  );
?>
 
The second is just a basic ldap search lookup by OU.

Code: Select all

 
<?php
    $host = "lab.constoso.com";
    $port = "389";
    $dn = "CN=Computers,DC=lab,DC=contoso,DC=com";
    $connection = ldap_connect($host)
        or die("Can't establish LDAP connection");
 
    ldap_set_option($connection, LDAP_OPT_PROTOCOL_VERSION, 3);
    ldap_set_option($connection, LDAP_OPT_REFERRALS,0);
    ldap_bind($connection,"user","password")
        or die("Can't bind to the server.");
    
    
    
    $results = ldap_search($connection, $dn, "objectcategory=computer");
    
    
    $entries = ldap_get_entries($connection, $results);
    
    
    $count = $entries["count"];
    
    for($x=0; $x < $count; $x++) {
    printf("%s ", $entries[$x]["cn"][0]); 
    echo "<br>";
    }
?>
 
What I would like to do is combine to the two, and be able to get a list of all servers in a particular OU.

So it would have to take the list from the mysql database (b/c some servers might not be in the active directory), and then cross reference that to the ldap database ou lookup. Is this possible in php, or am I even looking at this the wrong way (possibly and easier way)?

PS I know the second code is pretty basic, and I'd probably be better off storing the host,port,user information elsewhere..

Thanks for any help, I want to do this so bad... but I'm too new to php to figure out even where to start.


~pickle | Please use [ code=html ], [ code=php ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.
jhurd8025
Forum Newbie
Posts: 3
Joined: Wed Apr 16, 2008 3:49 pm
Location: Dallas, Texas
Contact:

Re: How can I combine two queries from LDAP and MYSQL?

Post by jhurd8025 »

Anyone?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: How can I combine two queries from LDAP and MYSQL?

Post by pickle »

There's no real way to combine them as they're different constructs.

Is there a common, unique identifier that you retrieve both via MySQL & LDAP? Say, a MAC address? If there is, you can build an array out of the results of one or both queries, then iterate through the arrays & build a new, combined array.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
jhurd8025
Forum Newbie
Posts: 3
Joined: Wed Apr 16, 2008 3:49 pm
Location: Dallas, Texas
Contact:

Re: How can I combine two queries from LDAP and MYSQL?

Post by jhurd8025 »

~pickle | Please use [ code=html ], [ code=php ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.


First off I apologize for being so new to this, so its a bit hard to grasp first off.

But would something like this work? (Code may be completly wrong)

Code: Select all

 
<?php
 
    $host = "lab.constoso.com";
    $port = "389";
    $dn = "CN=Computers,DC=lab,DC=contoso,DC=com";
    $connection = ldap_connect($host)
        or die("Can't establish LDAP connection");
 
    ldap_set_option($connection, LDAP_OPT_PROTOCOL_VERSION, 3);
    ldap_set_option($connection, LDAP_OPT_REFERRALS,0);
    ldap_bind($connection,"user","password")
        or die("Can't bind to the server.");
    
    $results = ldap_search($connection, $dn, "objectcategory=computer");
    
    $entries = ldap_get_entries($connection, $results); 
    $count = $entries["count"];
    
    // Cycle through array and output name and e-mail address
    for($x=0; $x < $count; $x++) {
    $sql .= "'" . $entries[$x]["cn"][0] . "',";
    }
$sql = substr($sql, 0, -1) . ");";
 
 
 
$query_array=array("headline"=>__("List all Windows Servers"),
                   "sql"=>"SELECT * FROM `system` WHERE (system_name LIKE '$sql')",
                   "sort"=>"system_name",
                   "dir"=>"ASC",
                   "get"=>array("file"=>"system.php",
                                "title"=>__("Go to System"),
                                "var"=>array("pc"=>"%system_uuid",
                                             "view"=>"summary",
                                            ),
                               ),
                   "fields"=>array("10"=>array("name"=>"system_uuid",
                                               "head"=>__("UUID"),
                                               "show"=>"n",
                                              ),
                                   "20"=>array("name"=>"net_ip_address",
                                               "head"=>__("IP"),
                                               "show"=>"y",
                                               "link"=>"y",
                                              ),
                                   "30"=>array("name"=>"system_name",
                                               "head"=>__("Hostname"),
                                               "show"=>"y",
                                               "link"=>"y",
                                              ),
                                    "40"=>array("name"=>"net_domain",
                                               "head"=>"Domain",
                                               "show"=>$show_domain,
                                              ),
                                   "50"=>array("name"=>"system_os_name",
                                               "head"=>__("OS"),
                                               "show"=>$show_os,
                                              ),
                                    "60"=>array("name"=>"system_service_pack",
                                               "head"=>"Servicepack",
                                               "show"=>$show_service_pack,
                                              ),
                                   "70"=>array("name"=>"system_timestamp",
                                               "head"=>__("Date Audited"),
                                               "show"=>$show_date_audited,
                                              ),
                                   "80"=>array("name"=>"system_system_type",
                                               "head"=>__("System Type"),
                                               "show"=>$show_type,
                                               "align"=>"center",
                                              ),
                                   "90"=>array("name"=>"system_description",
                                               "head"=>"Description",
                                               "show"=>$show_description,
                                              ),
 
 
                                  ),
                  );
 

First it would query CN=Computers for all computer objects underneath it. Then the second part would query for all systems in the mysql database that are in the array $sql?

Would this work?


~pickle | Please use [ code=html ], [ code=php ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: How can I combine two queries from LDAP and MYSQL?

Post by pickle »

It looks like it could work, but it's hard to say due to the funky way you're building your MySQL query. After the LDAP query, you build $sql, but I'm not seeing it used anywhere in that big query array.

Like I mentioned in your post before, wrap your code in [syntax=php][/syntax] tags, rather than just the default [syntax=php][/syntax] tags.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply