Array in WHERE clause

[DjMikeS]

Hi all,

 

I'm trying to write a query that will search the db using an array as where clause...

Unfortunately, it only returns a single row, which it shouldn't return at all...

 

The code:

<?php
function getServices($intTid, $uid) {
	$qryRemHosts = "SELECT removed_hosts FROM usr_preferences WHERE uid='$uid'";
	try {
		if (!$rsltRemHosts = mysql_query($qryRemHosts)) {
			$mysql_error = mysql_error();
			throw new Exception($mysql_error);
			return false;
		}
/* 		if (!$rsltLastServices = mysql_query($qryLastServices)) {
			$mysql_error = mysql_error();
			throw new Exception($mysql_error);
			return false;
		} */
		else {
			$arrRemHosts = mysql_fetch_array($rsltRemHosts);
			$arrRemHosts = explode(";", $arrRemHosts[0]);
//arrRemHosts looks like: Array ( [0] => localhost [1] => VOCNL01S002 ) 
			$rsltLastServices = mysql_query ("SELECT * FROM nagios_services WHERE tid = '$intTid' AND host NOT IN ('".implode("', '", $arrRemHosts)."') ORDER BY host") or die(mysql_error());
			$i = 0;
			$arrLastServices = array();
			while($row = mysql_fetch_array($rsltLastServices)) {
				while(list($myVariableName,$sqlFieldName)=each($row)) {
					$arrLastServices[$i][$myVariableName] = $sqlFieldName;
				}
			}
			$i++;
			return $arrLastServices;
		}
	}
	catch (Exception $e) {
		$error = $e->getMessage();
		$script = $e->getFile();
		log_error($error, $script);
		return "An unexpected error has occured. This error has been logged and will be investigated as soon as possible.";
	}
}
?>

 

And it only returns one array with host VOCNL01S002 while it should return only rows where host is not localhost or VOCNL01S002

 

Can you help me ?

[DarkWater]

$qryRemHosts = sprintf("SELECT removed_hosts FROM usr_preferences WHERE uid IN('$s')", implode("', '", $uid));

 

Try that.  Also, I don't like what you're doing with $arrLastServices.  That loop seems pointless and inefficient.

[DjMikeS]

I'm sorry...I think I haven't explained the problem right.

 

The usr_preferences table holds one record for each user. The field removed_hosts looks like: host1;host2;host3; etc...

 

The nagios_services table holds one record for each service. The row looks like:

host: localhost, service: check_ping, last_check: unix_time, description: Service is okay, yeah!

 

So one host can have (and does have) multiple service checks. That's why there is a loop for $arrLastServices

What I want to achieve is that $arrLastServices does NOT contain the service records of the hosts that are in the usr_preferences table....