In the article for step 1, we set up the directory structure and covered the config directory. Using standard top-down approach, we are moving on to the objects directory today.
In this REST service, we have a list of wineries, their online listings and a trivia page. This means we need three object files inside of the directory – online.php, trivia.php and wineries.php. The code shown below is for online.php.
<?php
class Online{
// database connection and table name
private $conn;
private $table_name = "Online";
// object properties
public $id;
public $description;
public $url;
public $recid;
// constructor with $db as database connection
public function __construct($db){
$this->conn = $db;
}
function reviewsById($id){
// select reviews for winery
$query = "SELECT id,description,url,recid
FROM " . $this->table_name . " where id = ? and
(description = 'tripadvisor' or description = 'googlemaps'
or description = 'yelp')";
// prepare query statement
$stmt = $this->conn->prepare($query);
$county=htmlspecialchars(strip_tags($id));
$stmt->bindParam(1, $id);
// execute query
$stmt->execute();
return $stmt;
}
function onlinesById($id){
// select reviews for winery
$query = "SELECT id,description,url,recid
FROM " . $this->table_name . " where id = ?";
// prepare query statement
$stmt = $this->conn->prepare($query);
$county=htmlspecialchars(strip_tags($id));
$stmt->bindParam(1, $id);
// execute query
$stmt->execute();
return $stmt;
}
function socialById($id){
// select reviews for winery
$query = "SELECT id,description,url,recid
FROM " . $this->table_name . " where id = ? and
(description = 'twitter' or description = 'facebook'
or description = 'instagram' or description = 'youtube')";
// prepare query statement
$stmt = $this->conn->prepare($query);
$county=htmlspecialchars(strip_tags($id));
$stmt->bindParam(1, $id);
// execute query
$stmt->execute();
return $stmt;
}
function websiteById($id){
// select reviews for winery
$query = "SELECT id,description,url,recid
FROM " . $this->table_name . " where id = ? and
description = 'website'";
// prepare query statement
$stmt = $this->conn->prepare($query);
$county=htmlspecialchars(strip_tags($id));
$stmt->bindParam(1, $id);
// execute query
$stmt->execute();
return $stmt;
}
}This PHP class uses only 6 variables – two private for the database connection and table plus four public. The four public variables are same as the names of our fields in the Online table, and are assigned for return when searching with $query variable. This statement retrieves the record(s) meeting two criteria – $id as a parameter fed into the function and description provided in the statement itself. We use $stmt to prepare the query, $county variable to properly format $id, bind that id, execute the statement and return results. This is the same process across all functions. Trivia.php follows the same general logic flow but is quite a bit simpler by nature, returning question/answer/hint based on a specific trivia id being fed into the getQuestion function.
<?php
class Trivia{
// database connection and table name
private $conn;
private $table_name = "Trivia";
// object properties
public $id;
public $question;
public $answer;
public $hint;
public function __construct($db){
$this->conn = $db;
}
function getQuestion($id){
// select all counties
$query = "SELECT question,answer,hint
FROM " . $this->table_name . " where id = ?";
// prepare query statement
$stmt = $this->conn->prepare($query);
$id=htmlspecialchars(strip_tags($id));
$stmt->bindParam(1, $id);
// execute query
$stmt->execute();
return $stmt;
}
}Our final PHP object file is called wineries.php and it is much more detailed although it follows the same general approach and logic flow already laid out. You will notice in the “searchby” functions that we are using LIKE for record retrieval instead of EQUALS. Coupled with the wildcard (%) character, this will return records containing the included string in that field.
<?php
class Wineries{
// database connection and table name
private $conn;
private $table_name = "Wineries";
private $table_name2 = "Online";
// object properties
public $id;
public $name;
public $address;
public $city;
public $state;
public $zip;
public $county;
public $phone;
public $website;
public $type;
public $tastings;
public $tours;
public $region;
public $latitude;
public $longitude;
public $tripadvisor;
public $googlemaps;
public $yelp;
public $twitter;
public $facebook;
public $instagram;
public $youtube;
public $weburl;
// constructor with $db as database connection
public function __construct($db){
$this->conn = $db;
}
// read products
/* function read(){
// select all query
$query = "SELECT c.name as category_name, p.id, p.name, p.description, p.price, p.category_id, p.created
FROM " . $this->table_name . " p LEFT JOIN categories c ON p.category_id = c.id
ORDER BY p.created DESC";
// prepare query statement
$stmt = $this->conn->prepare($query);
// execute query
$stmt->execute();
return $stmt;
} */
function readCounties(){
// select all counties
$query = "SELECT distinct State, County
FROM " . $this->table_name . " ORDER BY State, County";
// prepare query statement
$stmt = $this->conn->prepare($query);
// execute query
$stmt->execute();
return $stmt;
}
function readRegions(){
// select all counties
$query = "SELECT distinct State, Region
FROM " . $this->table_name . " ORDER BY State, Region";
// prepare query statement
$stmt = $this->conn->prepare($query);
// execute query
$stmt->execute();
return $stmt;
}
function selectByCounty($county){
// select wineries in a county
$query = "SELECT a.id,a.Name,a.Address,a.City,a.State,a.Zip,a.County,a.Phone,a.Website,a.Type,a.Tastings,a.Tours,a.Region,a.Latitude,a.Longitude, (SELECT b.url FROM " . $this->table_name2 . " b where b.id = a.id and b.description = 'tripadvisor') as Tripadvisor, (SELECT c.url FROM " . $this->table_name2 . " c where c.id = a.id and c.description = 'googlemaps') as Googlemaps, (SELECT d.url FROM " . $this->table_name2 . " d where d.id = a.id and d.description = 'yelp') as Yelp, (SELECT e.url FROM " . $this->table_name2 . " e where e.id = a.id and e.description = 'twitter') as Twitter, (SELECT f.url FROM " . $this->table_name2 . " f where f.id = a.id and f.description = 'facebook') as Facebook, (SELECT g.url FROM " . $this->table_name2 . " g where g.id = a.id and g.description = 'instagram') as Instagram, (SELECT i.url FROM " . $this->table_name2 . " i where i.id = a.id and i.description = 'website') as Weburl, (SELECT h.url FROM " . $this->table_name2 . " h where h.id = a.id and h.description = 'youtube') as Youtube FROM " . $this->table_name . " a where a.County = ? ORDER BY a.Name";
// prepare query statement
$stmt = $this->conn->prepare($query);
$county=htmlspecialchars(strip_tags($county));
$stmt->bindParam(1, $county);
// execute query
$stmt->execute();
return $stmt;
}
function searchByName($name){
// select wineries in a county
$query = "SELECT a.id,a.Name,a.Address,a.City,a.State,a.Zip,a.County,a.Phone,a.Website,a.Type,a.Tastings,a.Tours,a.Region,a.Latitude,a.Longitude, (SELECT b.url FROM " . $this->table_name2 . " b where b.id = a.id and b.description = 'tripadvisor') as Tripadvisor, (SELECT c.url FROM " . $this->table_name2 . " c where c.id = a.id and c.description = 'googlemaps') as Googlemaps, (SELECT d.url FROM " . $this->table_name2 . " d where d.id = a.id and d.description = 'yelp') as Yelp, (SELECT e.url FROM " . $this->table_name2 . " e where e.id = a.id and e.description = 'twitter') as Twitter, (SELECT f.url FROM " . $this->table_name2 . " f where f.id = a.id and f.description = 'facebook') as Facebook, (SELECT g.url FROM " . $this->table_name2 . " g where g.id = a.id and g.description = 'instagram') as Instagram, (SELECT i.url FROM " . $this->table_name2 . " i where i.id = a.id and i.description = 'website') as Weburl, (SELECT h.url FROM " . $this->table_name2 . " h where h.id = a.id and h.description = 'youtube') as Youtube FROM " . $this->table_name . " a where a.Name like ? ORDER BY a.Name";
// prepare query statement
$stmt = $this->conn->prepare($query);
$name=htmlspecialchars(strip_tags($name));
$name="%{$name}%";
$stmt->bindParam(1, $name);
// execute query
$stmt->execute();
return $stmt;
}
function searchForMileage(){
// select wineries in a county
$query = "SELECT id,Name,Address,City,State,Zip,County,Phone,Website, Type, Tastings, Tours, Region, Latitude, Longitude
FROM " . $this->table_name . " ORDER BY Name";
// prepare query statement
$stmt = $this->conn->prepare($query);
//$name=htmlspecialchars(strip_tags($name));
//$name="%{$name}%";
//$stmt->bindParam(1, $name);
// execute query
$stmt->execute();
return $stmt;
}
function SearchByID($id){
// query to read single record
$query = "SELECT id,Name,Latitude,Longitude FROM " . $this->table_name . " WHERE id = ? LIMIT 0,1";
// prepare query statement
$stmt = $this->conn->prepare($query);
// bind id of product to be updated
$id=htmlspecialchars(strip_tags($id));
$stmt->bindParam(1, $id);
// execute query
$stmt->execute();
// get retrieved row
return $stmt;
}
function searchByRegion($region){
// select wineries in a county
$query = "SELECT a.id,a.Name,a.Address,a.City,a.State,a.Zip,a.County,a.Phone,a.Website,a.Type,a.Tastings,a.Tours,a.Region,a.Latitude,a.Longitude, (SELECT b.url FROM " . $this->table_name2 . " b where b.id = a.id and b.description = 'tripadvisor') as Tripadvisor, (SELECT c.url FROM " . $this->table_name2 . " c where c.id = a.id and c.description = 'googlemaps') as Googlemaps, (SELECT d.url FROM " . $this->table_name2 . " d where d.id = a.id and d.description = 'yelp') as Yelp, (SELECT e.url FROM " . $this->table_name2 . " e where e.id = a.id and e.description = 'twitter') as Twitter, (SELECT f.url FROM " . $this->table_name2 . " f where f.id = a.id and f.description = 'facebook') as Facebook, (SELECT g.url FROM " . $this->table_name2 . " g where g.id = a.id and g.description = 'instagram') as Instagram, (SELECT i.url FROM " . $this->table_name2 . " i where i.id = a.id and i.description = 'website') as Weburl, (SELECT h.url FROM " . $this->table_name2 . " h where h.id = a.id and h.description = 'youtube') as Youtube FROM " . $this->table_name . " a where a.Region = ? ORDER BY a.state, a.Name";
// prepare query statement
$stmt = $this->conn->prepare($query);
$region=htmlspecialchars(strip_tags($region));
$stmt->bindParam(1, $region);
// execute query
$stmt->execute();
return $stmt;
}
// create the item, placeholder code for future development
function create(){
$query = "INSERT INTO " . $this->table_name . " SET
name=:name, price=:price, description=:description, category_id=:category_id, created=:created";
// prepare query
$stmt = $this->conn->prepare($query);
// sanitize
$this->name=htmlspecialchars(strip_tags($this->name));
$this->price=htmlspecialchars(strip_tags($this->price));
$this->description=htmlspecialchars(strip_tags($this->description));
$this->category_id=htmlspecialchars(strip_tags($this->category_id));
$this->created=htmlspecialchars(strip_tags($this->created));
// bind values
$stmt->bindParam(":name", $this->name);
$stmt->bindParam(":price", $this->price);
$stmt->bindParam(":description", $this->description);
$stmt->bindParam(":category_id", $this->category_id);
$stmt->bindParam(":created", $this->created);
// execute query
if($stmt->execute()){
return true;
}
return false;
}
// update the item, placeholder code for future development
function update(){
// update query
$query = "UPDATE " . $this->table_name . " SET
name = :name, price = :price, description = :description, category_id = :category_id
WHERE id = :id";
// prepare query statement
$stmt = $this->conn->prepare($query);
// sanitize
$this->name=htmlspecialchars(strip_tags($this->name));
$this->price=htmlspecialchars(strip_tags($this->price));
$this->description=htmlspecialchars(strip_tags($this->description));
$this->category_id=htmlspecialchars(strip_tags($this->category_id));
$this->id=htmlspecialchars(strip_tags($this->id));
// bind new values
$stmt->bindParam(':name', $this->name);
$stmt->bindParam(':price', $this->price);
$stmt->bindParam(':description', $this->description);
$stmt->bindParam(':category_id', $this->category_id);
$stmt->bindParam(':id', $this->id);
// execute the query
if($stmt->execute()){
return true;
}
return false;
}
// delete the item
function delete(){
// delete query
$query = "DELETE FROM " . $this->table_name . " WHERE id = ?";
// prepare query
$stmt = $this->conn->prepare($query);
// sanitize
$this->id=htmlspecialchars(strip_tags($this->id));
// bind id of record to delete
$stmt->bindParam(1, $this->id);
// execute query
if($stmt->execute()){
return true;
}
return false;
}
}In step 3 of our series, we will experience the use of a shared utilities file which contains the getPages function to be used in multiple WordPress PHP Theme pages.
2 thoughts on “Building Your PHP Rest Service (step 2)”