Solved

PHP Web Service query similar to ASP?

Posted on 2009-04-08
18
262 Views
Last Modified: 2012-05-06
I currently use ASP to communicate with a webservice to retrieve data. This works with no problems. I now need to retrieve same information using PHP code. Can someone please show me how this code can be translated into PHP to work properly?
Function GetValue(ByVal xpath, ByVal node)
			Dim target
			Set target = node.selectSingleNode(xpath)
			If Not target Is Nothing Then
    			 GetValue = target.Text
			End If
End Function
 
 
strXML = "https://www.mysite.com/sqlws/sqlws.asmx/runsql?sqlcommand=SELECT TOP 10 a.ind_first_name, a.ind_last_name  FROM [myDatabase].[dbo].[co_individual] a WHERE a.ind_last_name = 'Hassel' "
				
			 
				
				Dim http2, document2
				Set http2 = Server.CreateObject("Msxml2.ServerXMLHTTP")
				Set document2 = Server.CreateObject("Msxml2.DOMDocument")
				document2.async = False
				document2.validateOnParse = False
				document2.resolveExternals = False
		 
				http2.Open "GET",strXML3, False
				http2.Send()
				'if the data is xml
				document2.load http2.responseBody
				document2.SetProperty "SelectionLanguage","XPath"
				
				strFirst = GetValue("//ind_first_name",document2.documentElement)

Open in new window

0
Comment
Question by:trevoray
[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
  • 10
  • 8
18 Comments
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 24107970
If you can post a sample of the XML, or give an ACTUAL URL where this can be tested, I will be glad to try to be sure this works.  It parses correctly in PHP, and may run "as-is" but there may need to be some tinkering with the XML wrappers.

Best regards, ~Ray
<?php // RAY_temp_ASPXML.php
error_reporting(E_ALL);
 
// THE ORIGINAL POST - THIS IS MY UNDERSTANDING OF ITS INTENDED FUNCTIONALITY
// THE VARIABLE strXML IS AN XML STRING RETRIEVED FROM AN EXTERNAL WEB SERVICE
// THE EXTERNAL SERVICE RUNS A QUERY THAT IS BASED ON THE URL "GET" ARGUMENT
// THE RETURN DATA IS AN XML STRING -- THIS IS SIMILAR TO A "REST" INTERFACE
// THE APPARENT QUERY IN THIS EXAMPLE IS TO FIND THE FIRST AND LAST NAMES FOR
// TEN INSTANCES OF THE LAST NAME == 'Hassel'
//
// OBVIOUSLY IT WOULD BE BEST TO SEE THE XML IN ORDER TO TEST THE PROGRAMMING!
//
/*
Function GetValue(ByVal xpath, ByVal node)
   Dim target
   Set target = node.selectSingleNode(xpath)
   If Not target Is Nothing Then
      GetValue = target.Text
   End If
End Function
 
 
strXML = "https://www.mysite.com/sqlws/sqlws.asmx/runsql?sqlcommand=SELECT
TOP 10 a.ind_first_name, a.ind_last_name
FROM [myDatabase].[dbo].[co_individual] a WHERE a.ind_last_name = 'Hassel' "
 
 
 
   Dim http2, document2
   Set http2 = Server.CreateObject("Msxml2.ServerXMLHTTP")
   Set document2 = Server.CreateObject("Msxml2.DOMDocument")
   document2.async = False
   document2.validateOnParse = False
   document2.resolveExternals = False
 
   http2.Open "GET",strXML3, False
   http2.Send()
 
   'if the data is xml
   document2.load http2.responseBody
   document2.SetProperty "SelectionLanguage","XPath"
 
   strFirst = GetValue("//ind_first_name",document2.documentElement)
*/
 
 
// ACQUIRE THE XML FROM THE WEB SERVICE
$xml = "https://www.mysite.com/sqlws/sqlws.asmx/runsql?sqlcommand=SELECT
TOP 10 a.ind_first_name, a.ind_last_name
FROM [myDatabase].[dbo].[co_individual] a WHERE a.ind_last_name = 'Hassel' ";
 
// ACTIVATE THIS LINE TO LOOK AT THE XML RESPONSE
echo "<pre>"; var_dump($xml); echo "</pre>";
 
// CONVERT THE XML TO AN OBJECT
if (!$obj = SimpleXML_Load_String($xml))
{
   die("THIS IS NOT A VALID XML FILE AND CANNOT BE MADE INTO AN OBJECT");
}
 
// ACTIVATE THIS LINE TO LOOK AT THE OBJECT
echo "<pre>"; var_dump($obj); echo "</pre>";
 
// ITERATE OVER THE OBJECT AND PRINT THE FIRST NAMES
foreach ($obj->ind_first_name as $thing)
{
// RECAST THE OOP OBJECT AS A CHARACTER STRING AND SHOW IT
   $first_name = (string)$thing;
   echo "<br/>$first_name\n";
}

Open in new window

0
 

Author Comment

by:trevoray
ID: 24108144
Thanks for the code! Unfortunately it is not working. I am getting the following error:

THIS IS NOT A VALID XML FILE AND CANNOT BE MADE INTO AN OBJECT

I cannot show you the actual URL because of the sensitive data and also the query will only work from our server. But I can show you a sample of how the returned XML looks. Below is what XML is returned when I query via my currently working ASP page.
<?xml version="1.0"?>
<parent>
	<runsql>
		<ind_first_name>Jayne</ind_first_name>
 
		<ind_last_name>Hassel</ind_last_name>
		 
	</runsql>
</parent>

Open in new window

0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 24108307
I did not get the "NOT A VALID XML FILE" message at all, and with this change I can parse the XML and find Jayne.  Note the difference on line 71.

HTH, ~Ray
<?php // RAY_temp_ASPXML.php
error_reporting(E_ALL);
 
// THE ORIGINAL POST - THIS IS MY UNDERSTANDING OF ITS INTENDED FUNCTIONALITY
// THE VARIABLE strXML IS AN XML STRING RETRIEVED FROM AN EXTERNAL WEB SERVICE
// THE EXTERNAL SERVICE RUNS A QUERY THAT IS BASED ON THE URL "GET" ARGUMENT
// THE RETURN DATA IS AN XML STRING -- THIS IS SIMILAR TO A "REST" INTERFACE
// THE APPARENT QUERY IN THIS EXAMPLE IS TO FIND THE FIRST AND LAST NAMES FOR
// TEN INSTANCES OF THE LAST NAME == 'Hassel'
//
// OBVIOUSLY IT WOULD BE BEST TO SEE THE XML IN ORDER TO TEST THE PROGRAMMING!
//
/*
Function GetValue(ByVal xpath, ByVal node)
   Dim target
   Set target = node.selectSingleNode(xpath)
   If Not target Is Nothing Then
      GetValue = target.Text
   End If
End Function
 
 
strXML = "https://www.mysite.com/sqlws/sqlws.asmx/runsql?sqlcommand=SELECT
TOP 10 a.ind_first_name, a.ind_last_name
FROM [myDatabase].[dbo].[co_individual] a WHERE a.ind_last_name = 'Hassel' "
 
 
 
   Dim http2, document2
   Set http2 = Server.CreateObject("Msxml2.ServerXMLHTTP")
   Set document2 = Server.CreateObject("Msxml2.DOMDocument")
   document2.async = False
   document2.validateOnParse = False
   document2.resolveExternals = False
 
   http2.Open "GET",strXML3, False
   http2.Send()
 
   'if the data is xml'
   document2.load http2.responseBody
   document2.SetProperty "SelectionLanguage","XPath"
 
   strFirst = GetValue("//ind_first_name",document2.documentElement)
*/
 
 
// SIMULATE THE XML FROM THE WEB SERVICE
$xml = '<?xml version="1.0"?>
<parent>
        <runsql>
                <ind_first_name>Jayne</ind_first_name>
 
                <ind_last_name>Hassel</ind_last_name>
 
        </runsql>
</parent>';
 
// ACTIVATE THIS LINE TO LOOK AT THE XML RESPONSE
echo "<pre>"; var_dump($xml); echo "</pre>";
 
// CONVERT THE XML TO AN OBJECT
if (!$obj = SimpleXML_Load_String($xml))
{
   die("THIS IS NOT A VALID XML FILE AND CANNOT BE MADE INTO AN OBJECT");
}
 
// ACTIVATE THIS LINE TO LOOK AT THE OBJECT
echo "<pre>"; var_dump($obj); echo "</pre>";
 
// ITERATE OVER THE OBJECT AND PRINT THE FIRST NAMES
foreach ($obj->runsql->ind_first_name as $thing)
{
// RECAST THE OOP OBJECT AS A CHARACTER STRING AND SHOW IT
   $first_name = (string)$thing;
   echo "<br/>$first_name\n";
}

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!

 

Author Comment

by:trevoray
ID: 24108798
ok, i see when i use your test XML code, I do not get the error message, but when using the modified code with the actual XML query, I get same error message. I think I need to see exactly what is being returned. For some reason, whatever is being returned, it says "THIS IS NOT A VALID XML FILE". Can you show me how I can modify code to write out what is actually being returned? Then we can look at it further.
0
 

Author Comment

by:trevoray
ID: 24108854
ok, i think i understand the problem. nothing is being returned. it's trying to parse my XML query as actual XML, when it should be trying to parse the XML that should be returned when submitting my query. So apparently nothing is being returned back.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 24108882
You should be able to see something (do a "view source") and look for the information produced here near line 59:

// ACTIVATE THIS LINE TO LOOK AT THE XML RESPONSE
echo "<pre>"; var_dump($xml); echo "</pre>";

Can you please post the code you are using now?  Thanks, ~Ray
0
 

Author Comment

by:trevoray
ID: 24108941
This is the code I am currently using. I did do a view source and no XML was being displayed or returned. It is taking the SELECT string and trying to parse that as XML.  
<?php
 
// ACQUIRE THE XML FROM THE WEB SERVICE
$xml = "https://www.mysite.com/sqlws/sqlws.asmx/runsql?sqlcommand=SELECT TOP 10 a.ind_first_name, a.ind_last_name FROM [myDatabase].[dbo].[co_individual] a WHERE a.ind_last_name = 'Hassel' ";
 
// ACTIVATE THIS LINE TO LOOK AT THE XML RESPONSE
echo "<pre>"; var_dump($xml); echo "</pre>";
 
echo $xml;
 
// CONVERT THE XML TO AN OBJECT
if (!$obj = SimpleXML_Load_String($xml))
{
   die("THIS IS NOT A VALID XML FILE AND CANNOT BE MADE INTO AN OBJECT");
}
 
// ACTIVATE THIS LINE TO LOOK AT THE OBJECT
echo "<pre>"; var_dump($obj); echo "</pre>";
 
// ITERATE OVER THE OBJECT AND PRINT THE FIRST NAMES
foreach ($obj->runsql->ind_first_name as $thing)
{
// RECAST THE OOP OBJECT AS A CHARACTER STRING AND SHOW IT
   $first_name = (string)$thing;
   echo "<br/>$first_name\n";
}
 
?> 

Open in new window

0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 24108965
Sorry - I'm a BOZO.  Try this instead - I omitted the "little detail" of getting the data from the remote server!

(Slaps forehead)

<?php
 
// ACQUIRE THE XML FROM THE WEB SERVICE
$xml = file_get_contents("https://www.mysite.com/sqlws/sqlws.asmx/runsql?sqlcommand=SELECT TOP 10 a.ind_first_name, a.ind_last_name FROM [myDatabase].[dbo].[co_individual] a WHERE a.ind_last_name = 'Hassel' ");
 
// ACTIVATE THIS LINE TO LOOK AT THE XML RESPONSE
echo "<pre>"; var_dump($xml); echo "</pre>";
 
echo $xml;
 
// CONVERT THE XML TO AN OBJECT
if (!$obj = SimpleXML_Load_String($xml))
{
   die("THIS IS NOT A VALID XML FILE AND CANNOT BE MADE INTO AN OBJECT");
}
 
// ACTIVATE THIS LINE TO LOOK AT THE OBJECT
echo "<pre>"; var_dump($obj); echo "</pre>";
 
// ITERATE OVER THE OBJECT AND PRINT THE FIRST NAMES
foreach ($obj->runsql->ind_first_name as $thing)
{
// RECAST THE OOP OBJECT AS A CHARACTER STRING AND SHOW IT
   $first_name = (string)$thing;
   echo "<br/>$first_name\n";
}
 
?> 

Open in new window

0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 24108973
We may need to URLENCODE the query string, but try that sample first with file_get_contents() and we can build on that going forward.  Best, ~Ray
0
 

Author Comment

by:trevoray
ID: 24109040
ok, i tried updated code and this is the returned view source:

<pre>bool(false)
</pre>THIS IS NOT A VALID XML FILE AND CANNOT BE MADE INTO AN OBJECT


is the file_get_contents a built in PHP function? Do i need to define that function anywhere?
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 24109129
Yes, that is a built-in function.  The web service returned an invalid response of some sort.  Let's try the URLENCODE.  See below.

To go any further, we will need to know more about the remote web service.  If you are still having issues with this, you can email me the true URL.  Send it to RPaseur@NationalPres dot org.
<?php
error_reporting(E_ALL);
 
// SET THE URL
$url = "https://www.mysite.com/sqlws/sqlws.asmx/runsql";
 
// ENCODE THE QUERY STRING
$qs = urlencode("sqlcommand=SELECT TOP 10 a.ind_first_name, a.ind_last_name FROM [myDatabase].[dbo].[co_individual] a WHERE a.ind_last_name = 'Hassel'");
 
// SET THE REMOTE ACCESS STRING
$rmt = $url . '?' . $qs;
 
// ACTIVATE THIS LINE TO LOOK AT THE REMOTE SERVICE CALL
echo "<pre>"; var_dump($rmt); echo "</pre>";
 
// ACQUIRE THE XML FROM THE WEB SERVICE
$xml = file_get_contents("$rmt");
 
// ACTIVATE THIS LINE TO LOOK AT THE XML RESPONSE
echo "<pre>"; var_dump($xml); echo "</pre>";
 
echo $xml;
 
// CONVERT THE XML TO AN OBJECT
if (!$obj = SimpleXML_Load_String($xml))
{
   die("THIS IS NOT A VALID XML FILE AND CANNOT BE MADE INTO AN OBJECT");
}
 
// ACTIVATE THIS LINE TO LOOK AT THE OBJECT
echo "<pre>"; var_dump($obj); echo "</pre>";
 
// ITERATE OVER THE OBJECT AND PRINT THE FIRST NAMES
foreach ($obj->runsql->ind_first_name as $thing)
{
// RECAST THE OOP OBJECT AS A CHARACTER STRING AND SHOW IT
   $first_name = (string)$thing;
   echo "<br/>$first_name\n";
}
 
?> 

Open in new window

0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 24109577
Let's try this...
<?php
error_reporting(E_ALL);
 
// SET THE URL AND GET ID
$url = "https://www.mysite.com/sqlws/sqlws.asmx/runsql?sqlcommand=";
 
// ENCODE THE QUERY STRING
$qs = urlencode("SELECT TOP 10 a.ind_first_name, a.ind_last_name FROM [myDatabase].[dbo].[co_individual] a WHERE a.ind_last_name = 'Hassel'");
 
// SET THE REMOTE ACCESS STRING
$rmt = $url . $qs;
 
// ACTIVATE THIS LINE TO LOOK AT THE REMOTE SERVICE CALL
echo "<pre>"; var_dump($rmt); echo "</pre>";
 
// ACQUIRE THE XML FROM THE WEB SERVICE
$xml = file_get_contents("$rmt");
 
// ACTIVATE THIS LINE TO LOOK AT THE XML RESPONSE
echo "<pre>"; var_dump($xml); echo "</pre>";
 
echo $xml;
 
// CONVERT THE XML TO AN OBJECT
if (!$obj = SimpleXML_Load_String($xml))
{
   die("THIS IS NOT A VALID XML FILE AND CANNOT BE MADE INTO AN OBJECT");
}
 
// ACTIVATE THIS LINE TO LOOK AT THE OBJECT
echo "<pre>"; var_dump($obj); echo "</pre>";
 
// ITERATE OVER THE OBJECT AND PRINT THE FIRST NAMES
foreach ($obj->runsql->ind_first_name as $thing)
{
// RECAST THE OOP OBJECT AS A CHARACTER STRING AND SHOW IT
   $first_name = (string)$thing;
   echo "<br/>$first_name\n";
}
 
?> 

Open in new window

0
 
LVL 110

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 24110055
This one worked for me (redacted for URL, KEY and TABLE fields)
<?php // RAY_temp_ASPXML4.php
error_reporting(E_ALL);
echo "<pre>";
 
// SET THE URL AND GET ID
$url = "https://www.mysite.com/aimsqlws/aimsqlws.asmx/runsql?sqlcommand=";
 
// ENCODE THE QUERY STRING
$qs = urlencode("SELECT TOP 1 a.ind_first_name, a.ind_last_name, b.cst_web_password, b.cst_eml_address_dn, b.cst_oldid, b.cst_key FROM [TABLE].[dbo].[co_individual] a, [TABLE].[dbo]. [co_customer] b, [TABLE].[dbo].[ev_registrant] f  WHERE a.ind_cst_key = b.cst_key AND f.reg_cst_key = b.cst_key AND f.reg_evt_key = 'KEY'");
 
// SET THE REMOTE ACCESS STRING
$rmt = $url . $qs;
 
// ACTIVATE THIS LINE TO LOOK AT THE REMOTE SERVICE CALL
echo "\nQUERY STRING: ";var_dump($rmt);
 
// ACQUIRE THE XML FROM THE WEB SERVICE
if (!$xml = file_get_contents("$rmt"))
{
   die("FALSE FROM $url");
}
 
// ACTIVATE THIS LINE TO LOOK AT THE XML RESPONSE
echo "\nRETURNED XML: ";echo htmlentities($xml); echo "\n";
 
// CONVERT THE XML TO AN OBJECT
if (!$obj = SimpleXML_Load_String($xml))
{
   die("THIS IS NOT A VALID XML FILE AND CANNOT BE MADE INTO AN OBJECT");
}
 
// ACTIVATE THIS LINE TO LOOK AT THE OBJECT
echo "\nOBJECT: ";var_dump($obj);
 
// ITERATE OVER THE OBJECT AND PRINT THE FIRST NAMES
echo "\nITERATOR OVER OBJECT DATA:\n";
foreach ($obj->runsql as $thing)
{
   var_dump($thing);
}
 
// ISOLATE ONE FIELD AND PRINT IT
echo "\n\n\n";
echo $thing->ind_first_name;
?>

Open in new window

0
 

Author Comment

by:trevoray
ID: 24110296
ok, so i used your attached code and exact same thing. so it seems  
like it must be something wrong with my server. i'm wondering if there  
is a way to tell if my installation of PHP supports the code you are  
using.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 24110307
Run this and post the somewhat lengthy output here.  It can tell us a lot.  I hope you are at PHP5!
<?php phpinfo(); >?

Open in new window

0
 

Author Comment

by:trevoray
ID: 24110474
I am on PHP Version 5.2.6  I emailed you the full phpinfo as I am concerned that it contains sensitive information.
0
 

Author Comment

by:trevoray
ID: 24112977
It seems obvious that this is not an issue with my code, but a setting on my server. thanks for your help!
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 24112985
Thanks for the points.  I am not entirely convinced it is your server - since we can type URLs and get results, it might also be something at the remote web service.  In any case, you've got the PHP model for accessing a "RESTful" web service.  Glad I could help.  ~Ray
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

This article discusses four methods for overlaying images in a container on a web page
This article discusses how to implement server side field validation and display customized error messages to the client.
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

623 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