Author: Brian Ravn Pedersen, Data Engineer and Software Developer
Created: 2024-09-18
Files: index.php, schema.sql, Controller.php, Database.php, Model.php, README.md, LICENSE.txt
Languages used: PHP, SQL
GitHub Repository: https://github.com/brp-labs/rest-api
The REST API has been tested with Postman API Platform (desktop) against a MySQL database (MariaDB) running on a local server (Apache/XAMPP).
CREATE: Use the HTTP method POST to create a new post in the database.
Submit the body in JSON format with at least the required keys (see below):
e.g., { "username": "John Doe", "email": "john@doe.com", "entity": "Business Intelligence" }
READ: Use the HTTP method GET to read or search for a post in the database. The response is returned in JSON format.
Read a single post (use the id key):
e.g., ../index.php?id=23
Read all posts:
e.g., ../index.php
Search for posts using the q key and a specified query string:
e.g., ../index.php?q=john
The queried table fields are: username, email. The contains method is being used in SQL, ie., LIKE '%<querystring>%'
UPDATE: Use the HTTP method PUT to update a post in the database.
Submit the body in JSON format with the keys that need to be changed. Required keys must not be empty (see below). Use the mandatory id key in the body to identify the post to update.
e.g., { "id": 23, "entity": "Development Division" }
DELETE: Use the HTTP method DELETE to delete a post in the database. Use the mandatory id key to identify the post to delete.
Delete post (with id=23):
e.g., ../index.php?id=23
Keys: The keys refer to the columns in the table
users (cf. the file: schema.sql). The keys are: id, user_id, username, email, entity, entitycode. The id, however, is the primary key and is used for auto-incrementing by the database.Required Keys: The required keys are those that must be declared when creating a new post in the
users table. The required keys cannot be empty (i.e., NULL). Some content for these keys is mandatory. The required keys are: username, email.Unique Keys: The unique keys do not need to be declared when a new post is created. Their default value on table creation is NULL. However, if a unique key is used, the key must have its own content (value). Two different posts are not allowed to have the same unique key with identical content (value). In that sense, a unique key must have unique content (value). However, multiple posts can have the same unique key's content set to NULL without violating the uniqueness rule. The unique keys are:
user_id, username, email.Queried Keys: When using the search functionality, the queried keys are
username and email. The keys used for quering can be changed with some minor modifications to the SQL code in the Model Class file. If needed, go to the search function in the Model Class file and alter the $sql variable as well as the value bindings.
The database configuration is shown in the Database Class file. The file
schema.sql contains the sequence of SQL batches used to create the database and the table, including its columns and specifications.
Whenever a dataset is returned from the database as a result of reading a single post, reading all posts, or searching for one or more posts, a separate array is inserted right before the dataset array. This separate array at the very top, called
Info, gives a timestamp and the number of posts returned from the database. The timezone used is set at the top of the Controller Class and is default set to 'Europe/Copenhagen'. And, of course, the timezone should be adjusted if the application runs in a different timezone. The array containing the dataset, by the way, is called Data.
Anyone who would like to use this REST API can do so and, of course, modify the code, the database configuration, and the list of different keys as well, in order to adapt it to one's actual needs - in compliance with the content of the attached LICENSE file.