MySQL - create & deploy the project on MDB GO

How to deploy & host MySQL app - tutorial & free hosting

This guide will take you step by step on how to create your project and deploy using MDB CLI.


Create database - MySQL

Create database

1. Run the following command in the terminal:


              $ mdb database init
        

2. Choose mysql8

3. You will be asked to create new user. Hit Enter to accept.

4. Provide username, password, database name and description.

CLI will display your username, password, database name and connections string.

Note: Your final username and database name may differ from your inputs. MDB GO may add some characters to randomize username and database name.

5. Your database is ready to use.

Connect

PHP


<?php
$mysqli = new mysqli("mysql.db.mdbgo.com:3306","username","password","database_name");

// Check connection
if ($mysqli -> connect_errno) {
  echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
  exit();
}
?>
        

Node.js


var mysql = require('mysql');

var con = mysql.createConnection({
  host: "mysql.db.mdbgo.com:3306",
  user: "username",
  password: "password"
});

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
});
        

Connect to MySQL using PHP

Connect to your database using PHP

Check following examples where you will find 3 different ways to connect to database using PHP using:

  • MySQLi - object-oriented
  • MySQLi - procedural
  • PDO (PHP Data Objects)

Each use case consist sample code for:

  • Database connection
  • Table creation
  • Data insertion
  • Data selection (SELECT statement)
  • Table deletion

Video tutorial:


<!-- MySQLi  Object-Oriented -->

<?php
$servername = "mysql.db.mdbgo.com";
$username = "your_username";
$password = "your_password";
$dbname = "db_name";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully  <br>";


// Create Table 
$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";

if ($conn->query($sql) === TRUE) {
    echo "Table MyGuests created successfully <br>";
} else {
    echo "Error creating table:  <br>" . $conn->error . "<br>";
}


// Insert data
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully  <br>";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}




// Query data


$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while ($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"] . " - Name: " . $row["firstname"] . " " . $row["lastname"] . "<br>";
    }
} else {
    echo "0 results";
}

//Drop table
$sql = "DROP TABLE MyGuests";

if ($conn->query($sql) === TRUE) {
    echo "Table MyGuests dropped successfully <br>";
} else {
    echo "Error dropping table:  <br>" . $conn->error . "<br>";
}


$conn->close();

            

<!-- MySQLi  Object-Oriented -->

<?php
$servername = "mysql.db.mdbgo.com";
$username = "your_username";
$password = "your_password";
$dbname = "db_name";


// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error() . "<br>");
}
echo "Connected successfully <br>";

//Create table
$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";

if (mysqli_query($conn, $sql)) {
  echo "Table MyGuests created successfully <br>";
} else {
  echo "Error creating table: " . mysqli_error($conn) . "<br>";
}


// Insert data
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";

if (mysqli_query($conn, $sql)) {
  echo "New record created successfully <br>";
} else {
  echo "Error: " . $sql . "<br>" . mysqli_error($conn) . "<br>";
}

// Query data

$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
  // output data of each row
  while ($row = mysqli_fetch_assoc($result)) {
    echo "id: " . $row["id"] . " - Name: " . $row["firstname"] . " " . $row["lastname"] . "<br>";
  }
} else {
  echo "0 results <br>";
}


//Drop table
$sql = "DROP TABLE MyGuests";

if (mysqli_query($conn, $sql)) {
  echo "Table MyGuests dropped successfully <br>";
} else {
  echo "Error dropping table: " . mysqli_error($conn) . "<br>";
}

mysqli_close($conn);

            

<!-- MySQLi  Object-Oriented -->

<?php
$servername = "mysql.db.mdbgo.com";
$username = "your_username";
$password = "your_password";
$dbname = "db_name";

try {
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

  // Create table

  $sql = "CREATE TABLE MyGuests (
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  firstname VARCHAR(30) NOT NULL,
  lastname VARCHAR(30) NOT NULL,
  email VARCHAR(50),
  reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  )";

  if ($conn->query($sql)) {
    echo "Table MyGuests created successfully <br>";
  } else {
    echo "Error creating table. <br> ";
  }

  //Insert data
  $sql = "INSERT INTO MyGuests (firstname, lastname, email)
  VALUES ('John', 'Doe', 'john@example.com')";
  // use exec() because no results are returned
  $conn->exec($sql);
  echo "New record created successfully";



  //Query data

  $sql = "SELECT id, firstname, lastname FROM MyGuests";
  $result = $conn->query($sql);
  if ($result->rowCount() > 0) {
    echo "<table><tr><th>ID</th><th>Name</th></tr>";
    // output data of each row
    while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
      echo "<tr><td>" . $row["id"] . "</td><td>" . $row["firstname"] . " " . $row["lastname"] . "</td></tr>";
    }
    echo "</table>";
  } else {
    echo "0 results";
  }

  //Drop table
  $sql = "DROP TABLE MyGuests";
  if ($conn->query($sql)) {
    echo "Table MyGuests dropped successfully <br>";
  } else {
    echo "Error dropping table. <br> ";
  }
} catch (PDOException $e) {
  echo $sql . "<br>" . $e->getMessage();
}

$conn = null;