| |
Excel API clients are most efficient working with
aggregate query results but returned record sets can optionally be
paged.
|
|
| |
This example searches 1,000,000 company records and pages
results ("%" or "_" are wildcards).
|
|
| |
|
| |
Enter Part of a Company Name
|
Sort By
|
Direction
|
Offset
|
|
|
| |
|
|
|
|
|
|
| |
This is the calculated URL that is sent to the PHP
script.
|
|
| |
|
|
| |
|
| |
|
|
| |
|
|
|
|
|
|
|
| |
Delimiter1
|
Delimiter2
|
Delimiter3
|
Delimiter4
|
Delimiter5
|
Delimiter6
|
|
| |
{"Company":"
|
","Address":"
|
","City":"
|
","County":"
|
","ZIP":"
|
","Phone1":"
|
|
| |
|
| |
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
| |
|
| |
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;
?>
|
|
| |
|