Create And Use Your Own Hosted MySQL Databases

I plan on working on a project that requires data between users to be saved in a database structure and shared with others on request.

I looked at different options like Firebase. 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 probably have been more comfortable knowing the exact price. Granted, the plan would probably be cheap, I decided to host my own on my server.

Starting Point

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

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

After you configure you 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.

The basics for PHP are:

  1. Get PHP from https://www.php.net/manual/en/install.windows.iis.php
  2. Extract contents to root drive ex. C:\PHP
  3. Configure php.ini to enable what you want. In our case with databases, enable mysqli
  4. Configure IIS – handler mapping to fast-cgi for php ( reference image below )

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

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

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.

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;
}

?>

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"}]


Comments

One response to “Create And Use Your Own Hosted MySQL Databases”

  1. […] Create And Use Your Own Hosted MySQL Databases […]

Leave a Reply

Your email address will not be published. Required fields are marked *