In this post I will show you the steps to take to create remote MySQL database to use in your projects.

MySQL Database For Web Server

Planning

I plan on working on a project that requires data to be shared between users to be saved in a database.

There are different options like Firebase that will allow me to create remote MySQL databases to use in my project. This seems fairly easy and straightforward but I didn't want to create a payment plan as of now. I'm not sure how well the project will perform on release/in testing and how many calls it was going to be doing. The plan was a "pay as you go" but I would have been more comfortable knowing the exact price. From looking into reviews, the plan would probably be cheap. Instead I ultimately decided to host my own on my server.

Note

Below is an in depth post I made on how to install IIS on Windows which also includes steps to install PHP and MySQL server.

Guide For Installing WordPress on IIS

Starting Point

To start, I wanted to add a local MySQL database on my machine. I am using a Windows 10 PC.

IIS

I installed IIS onto my PC through the Windows Add/Remove Programs >> Turn Windows features on or off

Image showing the process of adding IIS in Windows through Add/Remove Programs.

PHP

After you configure your IIS server, you will need to add and configure PHP to your server.

I have installed PHP on IIS multiple times in the past. Each time it was a headache. You can follow the link below to start your process. It has good steps and they are easy to follow.

installing-php-on-windows-10-and-iis/

The basics for PHP are:

Get PHP from PHP download site
Extract contents to root drive ex. C:\PHP
Configure php.ini to enable what you want. In our case with databases, enable mysqli
Configure IIS - handler mapping to fast-cgi for php ( reference image below )

5. Configure IIS - add *.php to default documents ( reference image below)

Image of adding PHP file support for IIS.

6. Then you need to update your systems PATH variables.

MySQL

Finally, you will need some sort of MySQL server. I opted to use MySQL Workbench. You can get a free version from their site

https://www.mysql.com/products/workbench

The setup is fairly simple. The CLI tool is the best to get more comfortable with MySQL than the GUI, but that is up to you.

After you have everything installed and configured. You will be ready to actually start implementing the server side of the project.

MySQL Creation

I started by creating a simple database using the MySQL CLI tool

create database syncourlists;

Now you will need to tell MySQL that you want to access/use this database for further actions.

use syncourlists;

Next you will need to create a table inside the database

create table users (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, username VARCHAR(255) NOT NULL);

Next we will add an entry into the table to make sure it works.

insert into users (username) values ("Thomas");

Ok, now the MySQL database and table is setup, we can move onto the PHP side.

PHP Code

Now we will create a few simple PHP scripts to add to your localhost website.

The following files will need to be added to the root of your website. For IIS it is defaulted to C:\inetpub\wwwroot\.

Add a folder for your php includes. The directory should result as:

C:\inetpub\wwwroot\include\

Inside the include directory, we will create our first php file. This file will hold the function to connect to your MySQL server and database you want to use.

conn.php

We will name the file conn.php. Then add the following to it.

<?php

function openConnection(){
    $con = new mysqli("localhost", "root", "your_password", "syncourlists");
    return $con;
}

?>

index.php

Now we will go back to the root directory and add the file index.php. Then add the following

<?php

include $_SERVER['DOCUMENT_ROOT'].'/include/conn.php';

$con = openConnection();

if($con){
    $get_all_users = "SELECT * FROM users";
    $query = mysqli_query($con, $get_all_users);
    $num_rows = mysqli_num_rows($query);
    if($num_rows > 0){
        $responses = array();
        while($rows=mysqli_fetch_assoc($query))
        {
            $id=$rows['id'];
            $username=$rows['username'];
            $arr = array(					
                    'id' => $id,
                    'username' => $username
            );
            $responses[] = $arr;
        }			
        echo json_encode($responses);
    } else {
        echo json_encode("Error");
    }

    mysqli_close($con);
}
?>

Finished. You should be able to test it by going to your browser and opening:

http://localhost

If everything worked, you should see the JSON response:

[{"1":"Thomas"}]

Hope this was useful.