Web Enabled Excel Spreadsheets
 
  
Excel WEBSERVICE() Function
  Web-enabled spreadsheets, like this one, can help simplify interactions with SQL databases and REST APIs.

They utilize Excel's WEBSERVICE() function to execute calculated queries and process results.

One spreadsheet can interact with data from virtually unlimited data sources.

   -  integrate Excel with open source PHP, PDO, SQL, Javascript, JSON
   -  initially modeled in Excel 2019, can use 220 of Excel's functions in calculations
   -  any cell can interact with data entered in to the spreadsheet or from a SQL or REST API query
   -  the JSON returned from a SQL or REST API query can be parsed into Excel calculations
   -  a database abstraction class can be used to simplify database communications
   -  the PDO data-access abstraction layer uses prepared statements for queries
   -  the abstraction layer can use create, update, delete in transactions
   -  calculated output can POST to other spreadsheets, forms, APIs
   -  MySQL, SQLServer, PostgreSQL, SQLlite, native PDO drivers
   -  mobile or desktop clients only require a web browser
   -  no xDBC, no subscriptions, you own the code

The examples below demonstrate web-enabled speadsheets in action and show the scripts used.
 
A Web Service API Example
  All examples retrieve user-requested data and pass results back to calculations.
  The first example uses the URL below to request currency rates from the fixer.io API.
  "https://data.fixer.io/api/latest?access_key=FIXERIOAPIKEY&base="&BASERATECODE  
 
  Select a Base Rate Currency below to trigger the API request and load the JSON result.  
 
  
  Select the target currency to parse the currency exchange rate from the JSON result.  
 
  
  
  
  Enter an amount in the base currency  below to calculate the converted amount.  
   
   
  The selected currency will be used to calculate prices for the rest of the examples.  
 
  The second example (not live) used the SmartyStreets API to verify a U.S. Postal address  
  Street Address Unit City State  
  
  [[{"Line1":"1 Microsoft Way"}],[{"LastLine:":"Redmond WA 98052-8300"}],[{"County":"King"}],[{"Latitude":"47.64352"}],[{"Longitude":"-122.11922"}],[{"TimeZone":"Pacific"}]]  
     
      
      
      
      
      
 
  Below is the smartystreets-php-sdk code modified to output JSON in a custom format.  
  <?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=utf8mb4");
header("Cache-Control: no-cache, must-revalidate");
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT");
require_once('src/ClientBuilder.php');
require_once('src/US_Street/Lookup.php');
require_once('src/StaticCredentials.php');
use SmartyStreets\PhpSdk\Exceptions\SmartyException;
use SmartyStreets\PhpSdk\StaticCredentials;
use SmartyStreets\PhpSdk\ClientBuilder;
use SmartyStreets\PhpSdk\US_Street\Lookup;
$street = $_GET['street'];
$secondary = $_GET['secondary'];
$city = $_GET['city'];
$state = $_GET['state'];
$zipcode = $_GET['zipcode'];
$lookupExample = new UsStreetSingleAddressExample();
$lookupExample->run();
class UsStreetSingleAddressExample {
    public function run() {
        $authId = 'SMARTYSTREETSAUTHORIZATIONID';
        $authToken = 'SMARTYSTREETSAUTHORIZATIONTOKEN';
        $staticCredentials = new StaticCredentials($authId, $authToken);
        $client = (new ClientBuilder($staticCredentials))
                        ->buildUsStreetApiClient();
        $lookup = new Lookup();
        $lookup->setStreet($GLOBALS['street']);
        $lookup->setSecondary($GLOBALS['secondary']);
        $lookup->setCity($GLOBALS['city']);
        $lookup->setState($GLOBALS['state']);
        $lookup->setZipcode($GLOBALS['zipcode']);
        $lookup->setMaxCandidates(3);
        $lookup->setMatchStrategy("strict");
        try {
            $client->sendLookup($lookup);
            $this->displayResults($lookup);
        }
        catch (SmartyException $ex) {
            echo($ex->getMessage());
        }
        catch (\Exception $ex) {
            echo($ex->getMessage());
        }
    }
    public function displayResults(Lookup $lookup) {
        $results = $lookup->getResult();
        if (empty($results)) {
            echo("\"[[{This address is not valid}]]\"");
            return;
        }
        $firstCandidate = $results[0];
        $Line1 = "[[{\"Line1\":\"" . $firstCandidate->getDeliveryLine1() . "\"}],";
        $Line2 = "[{\"LastLine:\":\"" . $firstCandidate->getLastLine() . "\"}],";
        $Line3 = "[{\"County\":\"" . $firstCandidate->getMetadata()->getCountyName() . "\"}],";
        $Line4 = "[{\"Latitude\":\"" . $firstCandidate->getMetadata()->getLatitude() . "\"}],";
        $Line5 = "[{\"Longitude\":\"" . $firstCandidate->getMetadata()->getLongitude() . "\"}],";
        $Line6 = "[{\"TimeZone\":\"" . $firstCandidate->getMetadata()->getTimeZone() . "\"}]]";
        $data = ($Line1.$Line2.$Line3.$Line4.$Line5.$Line6);
        echo $data;
    }
}
?> 		 
A Price Look-up Example
  This example uses a calculated URL to connect to a PHP script and run a SQL query that returns the result in JSON.  
  Changing any of the selectors below re-calculates the URL and triggers the price per carat look-up.  
  Cut Type Clarity Color Weight Price Per Carat   Extended Price    
 
    
The calculated URL with parameters     
  
  The JSON response to the WEBSERVICE() function below.       
        IFERROR(WEBSERVICE(URL),"")       
  The formula below parses and converts the JSON response.       
  VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(JSONRESPONSE,CHAR(34),""),"[{Price:",""),"}]",""))  
  The PDOModel version of the pricequery.php script that contains the query and returns the result in JSON format  
  <?php
require_once 'script/PDOModel.php';
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Cache-Control: no-cache, must-revalidate");
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT");
$series = "Diamonds";
$gemtype = "Diamond";
$cuttype = $_GET["CutType"];
$weight = $_GET["Weight"];
$quality = $_GET["Quality"];
$color = $_GET["Color"];
$pdomodel = new PDOModel();
$pdomodel->connect($servername, $username, $password, $dbname);
$records = $pdomodel->executeQuery("select Price from grid WHERE Series = ? and GemType = ? and CutType = ? and WeightLower <= ? and WeightUpper > ? and Quality = ? and Color = ?", array($series,$gemtype,$cuttype,$weight,$weight,$quality,$color));
$json=$pdomodel->arrayToJson($records);
print_r($json);
?> 		 
  The native PDO  version of the pricequery.php script that contains the query and returns the result in JSON format  
  <?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=utf8mb4");
header("Cache-Control: no-cache, must-revalidate");
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT");
$host = '127.0.0.1';
$db   = 'databasename';
$user = 'databaseusername';
$pass = 'databaseuserpassword';
$charset = 'utf8mb4';
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => TRUE,
];
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
$series = "Diamonds";
$gemtype = "Diamond";
$cuttype = $_GET["CutType"];
$weight = $_GET["Weight"];
$quality = $_GET["Quality"];
$color = $_GET["Color"];
$sql = "select Price from grid 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();
$json = json_encode($records);
echo $json;
?> 		 
An Order Form Example
  This example order form requests product information from a SQL inventory database via a PHP script.
  Each line has a calculated URL which sends query parameters, the product number in this example, to the script.
  Line 1 WEBSERVICE() function and URL. Enter a Product Number between 1 and 40 in to the light grey fields to trigger a look-up.
  WEBSERVICE("https://xlforms.net/productquery.php?productnumber="&PRODUCTNUMBER01)  
      
  Product Line 1 JSON result which gets parsed in to the first row's fields.
  
    
  Product Number   Product Name      Price On Hand   Ordered Extension   
     
     
     
     
     
     
     
     
     
     
      
  The productquery.php PDOModel version of the script that contains the query and returns the record in JSON format.
  <?php
require_once 'script/PDOModel.php';
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Cache-Control: no-cache, must-revalidate");
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT");
$productnumber = $_GET["productnumber"];
$pdomodel = new PDOModel();
$pdomodel->connect($servername, $username, $password, $dbname);
$records = $pdomodel->executeQuery("select ProductNumber, ProductName, RetailPrice, QuantityOnHand from products WHERE productnumber = ?", array($productnumber));
$json=$pdomodel->arrayToJson($records);
print_r($json);
?> 		 
  The native PDO version of the script that script that contains the query and returns the record in JSON format.
  <?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=utf8mb4");
header("Cache-Control: no-cache, must-revalidate");
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT");
$host = '127.0.0.1';
$db   = 'databasename';
$user = 'databaseuser';
$pass = 'databaseuserpassword';
$charset = 'utf8mb4';
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => TRUE,
];
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
$productnumber = $_GET["productnumber"];
$sql = "select ProductNumber, ProductName, RetailPrice, QuantityOnHand from Products WHERE productnumber = :productnumber";
$stmt = $pdo->prepare($sql);
$stmt->execute(['productnumber' => $productnumber]);
$records = $stmt->fetchAll();
$json = json_encode($records);
echo $json;
?>
A Transaction Example
  This example updates 3 database records using 1 transaction. If an individual record's update fails the transaction rolls back. 
  The first 3 products from the Order Form Example appear below. Enter new Quantity On Hand values to test transactions.
  Product Number Product Data  
 
 
 
   
  Product Number New Quantity On Hand  
   Enter values in the New Quantity On Hand fields to build the URL.
   All 3 fields need to have a numeric value between 0 and 32767.
   Non-numeric, empty or larger numbers cause the transaction to fail.
 
 
  Select Execute to run the transaction, Refresh to see updates above.
  The calculated URL
 
  The SQL error code if there is an error or Null if the transaction was successful.
 
  The PDOModel version of the script that updates QuantityOnHand values and timestamps the updated rows.
  <?php
require_once 'script/PDOModel.php';
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Cache-Control: no-cache, must-revalidate");
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT");
date_default_timezone_set('America/Los_Angeles');
$status = date('m/d/Y h:i:s a', time());
$productnumber1 = $_GET["productnumber1"];
$quantityonhand1 = $_GET["quantityonhand1"];
$productnumber2 = $_GET["productnumber2"];
$quantityonhand2 = $_GET["quantityonhand2"];
$productnumber3 = $_GET["productnumber3"];
$quantityonhand3 = $_GET["quantityonhand3"];
$pdomodel = new PDOModel();
$pdomodel->connect($servername,$username,$password,$dbname);
$pdomodel->dbTransaction = true;
$pdomodel->where("ProductNumber", $productnumber1 );
$pdomodel->update("products", array("QuantityOnHand"=> $quantityonhand1,"Status"=> $status));
$pdomodel->where("ProductNumber", $productnumber2 );
$pdomodel->update("products", array("QuantityOnHand"=> $quantityonhand2,"Status"=> $status));
$pdomodel->where("ProductNumber", $productnumber3 );
$pdomodel->update("products", array("QuantityOnHand"=> $quantityonhand3,"Status"=> $status));
$pdomodel->commitTransaction();
$json=$pdomodel->arrayToJson($pdomodel->error);
print_r($json);
?>
  The native PDO version of the script that updates QuantityOnHand values and timestamps the updated rows.
  <?php
$host = '127.0.0.1';
$db   = 'databasename';
$user = 'databaseusername';
$pass = 'databaseuserpassword';
$charset = 'utf8mb4';
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => FALSE,
];
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
date_default_timezone_set('America/Los_Angeles');
$status = date('m/d/Y h:i:s a', time());
$productnumber1 = $_GET["productnumber1"];
$quantityonhand1 = $_GET["quantityonhand1"];
$productnumber2 = $_GET["productnumber2"];
$quantityonhand2 = $_GET["quantityonhand2"];
$productnumber3 = $_GET["productnumber3"];
$quantityonhand3 = $_GET["quantityonhand3"];
try{
    $pdo->beginTransaction();
    $sql = "UPDATE Products SET QuantityOnHand = ?, Status = ? WHERE ProductNumber = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array(
            $quantityonhand1,
            $status,
            $productnumber1
        )
    );
$sql = "UPDATE Products SET QuantityOnHand = ?, Status = ? WHERE ProductNumber = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array(
            $quantityonhand2,
            $status,
            $productnumber2
        )
    );
$sql = "UPDATE Products SET QuantityOnHand = ?, Status = ? WHERE ProductNumber = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array(
            $quantityonhand3,
            $status,
            $productnumber3
        )
    );
    $pdo->commit(); 
}
catch(Exception $e){
    echo $e->getMessage();
    $pdo->rollBack();
}
?>
Report Query Examples
  Example 1 runs 4 aggregate queries, example 2 retrieves a list with calculated totals.  
  The PDOModel script that contains the first query and returns the results in a custom JSON array.  
  <?php
require_once 'script/PDOModel.php';
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Cache-Control: no-cache, must-revalidate");
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT");
$pdomodel = new PDOModel();
$pdomodel->connect($servername, $username, $password, $dbname);
$count = $pdomodel->executeQuery("SELECT COUNT(ProductNumber) AS DistinctProducts FROM Products");
$quantity = $pdomodel->executeQuery("SELECT SUM(QuantityOnHand) AS TotalInInventory FROM Products");
$value = $pdomodel->executeQuery("SELECT SUM(RetailPrice * QuantityOnHand) AS TotalRetailValue FROM Products");
$average = $pdomodel->executeQuery("SELECT AVG(RetailPrice) AS AverageRetailPrice FROM Products");
$result = array($count,$quantity,$value,$average);
$json=$pdomodel->arrayToJson($result);
print_r($json);
?> 		  
 
  
  The result   
     
  Distinct Products  Total In Inventory  Average Retail Price Total Retail Value     
     
  Product Number Product Name Total Item Retail Value     
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
  
  The PDOModel script that contains the second query and returns the result in a custom JSON array.  
  <?php
require_once 'script/PDOModel.php';
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Cache-Control: no-cache, must-revalidate");
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT");
$pdomodel = new PDOModel();
$pdomodel->connect($servername, $username, $password, $dbname);
$records = $pdomodel->executeQuery("SELECT CONCAT(ProductNumber,'=', ProductName,'---', RetailPrice * QuantityOnHand) AS Item FROM Products ORDER BY ProductName ASC");
$json=$pdomodel->arrayToJson($records);
print_r($json);
?> 		  
Complex Query Examples
  The first query requests a list of customers that ordered Product 1 that also ordered Product 2.  
  The PDOModel version of the script that contains the query and returns the result in JSON format.  
  <?php
require_once 'script/PDOModel.php';
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Cache-Control: no-cache, must-revalidate");
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT");
$productname1 = $_GET["productname1"];
$productname2 = $_GET["productname2"];
$pdomodel = new PDOModel();
$pdomodel->connect($servername, $username, $password, $dbname);
$records = $pdomodel->executeQuery("SELECT Customers.CustomerID,Customers.CustFirstName,Customers.CustLastName FROM Customers WHERE EXISTS(SELECT * FROM (Orders INNER JOIN Order_Details ON Orders.OrderNumber = Order_Details.OrderNumber) INNER JOIN Products ON Products.ProductNumber = Order_Details.ProductNumber WHERE Products.ProductName = ? AND Orders.CustomerID = Customers.CustomerID) AND EXISTS (SELECT * FROM (Orders INNER JOIN Order_Details ON Orders.OrderNumber = Order_Details.OrderNumber) INNER JOIN Products ON Products.ProductNumber = Order_Details.ProductNumber WHERE Products.ProductName = ? AND Orders.CustomerID = Customers.CustomerID)", array($productname1,$productname2));
$json=$pdomodel->arrayToJson($records);
print_r($json);
?>
  Product 1   Product 2   The number of customers found  
 
 
  The calculated URL with parameters  
 
  The result  
 
 
  The second query returns summarized product sales totals. The minimized native PDO script is below.  
     
  The result  
 
 
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
 
  This script is reduced to the minimum required to execute the query and return the result.
         
  <?php
header("Access-Control-Allow-Origin: *");
$username  = 'USERNAME';
$password  = 'PASSWORD';
$dbconn = new PDO('mysql:host=localhost;dbname=salesorders', $username, $password);
$stmt = $dbconn->prepare("SELECT Products.ProductName, sum(Order_Details.QuotedPrice * Order_Details.QuantityOrdered) AS TotalSales
FROM Products INNER JOIN Order_Details ON (Products.ProductNumber = Order_Details.ProductNumber)
GROUP BY Products.ProductName ORDER BY TotalSales DESC");
$stmt->execute();
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
$json = json_encode($data);
$dbconn = null;
echo $json;
?>
 
An Auxiliary Control Example
  Web-enabled spreadsheets can add calculation, look-up, or data interaction interfaces to data in other systems.
  The Order Form, Transaction and Query examples add missing functionality to the example inventory system below.
 
   
Summary & Contact Information
  A Microsoft Excel spreadsheet can be configured to utilize the WEBSERVICE() function to send calculated parameters to REST APIs or to PHP scripts that execute SQL queries based on the parameters and return the JSON results back in to the spreadsheet's calculations.

The spreadsheet can then be converted to JavaScript using the SpreadSheetConverter add-in, resulting in calculating web forms, like the examples above, that merge Excel's formulas and functions with PHP's connectivity and execution methods and SQL's data management capabilities.

Optionally integrating the PDOModel database abstraction class simplifies database communications, making it easier to design and build web applications that can include complex calculations, very custom math or business logic, with unlimited database or web service connections.

Creating new workgroup applications or enhancing legacy systems, web-enabled spreadsheets provide extremely cost-effective and adaptive MVC components, running on premises or cloud.

Please use this form to send questions or upload files for project estimates.
Additional Projects And Examples
  Example 1 is a web-enabled spreadsheet that calculates the value of an item of fine jewelry  
 
  
        
  Example 2 is a web-enabled SQL database showcasing various components and capabilities  
 
  
 
  Example 3 searches 1,000,000 company records and pages results ("%" or "_" are wildcards).  
  
  Enter Part of a Company Name Sort By Direction Offset   
 
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  These scripts both return the results above using a whitelist method that, used with prepared statements, protects against SQL injection.  
  <?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=utf8mb4");
header("Cache-Control: no-cache, must-revalidate");
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT");
$host = '127.0.0.1';
$db   = 'databasename';
$user = 'databaseusername';
$pass = 'databaseuserpassword';
$charset = 'utf8mb4';
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => TRUE,
];
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
$searchterm = $_GET["searchterm"];
$whitelist = ["Company","Address","City","County","ZIP","Phone1"];
$selection = array_search($_GET['orderby'], $whitelist);
$orderby = $whitelist[$selection];
$direction = $_GET['direction'] == 'DESC' ? 'DESC' : 'ASC';
$limit = 10;
$offset = $_GET["offset"];
$stmt = $pdo->prepare("SELECT Company, Address, City, County, ZIP, Phone1 FROM customers WHERE Company LIKE :searchterm ORDER BY ".$orderby." ".$direction." LIMIT :limit OFFSET :offset");
$stmt->bindValue(':searchterm', "$searchterm%");
$stmt->bindValue(':limit', (int) $limit, PDO::PARAM_INT);
$stmt->bindValue(':offset', (int) $offset, PDO::PARAM_INT);
$stmt->execute();
$data = $stmt->fetchAll();
$json = json_encode($data);
echo $json;
?> 		 
  
  <?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=utf8mb4");
header("Cache-Control: no-cache, must-revalidate");
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT");
$host = '127.0.0.1';
$db   = 'databasename';
$user = 'databaseusername';
$pass = 'databaseuserpassword';
$charset = 'utf8mb4';
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => FALSE,
];
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
$whitelist = ["Company","Address","City","County","ZIP","Phone1"];
$selection = array_search($_GET['orderby'], $whitelist);
$orderby = $whitelist[$selection];
$direction = $_GET['direction'] == 'DESC' ? 'DESC' : 'ASC';
$searchterm = $_GET["searchterm"];
$limit = 10;
$offset = $_GET["offset"];
$stmt = $pdo->prepare('SELECT Company, Address, City, County, ZIP, Phone1 FROM customers WHERE Company LIKE ? ORDER BY '.$orderby.' '.$direction.' LIMIT ? OFFSET ?');
$stmt->execute(["%$searchterm%", $limit, $offset]);
$data = $stmt->fetchAll();
$json = json_encode($data);
echo $json;
?>