Avatar of Barry62
Barry62
Flag for United States of America asked on

Using RESTful web service in PHP to update a remote database

This is related to another question I asked: https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28083025.html

That question led me to REST using XML and PHP.  I need to know how I can update a remote database using the same technologies.
Web ComponentsWeb ServicesPHP

Avatar of undefined
Last Comment
Ray Paseur

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Ray Paseur

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Barry62

ASKER
Thanks, Ray.  I'll see what I can work out.
Ray Paseur

Some debugging hints may be helpful.  Scripts that are started via POST have no browser output and this can make them very difficult to debug.  So when you are testing, use the $_REQUEST superglobal array instead of $_POST.  This will let you visit the URL with a GET-method request, just by typing the URL into a browser address bar.  Then you can see the output of echo and var_dump() statements.  Once you're ready for prime-time, you would probably want to replace $_REQUEST with $_POST so that your script won't get activated if Google tries to search it!
Barry62

ASKER
I got something working, Ray.  Here's what I'm doing:  My client is actually calling three web services (I guess I could make it into one using functions).  First it gets a season and populates a drop-down.  Based on user selection, it calls a second service to get shows in the selected season.  Based on a selected show, it calls a third service to populate relevant data for the selected show into text boxes.  After updating the text boxes, I am posting the changes directly to an update script on the server site (I do use an authentication key so nobody can just update the database by typing the URL in.)

Here is the code.  Feel free to make any suggestions:

server_AllSeasons.php:
<?
include("dbCalls.php");
$pickSQL = "SELECT distinct season from test_season order by id";
$arrSea = mysql_query($pickSQL,$dbConn) or die ("Error performing arrSea query: ".mysql_error());
$arrSeason = mysql_fetch_row($arrSea);
$xmlstr2="";
$xmlstr = "<?xml version='1.0' encoding='UTF-8' standalone='yes'?>\n<seasons>\n";
foreach ($arrSeason as $key=>$row){
$xmlstr2 .= "<season>".$row['season']."</season>\n";
}
$xmlstr3 = "</seasons>";
echo $xmlstr.$xmlstr2.$xmlstr3;
?>

Open in new window


server_CurrentSeason.php
<?
include("dbCalls.php");
$season = @$_GET['season'];
$pickSQL = "SELECT * from season where season = '" . $season . "' order by id";
$arrSea = mysql_query($pickSQL,$dbConn) or die ("Error performing arrSea query: ".mysql_error());
$arrSeason = mysql_fetch_row($arrSea);
$xmlstr2="";
$xmlstr = "<?xml version='1.0' encoding='UTF-8' standalone='yes'?>\n<shows>\n";
foreach ($arrSeason as $key=>$row){
$xmlstr2 .= "<show>\n";
$xmlstr2 .= "<showname>".$row['showname']."</showname>\n";
$xmlstr2 .= "<credits>".$row['credits']."</credits>\n";
$xmlstr2 .= "<director>".$row['director']."</director>\n";
$xmlstr2 .= "</show>\n";
}
$xmlstr3 = "</shows>";
echo $xmlstr.$xmlstr2.$xmlstr3;
?>

Open in new window


server_selectedShow.php
<?
include("dbCalls.php");
$pickSQL = "SELECT * from test_season where season = '".$_GET['season']."' and showname = '".$_GET['show']."' order by id";
//echo $pickSQL."<br/>";
$arrSea = mysql_query($pickSQL,$dbConn) or die ("Error performing arrSea query: ".mysql_error());
$arrSeason = mysql_fetch_row($arrSea);

$xmlstr2="";


$xmlstr = "<?xml version='1.0' encoding='UTF-8' standalone='yes'?>\n<shows>\n";


foreach ($arrSeason as $key=>$row){
$xmlstr2 .= "<show>\n";
$xmlstr2 .= "<season>".$row['season']."</season>\n";
$xmlstr2 .= "<showname>".$row['showname']."</showname>\n";
$xmlstr2 .= "<credits>".$row['credits']."</credits>\n";
$xmlstr2 .= "<director>".$row['director']."</director>\n";
$xmlstr2 .= "</show>\n";
}

$xmlstr3 = "</shows>";

echo $xmlstr.$xmlstr2.$xmlstr3;

?>

Open in new window



Client page(test.php):
<?
if (!isset($_POST['subchanges'])){
$xml = file_get_contents('http://mysite/server_AllSeasons.php');
$seasobj = simpleXML_Load_String($xml);
$seasonarr = array();
foreach ($seasobj->season as $season){
  array_push($seasonarr,$season);
}

if(isset($_POST['subseason'])){
echo $_POST['season'];
$xml = file_get_contents("http://mysite/server_CurrentSeason.php?season=".$_POST['season']."");
$showobj = simpleXML_Load_String($xml);
$showarr = array();
foreach ($showobj->show as $show){
  array_push($showarr,$show->showname);
}
}

if(isset($_POST['subshow'])){
$xml = file_get_contents("http://mysite/server_selectedShow.php?season=".$_POST['season']."&show=".urlencode($_POST['show'])."");
$detailobj = simpleXML_Load_String($xml);
}

}

?>

<html>
<head>
<title></title>
</head>
<body>
<form name="seasonlist" method="post" action="test.php">
Select a Season: <select name="season">
<? for($x=0;$x<sizeof($seasonarr);$x++){?>
<option><?=$seasonarr[$x]?></option>
<? } ?>
</select>
<input type='submit' name='subseason' id='subseason'>
</form>
<? if(isset($_POST['subseason'])){?>
<form name="showlist" method="post" action="test.php">
<input type='hidden' name='season' value="<?=$_POST['season']?>">
Select a Show: <select name="show">
<? for($y=0;$y<sizeof($showarr);$y++){?>
<option><?=$showarr[$y]?></option>
<? } ?>
</select>
<input type='submit' name='subshow' id='subshow'>
</form>
<? } ?>
<? if(isset($_POST['subshow'])){?>
<form name="changedata" method="post" action="http://mysite/server_UpdateTestSeason.php">
<input type="hidden" name="Key" value="secretkey">
<input type="hidden" name="oldshow" value="<?=$_POST['show']?>">
<input type="hidden" name="oldseason" value="<?=$_POST['season']?>">
<? foreach ($detailobj->show as $showdetail){?>
Show:<input type="text" name="showname" size="40" value="<?=$showdetail->showname?>"><br/>
Credits: <input type="text" name="credits" size="40" value="<?=$showdetail->credits?>"><br/>
Director: <input type="text" name="director" size="30" value="<?=$showdetail->director?>"><br/>
<? } ?>
<input type="submit" name='subchanges' id='subchanges'>
</form>
<? } ?>
</body>
</html>

Open in new window


server_UpdateTestSeason.php
<?
include("dbCalls.php");
if(@$_POST['Key'] == "secretkey"){
//update database
$pickSQL = "update test_season set showname = '".$_POST['showname']."', director = '".$_POST['director']."',credits = '".$_POST['credits']."' where season = '".$_POST['oldseason']."' and showname = '".$_POST['oldshow']."'";
echo $pickSQL;
mysql_query($pickSQL,$dbConn);
} else{
echo "Unauthorized request.";
}
?>

Open in new window

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Ray Paseur

Couple of thoughts.  First, you want to choose a different API, not mySQL.  Choose either MySQLi or PDO.  If you use MySQL you're setting yourself up for a forced data base conversion.  This article explains what is going on and what you must do.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

Second, although I have not tested any of the code, mysql_fetch_row() may not be the right function.  I think a ..._fetch_assoc() variant would be a better choice.

Third, unless you're using PDO, you want to learn about the ..._real_escape_string() functions to prepare external data for safe use in a query.
Barry62

ASKER
OK, I'll check this out.  I am not on my test box right now, so I am not exactly sure what PHP version I'm running, but I suspect it's above 4.1.3.  

I just checked mysql_fetch_assoc() and it is deprecated as of 5.5.0, so if I want to use it, it will have to be with mysqli.

Was my method for writing to the db OK?  Would I be able to do this from, say, a .net windows application, or would I need to send XML?
Ray Paseur

I would agree with using MySQLi.  Not sure about the other part - I think you would want to experiment with it some to see what works and what doesn't.  In all cases var_dump() is your friend!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Barry62

ASKER
Wow.  Looks like I have a lot of work ahead of of me!  I need to start converting my websites.  Thanks, Ray.  I have been enlightened. :)
Ray Paseur

Thanks for the points - it's a great question, ~Ray