Link to home
Start Free TrialLog in
Avatar of Barry62
Barry62Flag 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/questions/28083025/Web-Service-is-giving-me-a-404-page.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.
ASKER CERTIFIED SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Barry62

ASKER

Thanks, Ray.  I'll see what I can work out.
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!
Avatar of 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

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.
Avatar of 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?
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!
Avatar of 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. :)
Thanks for the points - it's a great question, ~Ray