[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 497
  • Last Modified:

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

0
Michael Williams
Asked:
Michael Williams
  • 2
1 Solution
 
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
 
Michael WilliamsApplication 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

Featured Post

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!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now