Solved

PHP Web Service query similar to ASP?

Posted on 2009-04-08
18
256 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
  • 10
  • 8
18 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
 

Author Comment

by:trevoray
Comment Utility
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
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:trevoray
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
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
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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
Comment Utility
It seems obvious that this is not an issue with my code, but a setting on my server. thanks for your help!
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now