PHP - odbc_connect - Inner Join Error

I have a PHP file created out of Dreamweaver and when I run it with the following query it works fine.

$query = "select [901_UNID] as UNID, [901_Abbreviation] as Abbreviation, [901_Name] as Name from [901-GeoState]";

When I put the following query in it fails with a 500 error.

$query = "
SELECT
[901-GeoState].[901_Abbreviation] AS Abbreviation, [901-GeoState].[901_Name] AS Name, [900-GeoCountry].[900_Country] AS Country
      
FROM
[900-GeoCountry]
      
INNER JOIN
[901-GeoState] ON [900-GeoCountry].[900_UNID] = [901-GeoState].[901_CountryID]"


Not sure why this is happening. The inner join query works in the SQL Server Query Editor.

Thanks in advance
<?php 
//connect to a DSN "myDSN" 
$conn = odbc_connect('DATABASE','USER','PASSWORD'); 
 
if ($conn) 
{ 
	//the SQL statement that will query the database 
	$query = "
	SELECT
	[901-GeoState].[901_Abbreviation] AS Abbreviation, [901-GeoState].[901_Name] AS Name, [900-GeoCountry].[900_Country] AS Country 
	
	FROM
	[900-GeoCountry]
	
	INNER JOIN
	[901-GeoState] ON [900-GeoCountry].[900_UNID] = [901-GeoState].[901_CountryID]"
	
	//perform the query 
	$result=odbc_exec($conn, $query); 
 
	echo ("<?xml version=\"1.0\"?>\n\n");
	echo ("<result>\n");
 
	//fetch tha data from the database 
	while(odbc_fetch_row($result)) 
	{ 
    	echo ("<tablerow>\n");
    	for($i=1;$i<=odbc_num_fields($result);$i++) 
    	{ 
      		$tag = odbc_field_name ($result, $i );
	  		echo ("<". $tag. ">". odbc_result($result,$i). "</". $tag. ">\n");
    	} 
    	echo ("</tablerow>\n\n");
	} 
 
	echo ("</result>");
 
	//close the connection 
	odbc_close ($conn); 
} 
else echo "odbc not connected"; 
?>

Open in new window

Michael WilliamsApplication DeveloperAsked:
Who is Participating?
 
Michael WilliamsConnect With a Mentor Application DeveloperAuthor Commented:
I figured out the problem. Needed a ; at the end of the query.

[901-GeoState] ON [900-GeoCountry].[900_UNID] = [901-GeoState].[901_CountryID]"  <---- ;
0
 
Michael WilliamsApplication DeveloperAuthor Commented:
I would normally think this would error because of some sort of data type issue. This query works in SQL.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.