Solved

PHP Web Service query similar to ASP?

Posted on 2009-04-08
18
257 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
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 108

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
 

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 108

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 108

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 108

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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 108

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 108

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 108

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 108

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 108

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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 …

920 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

16 Experts available now in Live!

Get 1:1 Help Now