PHP Web Service query similar to ASP?

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

trevorayAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ray PaseurConnect With a Mentor Commented:
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
 
Ray PaseurCommented:
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
 
trevorayAuthor Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Ray PaseurCommented:
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
 
trevorayAuthor Commented:
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
 
trevorayAuthor Commented:
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
 
Ray PaseurCommented:
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
 
trevorayAuthor Commented:
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
 
Ray PaseurCommented:
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
 
Ray PaseurCommented:
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
 
trevorayAuthor Commented:
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
 
Ray PaseurCommented:
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
 
Ray PaseurCommented:
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
 
trevorayAuthor Commented:
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
 
Ray PaseurCommented:
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
 
trevorayAuthor Commented:
I am on PHP Version 5.2.6  I emailed you the full phpinfo as I am concerned that it contains sensitive information.
0
 
trevorayAuthor Commented:
It seems obvious that this is not an issue with my code, but a setting on my server. thanks for your help!
0
 
Ray PaseurCommented:
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
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.