select statement syntax error

[knowram]

I am using a select statement to find some information in a big table using other info form different tables as criteria. My problem is that one of the items has signal quotes in the name. So I get a syntax error. I have tried commenting the signal quotes out in the databases where that name is pulled form but that didn't seem to work.

 

Any ideas.

 

here is what my code looks like.

 

SELECT StatisticalInterfaceIdentification.nStatisticalInterfaceIdentificationID
			FROM StatisticalInterfaceIdentification
			INNER JOIN PivotStatisticalMonitorTypeToDevice
			ON StatisticalInterfaceIdentification.nPivotStatisticalMonitorTypeToDeviceID = PivotStatisticalMonitorTypeToDevice.nPivotStatisticalMonitorTypeToDeviceID
			INNER JOIN Device
			ON PivotStatisticalMonitorTypeToDevice.nDeviceID = Device.nDeviceID
			WHERE Device.sDisplayName = '$Device_Name[$key]'
				AND Device.nDeviceTypeID = '3'
				AND PivotStatisticalMonitorTypeToDevice.nStatisticalMonitorTypeID = '1'
				AND StatisticalInterfaceIdentification.sIfDescr = '$Int'
				OR
				Device.sDisplayName = '$Device_Name[$key]'
				AND Device.nDeviceTypeID = '3'
				AND PivotStatisticalMonitorTypeToDevice.nStatisticalMonitorTypeID = '1'
				AND StatisticalInterfaceIdentification.sIfDescr = '$intadd'") or
					die ("can't select StatisticalInterface");

 

At one point $Int is equal to PIX Firewall 'outside' interface I have also tried PIX Firewall \'outside\' interface

 

:-\ any ideas

 

Thanks for the help

[pikemsu28]

PLEASE DONT DOUBLE POST!!!

 

http://www.phpfreaks.com/forums/index.php/topic,142171.0.html

[knowram]

Sorry again for the double post the pop up telling me that my first post had gotten moved was blocked.

[Illusion]

'$intadd'") 

 

 

 

take a look at it again,Where is the matching double quote.You need to  be careful while posting the code.

[knowram]

Looks like i missed some when i was copy pasting

 

$resultSIIID = mssql_query("
			SELECT StatisticalInterfaceIdentification.nStatisticalInterfaceIdentificationID
			FROM StatisticalInterfaceIdentification
			INNER JOIN PivotStatisticalMonitorTypeToDevice
			ON StatisticalInterfaceIdentification.nPivotStatisticalMonitorTypeToDeviceID = PivotStatisticalMonitorTypeToDevice.nPivotStatisticalMonitorTypeToDeviceID
			INNER JOIN Device
			ON PivotStatisticalMonitorTypeToDevice.nDeviceID = Device.nDeviceID
			WHERE Device.sDisplayName = '$Device_Name[$key]'
				AND Device.nDeviceTypeID = '3'
				AND PivotStatisticalMonitorTypeToDevice.nStatisticalMonitorTypeID = '1'
				AND StatisticalInterfaceIdentification.sIfDescr = '$Int'
				OR
				Device.sDisplayName = '$Device_Name[$key]'
				AND Device.nDeviceTypeID = '3'
				AND PivotStatisticalMonitorTypeToDevice.nStatisticalMonitorTypeID = '1'
				AND StatisticalInterfaceIdentification.sIfDescr = '$intadd'") or
					die ("can't select StatisticalInterface");

[fenway]

Post the final query strying.

[knowram]

Ok here is the whole thing. There is some extra info which is why I didn't post it all in the first place.

 

		foreach ($Print_Order as $P_key){
		$dev = explode("|", $P_key);
		foreach ($dev as $key){

		$Interfaces = explode("|",$Interface[$key]);

		unset($tempsiiid);

		foreach ($Interfaces as $Int){
			$intadd = $Int.'-802.1Q vLAN subif';

			$resultSIIID = mssql_query("
			SELECT StatisticalInterfaceIdentification.nStatisticalInterfaceIdentificationID
			FROM StatisticalInterfaceIdentification
			INNER JOIN PivotStatisticalMonitorTypeToDevice
			ON StatisticalInterfaceIdentification.nPivotStatisticalMonitorTypeToDeviceID = PivotStatisticalMonitorTypeToDevice.nPivotStatisticalMonitorTypeToDeviceID
			INNER JOIN Device
			ON PivotStatisticalMonitorTypeToDevice.nDeviceID = Device.nDeviceID
			WHERE Device.sDisplayName = '$Device_Name[$key]'
				AND Device.nDeviceTypeID = '3'
				AND PivotStatisticalMonitorTypeToDevice.nStatisticalMonitorTypeID = '1'
				AND StatisticalInterfaceIdentification.sIfDescr = '$Int'
				OR
				Device.sDisplayName = '$Device_Name[$key]'
				AND Device.nDeviceTypeID = '3'
				AND PivotStatisticalMonitorTypeToDevice.nStatisticalMonitorTypeID = '1'
				AND StatisticalInterfaceIdentification.sIfDescr = '$intadd'") or
					die ("can't select StatisticalInterface");

			$rowSIIID = mssql_fetch_array($resultSIIID, MSSQL_ASSOC);


			if (empty($tempsiiid) && $rowSIIID['nStatisticalInterfaceIdentific'] != ""){
				$tempsiiid = array('1' => $rowSIIID['nStatisticalInterfaceIdentific']);
			} else if ($rowSIIID['nStatisticalInterfaceIdentific'] != ""){
				array_push($tempsiiid, $rowSIIID['nStatisticalInterfaceIdentific']);
			}
		}
		if (empty($SIIID) && isset($tempsiiid)){
			$SIIID = array($key => $tempsiiid);
		} else if (isset($tempsiiid)) {
			$temp = array($key => $tempsiiid);
			array_push_associative($SIIID, $temp);
		}
		}


	}

This runs through an array of devices finds the interfaces for each device and then finds that nStatisticalInterfaceIdentificationID for each interface if fails when it gets to the interface PIX Firewall 'outside' interface

Hope that helps. I am not sure what you mean by "final query strying"

[fenway]

I am not sure what you mean by "final query strying"

I mean store the entire SQL query into a string variable, echo the variable, and post that here.

[Illusion]

There are ambiguities in query so try placing the parenthesis whereever possible after the where clause.

[knowram]

Maybe I am just slow or something but I am still not understanding you fenway. Are you talking about the results of the query. If so I am not sure how that would help salve the syntax error question.

 

Illusion- are you talking about single or double quotes? I thought I had all the right quotes in the right places in my query statement.

 

I am really new to query statements and this is my first one with the join feature. From what I know you encased the entire query with double quotes and the the equal to variables in single quotes.

[Illusion]

Illusion- are you talking about single or double quotes? I thought I had all the right quotes in the right places in my query statement.

 

I am really new to query statements and this is my first one with the join feature. From what I know you encased the entire query with double quotes and the the equal to variables in single quotes.

Do you think MySQL is smart enough to identify Exp1 AND Exp2 OR Exp3 AND Exp4 as (Exp1 AND Exp2) OR (Exp3 AND Exp4).

[knowram]

I guess I am not really sure. But I would have guessed given that that part seems to work fine. Again though I don't think that has anything to do with the problem that I am having. but I will try adding the parenthesis anyway.

[knowram]

Here is a simpler example of what my problem is.

 

$Int = "Pix 'outside' Interface";

$resultSIIID = mssql_query("
			SELECT StatisticalInterfaceIdentification.nStatisticalInterfaceIdentificationID
			FROM StatisticalInterfaceIdentification
			WHERE StatisticalInterfaceIdentification.sIfDescr = '$Int'") or
					die ("can't select StatisticalInterface");

			$rowSIIID = mssql_fetch_array($resultSIIID, MSSQL_ASSOC);

 

I get this error

Incorrect syntax near 'outside'. (severity 15)

the problem is that "Pix 'outside' Interface" is the value of the field that I am trying to pull and I can't change that value.

 

hope that helps

[bubblegum.anarchy]

$Int = addslashes("Pix 'outside' Interface");

[fenway]

Maybe I am just slow or something but I am still not understanding you fenway. Are you talking about the results of the query. If so I am not sure how that would help salve the syntax error question.

Take everything inside mysql_query() & assign it to a variable... then echo that variable to the screen -- I need to see what query the server is getting.