How to connect PHP to MySQL in private subnet VPC

Hi I have two EC2 instances, one in a public subnet and one in a private subnet in my VPC.

Hi I have two EC2 instances, one in a public subnet and one in a private subnet in my VPC.

Web server (EC2) - public subnet
MySQL database (EC2) - private subnet

I am not using AWS RDS for the database, I am using EC2.

Here is my PHP code:

define ("DB_HOST", "");
define ("DB_USER", "dbuser");
define ("DB_PASS",'dbpassword');
define ("DB_NAME","dbname");
try {
    $dsn = "mysql:dbname=".DB_NAME.";host=".DB_HOST;
    $dbh = new PDO($dsn, DB_USER, DB_PASS);
    return $dbh;
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

What do I put in DB_HOST if the EC2 instance is on a private subnet?

AWS Training Online Course - Best AWS Certification - Intellipaat

AWS Training Online Course - Best AWS Certification - Intellipaat

Enroll for best AWS training course with AWS Certification online and placement support. Learn Amazon web services AWS course from certified AWS experts. Enroll Now!

Enroll for best AWS training course with AWS Certification online and placement support. Learn Amazon web services AWS course from certified AWS experts. Enroll Now!

Create Registration form with MySQL and PHP

Create Registration form with MySQL and PHP

In membership-based website registration and login page is common.User needs to create a new account and login to the website to access services and manage its account.In this tutorial, I show how you can create a signup page with MySQL and PHP.

1. Table structure

I am using users table in the tutorial example.

CREATE TABLE `users` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `fname` varchar(80) NOT NULL,
  `lname` varchar(80) NOT NULL,
  `email` varchar(80) NOT NULL,
  `password` varchar(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. Configuration

Create a new config.php file.

Completed Code

<?php
session_start();
$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */

$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
 die("Connection failed: " . mysqli_connect_error());
}

3. HTML & PHP

Create a <form method='post' action='' >.

If $error_message is not empty then display $error_message value on the screen. Similarly, if $success_message is not empty then display the $success_message value on the screen.

NOTE – Value is assigned to $error_message and $success_message variable on <form > submit according to conditions.
Add input fields for entering – first name, last name, email, password, and confirm password.

Also, add a submit button.

Completed Code

<?php 
include "config.php";
?>
<!DOCTYPE html>
<html>
  <head>
    <title>Create Registration form with MySQL and PHP</title>

    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">

    <!-- jQuery library -->
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>

    <!-- Bootstrap JS -->
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></script>

  </head>
  <body>
    <div class='container'>
      <div class='row'>

        <div class='col-md-6' >

          <form method='post' action=''>

            <h1>SignUp</h1>
            <?php 
            // Display Error message
            if(!empty($error_message)){
            ?>
            <div class="alert alert-danger">
              <strong>Error!</strong> <?= $error_message ?>
            </div>

            <?php
            }
            ?>

            <?php 
            // Display Success message
            if(!empty($success_message)){
            ?>
            <div class="alert alert-success">
              <strong>Success!</strong> <?= $success_message ?>
            </div>

            <?php
            }
            ?>

            <div class="form-group">
              <label for="fname">First Name:</label>
              <input type="text" class="form-control" name="fname" id="fname" required="required" maxlength="80">
            </div>
            <div class="form-group">
              <label for="lname">Last Name:</label>
              <input type="text" class="form-control" name="lname" id="lname" required="required" maxlength="80">
            </div>
            <div class="form-group">
              <label for="email">Email address:</label>
              <input type="email" class="form-control" name="email" id="email" required="required" maxlength="80">
            </div>
            <div class="form-group">
              <label for="password">Password:</label>
              <input type="password" class="form-control" name="password" id="password" required="required" maxlength="80">
            </div>
            <div class="form-group">
              <label for="pwd">Confirm Password:</label>
              <input type="password" class="form-control" name="confirmpassword" id="confirmpassword" onkeyup='' required="required" maxlength="80">
            </div>

            <button type="submit" name="btnsignup" class="btn btn-default">Submit</button>
          </form>
        </div>

     </div>
    </div>
  </body>
</html>

4. Form Submit

Add following code in <head> section.

On <form > submit assign $_POST values in variables.

Validate the values –

To check the input values are valid or not created a $isValid = true variable. If any validation is false then assign false to $isValid and record not inserted.

  1. First, check if all values are entered or not. If not entered then assign false to $isValid and "Please fill all fields." to $error_message.
  2. Check if entered password and confirm password are equal or not. If not equal then assign false to $isValid and "Confirm password not matching." to $error_message.
  3. Check if $email variable value has valid email or not. If not valid then assign false to $isValid and "Invalid Email-ID." to $error_message.
  4. Check if email-id already exists in users table or not. If available then assign false to $isValid and "Email-ID is already existed." to $error_message.

If $isValid has true value then insert a new record in the users table and assign "Account created successfully." to $success_message.

Completed Code

<?php 
$error_message = "";$success_message = "";

// Register user
if(isset($_POST['btnsignup'])){
   $fname = trim($_POST['fname']);
   $lname = trim($_POST['lname']);
   $email = trim($_POST['email']);
   $password = trim($_POST['password']);
   $confirmpassword = trim($_POST['confirmpassword']);

   $isValid = true;

   // Check fields are empty or not
   if($fname == '' || $lname == '' || $email == '' || $password == '' || $confirmpassword == ''){
     $isValid = false;
     $error_message = "Please fill all fields.";
   }

   // Check if confirm password matching or not
   if($isValid && ($password != $confirmpassword) ){
     $isValid = false;
     $error_message = "Confirm password not matching";
   }

   // Check if Email-ID is valid or not
   if ($isValid && !filter_var($email, FILTER_VALIDATE_EMAIL)) {
     $isValid = false;
     $error_message = "Invalid Email-ID.";
   }

   if($isValid){

     // Check if Email-ID already exists
     $stmt = $con->prepare("SELECT * FROM users WHERE email = ?");
     $stmt->bind_param("s", $email);
     $stmt->execute();
     $result = $stmt->get_result();
     $stmt->close();
     if($result->num_rows > 0){
       $isValid = false;
       $error_message = "Email-ID is already existed.";
     }

   }

   // Insert records
   if($isValid){
     $insertSQL = "INSERT INTO users(fname,lname,email,password ) values(?,?,?,?)";
     $stmt = $con->prepare($insertSQL);
     $stmt->bind_param("ssss",$fname,$lname,$email,$password);
     $stmt->execute();
     $stmt->close();

     $success_message = "Account created successfully.";
   }
}
?>

5. Demo 6. Conclusion

In this tutorial, I only cover the registration system and if you want to know how to create login page then you can view the following tutorial.

Recommended Reading

Laravel Repository Pattern Implementation

Laravel 6 Release New Features and Upgrade

Instructions to Create your first Laravel package

Upgrading Laravel To 6.0 From 5.8

Laravel Custom Casts Package

Top 12 Array Functions In PHP

Putting a Laravel App into Production

Why we use Laravel & Wink

PHP with Vue.js & MySQL: REST API CRUD Tutorial

PHP with Vue.js & MySQL: REST API CRUD Tutorial

PHP with Vue.js & MySQL: REST API CRUD Tutorial - In this tutorial, we'll build a RESTful CRUD application with PHP & MySQL in the backend and Vue.js in the frontend. We'll also be using Axios for sending Ajax request to PHP from Vue.

The Vue.js library, Axios client and Ajax technology allows you to fetch and display data in your application without the need to refresh the whole page each time.

For database we'll be using MySQL, the most popular database used by PHP developers.

Creating the MySQL Database

In your terminal, start the MySQL client using:

mysql -u root -p

Enter your password when prompted and hit Enter.

Next, create a database using the following SQL statement:

mysql> create database vuedb;

Next, create the following SQL table in your vuedb database:

mysql> use vuedb;
mysql> CREATE TABLE `contacts` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `city` varchar(100),
  `country` varchar(100),
  `job` varchar(100)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Create The PHP & MySQL CRUD App

Now, let's create a PHP and MySQL CRUD application. Open a new terminal, navigate to your working directory then create a folder for your project:

$ cd ~/demos
$ mkdir php-vuejs-crud

Next, navigate in your project's folder and add an index.php file:

$ cd php-vuejs-crud
$ touch index.php

Open the index.php file and add the following code:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>PHP| MySQL | Vue.js | Axios Example</title>
    <script src="https://cdn.jsdelivr.net/npm/vue"></script>
    <script src="https://unpkg.com/axios/dist/axios.min.js"></script>

</head>
<body>
</body>
</html>

We first include Vue.js and Axios from their CDNs.

Next, in the body of the document, add a <table> to display fetched data:

<h1>Contact Management</h1>
<div id='vueapp'>

<table border='1' width='100%' style='border-collapse: collapse;'>
   <tr>
     <th>Name</th>
     <th>Email</th>
     <th>Country</th>
     <th>City</th>
     <th>Job</th>

   </tr>

   <tr v-for='contact in contacts'>
     <td>{{ contact.name }}</td>
     <td>{{ contact.email }}</td>
     <td>{{ contact.country }}</td>
     <td>{{ contact.city }}</td>
     <td>{{ contact.job }}</td>
   </tr>
 </table>

We use the v-for directive to iterate over the contacts array and display each contact.

Next, add a <form> tag:

</br>

    <form>
      <label>Name</label>
      <input type="text" name="name" v-model="name">
</br>
      <label>Email</label>
      <input type="email" name="email" v-model="email">
      </br>
      <label>Country</label>
      <input type="text" name="country" v-model="country">
      </br>
      <label>City</label>
      <input type="text" name="city" v-model="city">
      </br>
      <label>Job</label>
      <input type="text" name="job" v-model="job">
      </br>
      <input type="button" @click="createContact()" value="Add">
    </form>

</div>

We use the v-model directive to bind the input fields to their corresponding variables in the Vue instance we'll be creating next. And we use the @click event to bind the click event of the button to the createContact() method that will be defined in the Vue instance.

Next, add a <script> tag and create a Vue app:

<script>
var app = new Vue({
  el: '#vueapp',
  data: {
      name: '',
      email: '',
      country: '',
      city: '',
      job: '',
      contacts: []
  },
  mounted: function () {
    console.log('Hello from Vue!')
    this.getContacts()
  },

  methods: {
    getContacts: function(){
    },
    createContact: function(){
    },
    resetForm: function(){
    }
  }
})    
</script>
</body>
</html>    

We declared three methods, let's implement them!

The getContacts() method gets contacts from the PHP endpoint using Axios:

    getContacts: function(){
        axios.get('api/contacts.php')
        .then(function (response) {
            console.log(response.data);
            app.contacts = response.data;

        })
        .catch(function (error) {
            console.log(error);
        });
    }

The createContact() methods creates a new contact in the MySQL database by sending a POST request with Axios and FormData:

    createContact: function(){
        console.log("Create contact!")

        let formData = new FormData();
        console.log("name:", this.name)
        formData.append('name', this.name)
        formData.append('email', this.email)
        formData.append('city', this.city)
        formData.append('country', this.country)
        formData.append('job', this.job)

        var contact = {};
        formData.forEach(function(value, key){
            contact[key] = value;
        });

        axios({
            method: 'post',
            url: 'api/contacts.php',
            data: formData,
            config: { headers: {'Content-Type': 'multipart/form-data' }}
        })
        .then(function (response) {
            //handle success
            console.log(response)
            app.contacts.push(contact)
            app.resetForm();
        })
        .catch(function (response) {
            //handle error
            console.log(response)
        });
    }

The resetForm() method resets the form:

    resetForm: function(){
        this.name = '';
        this.email = '';
        this.country = '';
        this.city = '';
        this.job = '';
    }

Create an API Endpoint

Now, let's create an endpoint that provides contacts data in a JSON format to our Vue frontend.

Create an api folder inside your project's root folder:

$ mkdir api

Navigate inside the api folder and create a contacts.php file and add the following content:

<?php
$host = "localhost"; 
$user = "root"; 
$password = "YOUR_MYSQL_DB_PASSWORD"; 
$dbname = "vuedb"; 
$id = '';

$con = mysqli_connect($host, $user, $password,$dbname);

$method = $_SERVER['REQUEST_METHOD'];
$request = explode('/', trim($_SERVER['PATH_INFO'],'/'));
//$input = json_decode(file_get_contents('php://input'),true);


if (!$con) {
  die("Connection failed: " . mysqli_connect_error());
}


switch ($method) {
    case 'GET':
      $id = $_GET['id'];
      $sql = "select * from contacts".($id?" where id=$id":''); 
      break;
    case 'POST':
      $name = $_POST["name"];
      $email = $_POST["email"];
      $country = $_POST["country"];
      $city = $_POST["city"];
      $job = $_POST["job"];

      $sql = "insert into contacts (name, email, city, country, job) values ('$name', '$email', '$city', '$country', '$job')"; 
      break;
}

// run SQL statement
$result = mysqli_query($con,$sql);

// die if SQL statement failed
if (!$result) {
  http_response_code(404);
  die(mysqli_error($con));
}

if ($method == 'GET') {
    if (!$id) echo '[';
    for ($i=0 ; $i<mysqli_num_rows($result) ; $i++) {
      echo ($i>0?',':'').json_encode(mysqli_fetch_object($result));
    }
    if (!$id) echo ']';
  } elseif ($method == 'POST') {
    echo json_encode($result);
  } else {
    echo mysqli_affected_rows($con);
  }

$con->close();

Finally, you can serve your PHP application using the following command from the root of your project:

$ php -S 127.0.0.1:8080

This is a screenshot of the application, after posting some data using the form:

For the same styling, add the following CSS:

<style>
    input {
  width: 100%;
  padding: 2px 5px;
  margin: 2px 0;
  border: 1px solid red;
  border-radius: 4px;
  box-sizing: border-box;
}

input[type=button]{
  background-color: #4CAF50;
  border: none;
  color: white;
  padding: 4px 7px;
  text-decoration: none;
  margin: 2px 1px;
  cursor: pointer;
}
th, td {
  padding: 1px;
  text-align: left;
  border-bottom: 1px solid #ddd;
  
}
tr:hover {background-color: #f5f5f5;}

</style>

Conclusion

In this tutorial, we've used PHP, MySQL, Vue.js and Axios to create a simple REST API CRUD example application.