?
Solved

How do I compare the results of 2 different queries?

Posted on 2011-09-30
6
Medium Priority
?
386 Views
Last Modified: 2013-12-13
I am trying to compare the results of 2 different types of queries.

the first query is a ldap query, looking for accounts which are disabled.
gathering the following : (displayname, mail, descriptions, physicalofficename)

the second query is a sql query against the bes database looking for active bes users
gathering the following : (displayname, mailboxsmtpaddr, status, lastsenttime)

Essentially - I want to evaluate both queries, and determine by either matching the displayname or email address, so we can determine accounts which are disabled on either end.

I get both individual queries working perfectly, and I'm thinking I simply need to create an array with the 2 different queries, and then intersect to find the unique data.

However, I'm a little fuzzy on how best to do this with 2 dissimalar queries..

Could anyone give any insight of how to do this?

Thank you all for your help!
Chris


LDAP PHP PAGE
 
// Specify only those parameters we're interested in displaying
$attrs = array("displayname","mail","description","physicaldeliveryofficename");

// Create the filter from the search parameters
//$filter = $_POST['filter']."=".$_POST['keyword']."*";
$filter = "userAccountControl=546";

$search = ldap_search($ad, $dn, $filter, $attrs)
          or die ("ldap search failed");

$entries = ldap_get_entries($ad, $search);

// function from http://php.net/manual/en/function.ldap-sort.php //
function sort_ldap_entries($e, $fld, $order) 
{ 
    for ($i = 0; $i < $e['count']; $i++) { 
        for ($j = $i; $j < $e['count']; $j++) { 
            $d = strcasecmp($e[$i][$fld][0], $e[$j][$fld][0]); 
            switch ($order) { 
            case 'A': 
                if ($d > 0) 
                    swap($e, $i, $j); 
                break; 
            case 'D': 
                if ($d < 0) 
                    swap($e, $i, $j); 
                break; 
            } 
        } 
    } 
    return ($e); 
} 

function swap(&$ary, $i, $j) 
{ 
    $temp = $ary[$i]; 
    $ary[$i] = $ary[$j]; 
    $ary[$j] = $temp; 
} 
?> 
<html>
<body>
<br /><br />
<?php 
    $entries = sort_ldap_entries($entries, 'displayname', 'A'); // sort entries by ascending order of mail 
?> 
<table border="1px" style="border-collapse:collapse; border:solid 1px #666; font-size:12px;" width="100%">
    <tr>
        <td><strong>Name</strong></td>
        <td><strong>Email</strong></td>
        <td><strong>Description</strong></td>
        <td><strong>PhysicalOffice</strong></td>
    </tr>
	<?php
    for ($i=0; $i<$entries["count"]; $i++)
    {
        echo "<tr><td>" . $entries[$i]["displayname"]
             [0] . "</td>" . "<td>" . $entries[$i]["mail"][0] . "</td>" . "<td>" . $entries[$i]["description"][0] . "</td>" . "<td>" . $entries[$i]["physicaldeliveryofficename"][0] . "</td></tr>";
    }
?>
</table>
</body>
</html>
<?php
    ldap_unbind($ad);
?>

Open in new window


SQL PHP PAGE
 
<?php 
include ("db/bes_db_connect.php");
$query = "SELECT UserConfig.Id, UserConfig.DisplayName, UserConfig.MailboxSMTPAddr, UserStats.UserConfigId, UserStats.Status, UserStats.LastSentTime ";
$query .= "FROM UserConfig, UserStats ";
$query .= "WHERE UserStats.Status like '%12%' AND UserStats.UserConfigId = UserConfig.Id"; 

//execute the SQL query and return records
$result = mssql_query($query);
$numRows = mssql_num_rows($result); 
?>
<html>
<body>
<?php
echo "<h1>" . $numRows . " Row" . ($numRows == 1 ? "" : "s") . " Returned </h1>"; 
//display the results
?>
<br /><br />
<table border="1px" style="border-collapse:collapse; border:solid 1px #666; font-size:12px;" width="100%">
<tr>
<td><strong>DisplayName</strong></td>
<td><strong>MailboxSMTPAddr</strong></td>
<td><strong>Status</strong></td>
<td><strong>LastSentTime</strong></td>
</tr>
<?php
while($row = mssql_fetch_array($result))
{
  echo "<tr>";
  echo "<td>" . $row["DisplayName"] . " </td><td> " . $row["MailboxSMTPAddr"] . " </td><td> " . $row["Status"] . " </td><td> " . $row["LastSentTime"] . "</td>";
  echo "</tr>";
}
//close the connection
mssql_close($dbhandle);
?>
</table>
<br /><br />
</body>
</html>

Open in new window

0
Comment
Question by:udsfsg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 9

Expert Comment

by:ghodder
ID: 36896042
So your LDAP query only returns disabled accounts?

Basically run your LDAP query and get the results into an array, run your SQL query and get the results into an array, then loop over the LDAP results and check whether the displayname exists in the SQL array.


Some pseudo-code might be
$entries = ldap_get_entries($ad, $search);
$result = mssql_query($query);
$sql_result = array();

foreach (mssql_fetch_row($result) as $row) {
    $sql_result[] = $row;
}

foreach ($entries as $row) {
    if (in_array($row['displayname'][0], $sql_result)) {
        // Do whatever
    }
}

Open in new window

0
 
LVL 13

Expert Comment

by:Hugh McCurdy
ID: 36899662
Do you still need help?  If so, more information would help.  It sounds like you want a report (?) of all accounts that are disabled (at either end).  I would guess that you don't want the same account showing up on the report twice.  This implies some sort of sort.  However, at this point I'm doing a lot of guessing about what you really want.  (Nor do I know if you've solved the problem already.)
0
 
LVL 2

Author Comment

by:udsfsg
ID: 36903804
ghodder, That is exactly what I was looking for.
Thank you very much sir.

I do have 1 small issue, everything appears to be working, and I have verified, I don't have erronious entries, however, my loop appears to repeat even after adding an else statement. Is there a better way to terminate the loop to have a little cleaner ending?

 looping

<?php
$entries = ldap_get_entries($ad, $search);
$result = mssql_query($query);

$sql_result = array();  
?>
<h2>Deactivated AD accounting matching an Active BES account.</h2>
<p>This table should remain empty if all accounts have been properly terminated. </p>
<?php
foreach (mssql_fetch_row($result) as $row) {  
	$sql_result[] = $row;  
}  
foreach ($entries as $row) {  
	if (in_array($entries['mail'], $sql_result)) {  
		echo "Match found!:" .$entries['mail'] . " AND " . $row['MailboxSMTPAddr'] . "located in both AD and BES.";
	} else {
		echo "No matching records found..";
	}
} 
//close the connection
ldap_unbind($ad);
mssql_close($dbhandle);
?>

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Accepted Solution

by:
Hugh McCurdy earned 2000 total points
ID: 36903934
Will this work for you?

	} else {
		echo "No matching records found..";
                break;
	}

Open in new window

0
 
LVL 2

Author Closing Comment

by:udsfsg
ID: 36904148
Thank you very much for the help.
0
 
LVL 13

Expert Comment

by:Hugh McCurdy
ID: 36904734
Glad to have been of assistance.  Thanks for the points.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this. Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it i…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question