Service to Connect Angular 6 App to php/mysql db hosted on DreamHost

Question

I have built an Angular 6 App for a client. The client currently hosts a mysql db on Dreamhost. There is an existing DB and I need to determine how to connect my app to the existing database to query for data. I know that querying the DB directly is opening the client up for sql injection so I need to implement a service. However, I am primarily a front end developer and am lost on the backend service needed to connect to the DB.

The app is Written in Angular 6 and will be deployed to both Andriod and iOS. Please advise the best service or tutorial to implement a web service that can be consumed by the app and provide a json response. The app currently gets data from an embedded json file that is generated by a query from the db and deployed with the app. I am trying to establish "live" updates between the database and the app

I have created a php rest service that I am attempting to get installed on the Dreamhost mysql vps:

Having server admin create a folder api/ on the VPS. Inside there will be three folders, with one file in each: api/config api/objects api/guest

/api/config/database.php

<?php
        class Database{

        // specify your own database credentials
        private $host = "localhost";
        private $db_name = "**************";
        private $username = "**************";
        private $password = "**************";
        public $conn;

        // get the database connection
        public function getConnection(){

        $this->conn = null;

        try{
            $this->conn = new PDO("mysql:host=" . $this->host . ";dbname=" 
    . $this->db_name, $this->username, $this->password);
            $this->conn->exec("set names utf8");
        }catch(PDOException $exception){
            echo "Connection error: " . $exception->getMessage();
        }

        return $this->conn;
        }
    }
?>

/api/objects/guest.php

<?php
    class Guest{

        // database connection and table name
        private $conn;
        private $table_name = "guests";

        // object properties
        public $id;
        public $name;
        public $bio;
        public $image;
        public $type;

        // constructor with $db as database connection
        public function __construct($db){
            $this->conn = $db;
        }
    }
?>

/api/guest/read.php

<?php
// required header
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");

// include database and object files
include_once '../config/database.php';
include_once '../objects/category.php';

// instantiate database and category object
$database = new Database();
$db = $database->getConnection();

// initialize object
$guest = new Guest($db);

// query categorys
$stmt = $guest->read();
$num = $stmt->rowCount();

// check if more than 0 record found
if($num>0){

    // guest array
    $guest_arr=array();
    $guest_arr["records"]=array();

    // retrieve our table contents
    // fetch() is faster than fetchAll()
    // http://stackoverflow.com/questions/2770630/pdofetchall-vs-pdofetch-in-a-loop
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
        // extract row
        // this will make $row['name'] to
        // just $name only
        extract($row);

        $guest_item=array(
            "id" => $id,
            "name" => $name,
            "bio" => html_entity_decode($bio),
            "image" => $image,
            "type" => $type
        );

        array_push($guest_arr["records"], $guest_item);
    }

    // set response code - 200 OK
    http_response_code(200);

    // show guests data in json format
    echo json_encode($guest_arr);
} else {

    // set response code - 404 Not found
    http_response_code(404);

    // tell the user no guest was found
    echo json_encode(
        array("message" => "No guests found.")
    );
}

// no guests found will be here
// read guests
function read(){
    // select all query
    $query = "SELECT
    c.name as name, p.id, p.name, p.bio, p.image, p.type
    FROM
    " . $this->table_name . " p
    LEFT JOIN
        categories c
            ON p.id = c.id
    ORDER BY
    p.name DESC";

    // prepare query statement
    $stmt = $this->conn->prepare($query);

    // execute query
    $stmt->execute();

    return $stmt;        
}
?>

I removed actual username/password/dbname from code for privacy but a user account with select & index access has been created for the app/services

The current server is a VPS mysql.ServerName.org so I assume that once I have this script installed in a folder named api/ that I can put the following address in my browser and hit this "service":

I will adjust the sql as I am not sure it is going to be correct, but here is the first pass at turning the basic script into a PHP Restful service

For the first pass I really just need to query the guest table for the included informtaion.

"https://mysql.ServerName.org/api/guest/read.php"

ok, got the service installed and here is my current server response: please adivse:

Uncaught Error: Using $this when not in object context in /home/libertycon/libertycon.org/api/guest/read.php:74 Line 74: $stmt = $this->conn->prepare($query);

My PHP is rather terrible, but I can't see any problems in the read.php file so I don't know why it can't find the method that is obviously there...

Answer 1

you need to learn abit how REST api works, here's a starting point for this: codeofaninja.com/2017/02/create-simple-rest-api-in-php.html