MDB GO Getting Started - PHP with PDO and MySQL API

MDB GO Getting Started - PHP with PDO and MySQL API

After reading this tutorial you will know how to create simple PHP API with PDO and MySQL database using MDB GO.


Introduction

These days in order to create an API using PHP we use frameworks such as Symfony or Laravel. However in a very simple scenarios you don't need all of this features. Instead you simply need to read, create, update or delete a few things and that's all. In this tutorial we will show you how to create such simple PHP API with MySQL database.

Technology recap

Here's a quick recap what are the main technologies used in this tutorial:

  • MySQL is an open-source relational database management system. If you want to learn more about it check out a separate tutorial.
  • PDO is a PHP extension which is an abstraction layer used for accessing databases.
  • PHP is a general-purpose scripting language created for web development. Read more about it here.

We won't dive deep into details about how to get started with these technologies using MDB GO. Go ahead and check the linked tutorials and once you learn a few things get back here.

If you have enough knowledge to get started, let's do this.

Building the API

Our example app will be a TODO list. It's a very popular use-case so let's simply get things done one at a time. We will go through the following steps:

  • Create a MySQL database named todo_app
  • Create PHP API with PDO

Let's go!

Creating MySQL database

In order to create a new database you need to run the following command:


                  $ mdb database init -db mysql8
              

Now you need to provide your user data and then the database details. Please provide the values of your choice. For example:

? Enter username thor
? Enter password Mjolnir_1
? Repeat password Mjolnir_1
? Enter database name todo_app
? Enter description Database for the TODO app

Note: the password must contain at least one uppercase letter, one lowercase letter, one number, one special symbol and have minimum length of 8.

Hit Enter and it's done.

Please notice that in the command output the username and database name slightly differs from what you provided earlier. Don't worry - it's OK. A few random characters have been added to these values to randomize them and improve security of your database credentials.

Important: Do not close your terminal window until you save your credentials somewhere. This is the only time we will show you your database password. If you won't save it you'll loose it.

The next step is to create the tasks table. In order to do that you need to visit https://phpmyadmin.mdbgo.com/ and log in using credentials displayed in your terminal. In my case the username is thor83ad51bf and password is Mjolnir_1.

After you're logged in you need to select your database (todo_app48a0ca47 in my case) and create a new table. Please select the SQL tab and run the following query:


                  CREATE TABLE IF NOT EXISTS `tasks` (
                      `id` INT(11) NOT NULL AUTO_INCREMENT,
                      `name` VARCHAR(255) NOT NULL,
                      `desc` VARCHAR(255) DEFAULT NULL,
                      PRIMARY KEY (`id`)
                  )
              

That's pretty much it. You can now see how to connect with this database from the Node.js API.

Creating PHP API with PDO

In case you don't know, PDO is an extension built for PHP. It helps managing database access. You can initialize a MDB GO starter that already has configured PDO. Simply run the following command:


                  $ mdb backend init
              

and choose Simple PHP PDO starter from the list that shows up.

After initialization just go to the newly created project directory and open the db-connect.php file. After that edit the $dsn, $user and $password values. You should make use of credentials that's been shown to you in the previous step. In my case the updated values look like this:

$dsn = 'mysql:host=mysql.db.mdbgo.com;port=3306;dbname=todo_app48a0ca47';
$user = 'thor83ad51bf';
$password = 'Mjolnir_1';

Save the file and close it.

Now let's implement CRUD operations. Here's what we are going to do:

  • Create tasks.php file responsible for retrieving tasks from the database
  • Create create.php file responsible for inserting new tasks to our table
  • Create update.php file responsible for updating a task
  • Create delete.php file responsible for deleting a task

Let's start with the first one - tasks.php. Create a new file and paste the following content:


                  <?php require_once 'db-connect.php';

                  $pdo = new DBConnect();
                  $conn = $pdo->connect();

                  if ( $conn ) {

                      $read_query = $conn->prepare( 'SELECT * FROM `tasks`' );
                      $read_query->execute();
                      $results = $read_query->fetch();

                      print_r($results);
                  }
                  ?>
              

It will select all the tasks from the database and print them as a response.

Now let's implement the rest of the operations:

  • create.php - creating a task
    
                          <?php require_once 'db-connect.php';
    
                          $pdo = new DBConnect();
                          $conn = $pdo->connect();
    
                          if ( $conn ) {
    
                              $name = $_REQUEST['name'];
                              $desc = $_REQUEST['desc'];
                              $insert_query = $conn->prepare( 'INSERT INTO `tasks` (`name`, `desc`) VALUES (?, ?)' );
                              $insert_query->execute([$name, $desc]);
    
                              echo 'OK';
                          }
                          ?>
                      
  • update.php - updating a task
    
                          <?php require_once 'db-connect.php';
    
                          $pdo = new DBConnect();
                          $conn = $pdo->connect();
    
                          if ( $conn ) {
    
                              $id = $_REQUEST['id'];
                              $name = $_REQUEST['name'];
                              $desc = $_REQUEST['desc'];
                              $update_query = $conn->prepare( 'UPDATE `tasks` SET `name` = ?, `desc` = ? WHERE id = ?' );
                              $update_query->execute([$name, $desc, $id]);
    
                              echo 'OK';
                          }
                          ?>
                      
  • delete.php - deleting task
    
                          <?php require_once 'db-connect.php';
    
                          $pdo = new DBConnect();
                          $conn = $pdo->connect();
    
                          if ( $conn ) {
    
                            $id = $_REQUEST['id'];
                            $delete_query = $conn->prepare( 'DELETE FROM `tasks` WHERE `id` = ?' );
                            $delete_query->execute([$id]);
    
                            echo 'OK';
                          }
                          ?>
                      

The project is ready to use. Now publish it using the following command:


                  $ mdb publish -p php7.4
              

After your files get uploaded you can access your app under the address shown in the output.

Note: You may want to check the tutorial where we explain in detail what can go wrong here and how to deal with it.

Alright, let's see how to "talk" with our fresh API.

Playing with PHP API - CRUD requests

Before we jump into "talking" stuff, firstly let's find out what endpoints do we have.

When you visit the / route you will see the default view. There are also four additional endpoints defined:

  • GET /tasks.php - reads the tasks table and sends it as a response
  • POST /create.php - creates a new task and appends it to the tasks table
  • POST /update.php - updates a specific task
  • POST /delete.php - deletes a specific task

And these are the ones we are going to talk to. In order to do that I recommend you install some REST client - like Postman for example - because it'll make your life way easier. Alternatively, you can utilize curl command available in Linux/MacOS terminals. However, in this tutorial we will go with Postman.

Go ahead and install it using official docs.

Talking to our API

Once you download and install Postman you can start making requests. We will cover all of the above requests.

Read tasks

In order to make a GET request you need to create a new request in Postman and do two things:

  • First, select the request method - GET
  • Then provide the request URL. In our case it's going to be: https://php-free-pdo.mdbgo.io/tasks.php

Once you hit Send you will see the response - an empty array. Let's add an item there.

Creating a task

In order to create a task we need to make a POST request. In order to do that you need to:

  • Select the request method - POST
  • Provide request URL: https://php-free-pdo.mdbgo.io/create.php
  • Click the Body tab and select x-www-form-urlencoded and provide the following content:
    
    "name" = "Do laundry",
    "desc" = "Boring but necessary..."
    

Once you hit Send you will see the OK response. Go ahead and run the GET request again to see it. Now let's try to update it.

Updating a task

Updating the task requires the following steps:

  • Select the request method - POST
  • Provide the request URL: https://php-free-pdo.mdbgo.io/update.php
  • Click the Body tab and select x-www-form-urlencoded and provide the following content:
    
    "id" = "1",
    "name" = "Do laundry",
    "desc" = "Boring but necessary... and also quite dangerous!"
    

Once you hit Send you will see the OK response. Go ahead and run the GET request again to see it. Alright, let's delete it.

Deleting a task

In order to delete a task you need to do the following:

  • Select the request method - POST
  • Provide the request URL: https://php-free-pdo.mdbgo.io/delete.php
  • Click the Body tab and select x-www-form-urlencoded and provide the following content:
    
    "id" = "1"
    

Once you hit Send you will see the OK response - it means the task has been deleted. If you run the GET request again you will see that the task is gone.