Extending Acrobat Forms With PHP to Create SQL or REST API Data Cients

It recently came to my attention that Adobe Acrobat forms can be used as extremely versatile client interfaces for SQL databases or REST APIs with just a little bit of PHP scripting, primarily because Acrobat forms can POST variables to PHP scripts that execute queries on SQL databases or REST APIs based on the variables and then format and return query results to designated form fields in very near real-time.

For example, this PHP script can look-up an exchange rate for a specified currency from a currency exchange API and return it to an Acrobat form field named "Result"...

<?php
header("Content-type: application/vnd.fdf");
$version = isset($_SERVER['HTTP_ACROBAT_VERSION']) ? $_SERVER['HTTP_ACROBAT_VERSION'] : '';
if (($version === false) or ($version == '')) {header("Location: https://yourserver.com/error.php"); die();}
$endpoint = 'convert';
$access_key = 'yourfixerioaccesskey';
date_default_timezone_set('America/Los_Angeles');
$StatMsg = date('d-m-y h:i:s');
$from = $_POST["From"];
$to = $_POST["To"];
$amount = $_POST["OneUSDollar"];
$ch = curl_init('https://data.fixer.io/api/'.$endpoint.'?access_key='.$access_key.'&from='.$from.'&to='.$to.'&amount='.$amount.'');
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$json = curl_exec($ch);
curl_close($ch);
$conversionResult = json_decode($json, true);
$rate = $conversionResult ['info']['rate'];
?>

%FDF-1.2
1 0 obj
<</FDF
<?php
echo "<</Fields[<</T(Status)/V($StatMsg)>> <</T(Result)/V($rate)>>]>>";
?>
>>
endobj
trailer << /Root 1 0 R >>
%%EOF

This PHP script can look up the per carat price of a diamond from a SQL database, POSTing 4 query parameters to a SQL query that inserts the parameters into a prepared statement (to protect agains SQL injection), executes the query, and returns the price to the form field named "PerCaratPrice".

<?php
header("Content-type: application/vnd.fdf");
$version = isset($_SERVER['HTTP_ACROBAT_VERSION']) ? $_SERVER['HTTP_ACROBAT_VERSION'] : '';
if (($version === false) or ($version == '')) {header("Location: https://yourserver.com/error.php"); die();}
$host = 'localhost';
$db = 'pricelookupdatabase';
$user = 'pricelookupdatabaseadmin';
$pass = 'pricelookupdatabaseadminkey';
$charset = 'utf8mb4';
$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,];
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$pdo = new PDO($dsn, $user, $pass, $options);
date_default_timezone_set('America/Los_Angeles');
$StatMsg = date('d-m-y h:i:s');
$series = "Diamonds";
$gemtype = "Diamond";
$cuttype = $_POST["CutType"];
$weight = $_POST["Weight"];
$quality = $_POST["Quality"];
$color = $_POST["Color"];
$sql = "select Price from PriceGrid WHERE Series = :series and GemType = :gemtype and CutType = :cuttype and WeightLower <= :weight and WeightUpper > :weight and Quality = :quality and Color = :color";
$stmt = $pdo->prepare($sql);
$stmt->execute(['series' => $series, 'gemtype' => $gemtype, 'cuttype' => $cuttype, 'weight' => $weight, 'quality' => $quality, 'color' => $color]);
$records = $stmt->fetchAll(PDO::FETCH_ASSOC);
$price = $records[0]['Price'];
?>

%FDF-1.2
1 0 obj
<</FDF
<?php
// echo "<</Fields[<</T(Status)/V($StatMsg)>> <</T(GemWeight)/V($weight)>> <</T(CaretPrice)/V($price)>>]>>";
echo "<</Fields[<</T(Status)/V($StatMsg)>> <</T(PerCaratPrice)/V($price)>>]>>";
?>
>>
endobj
trailer << /Root 1 0 R >>
%%EOF