MDB GO Getting Started - PHP with PDO and MySQL API

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.

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.

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 don't save it you'll lose 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
  • create.php

          
              
                        <?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
  • update.php

          
              
                        <?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
  • delete.php

          
              
                        <?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.