PHP Script to create Database Automatically Using PHP and MySQLi

What Will I Learn?

You will learn how to create MySQL database automatically using PHP script and MySQLi queries(Object Oriented Programming)

Requirements

WAMP/MAMP/LAMP/XAMPP or any other PHP/MySQL web stack package, text editor which may include Notepad++, atom, sublime text2 etc.
Basic knowledge on HTML, PHP and MySQL

Difficulty

Intermediate

Tutorial Contents

As a beginner when writing a web application that works with a database, we tend to create our database manually through the database frontend on your browser. This procedure could take time when trying to do this on separate machines.

This tutorial will teach you how to write a PHP script to automatically do this at the launch of your web application.

STEP 1: Create a registration form using HTML so that as the user clicks on register button from the webpage, everything happens immediately.

<!DOCTYPE html>
<html>
        <head>
              <meta charset="utf-8">
              <title>Form</title>
        </head>
        <body>
              <form class="form" action="signup.php" method="post">
                  <table>
                      <tr>
                         <td>
                               <label for="">Name:</label><input type="text" name="name" placeholder="Chris Josh">
                         </td>
                     </tr>
                     <tr>
                        <td>
                              <label for="">Email:</label><input type="email" name="email" placeholder="[email protected]">
                       </td>
                   </tr>
                   <tr>
                       <td><label for="">Password:</label><input type="password" name="pass" placeholder="**********">
                      </td>
                      <td>
                           <input type="password" name="cpass" placeholder="Confirm Entry">
                      </td>
                  </tr>
                  <tr>
                     <td>
                          <input type="submit" name="submit" value="Register">
                     </td>
                </tr>
             </table>
        </form>
    </body>
</html>

action="signup.php" refers to the page that will handle the form processing and in this case it is the signup.php page.
method="post" refers to how the form will be processed. Forms are mostly processed using post methods because it hides important form details, unlike the get method that displays the details.
name The name attribute contained in all the form elements will be used for processing each form element with its own specified name by the PHP script.
<input type="submit" name="submit" value="Register"> This is the submit button that must be clicked before the form will be processed.
It is believed that you have a basic knowledge of HTML, therefore every other HTML tags are familiar.

STEP 2: Create a php file for database connection. save as db_config.php.

<?php
//the variables above are default for Wamp and Xampp
$SERVER = 'localhost';
$USER = 'root';
$PASSWORD = '';

//_dbx is my connection variable
$_dbx = new mysqli ($SERVER,$USER,$PASSWORD);

//Checking Connection
if ($_dbx->connect_error){
 echo "Connection not detected".$_dbx->connect_error;
}

//we create the database with the following command;
$database_sql = "CREATE DATABASE IF NOT EXISTS name_of_database";
if ($_dbx->query($database_sql) === FALSE){
return true;
}

$table_sql = "CREATE TABLE IF NOT EXISTS .fisrt_table(ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(30) NOT NULL, EMAIL VARCHAR(30) NOT NULL, PASSWORD VARCHAR(10) NOT NULL, STATUS VARCHAR(5) NOT NULL)";
$table_sql = "CREATE TABLE IF NOT EXISTS .status_table(ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(30) NOT NULL, STATUS VARCHAR(5) NOT NULL)";
if ($_dbx->query($table_sql) === FALSE){
echo "Table not created: ".$_dbx->error;
}
?>

$SERVER = 'localhost'; $USER = 'root'; $PASSWORD = ''; are required variables for databse connection. The Database Servername, the database user and the database password. The Above values are the default values for XAMP and WAMP server stack.
$_dbx = new mysqli ($SERVER,$USER,$PASSWORD); This query creates a connection to the database using OOP(Object Oriented Programming).
if ($_dbx->connect_error){echo "Connection not detected".$_dbx->connect_error;} This checks if the connection that was created is successful and if it is not successful it will display an error message but if successful, it will proceed to the next available query.
$database_sql = "CREATE DATABASE IF NOT EXISTS name_of_database";if ($_dbx->query($database_sql) === FALSE){return true;} This creates a new database only if there is no existing database with the specified name by the user.
$table_sql = "CREATE TABLE IF NOT EXISTS <name_of_database>.fisrt_table(ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(30) NOT NULL, EMAIL VARCHAR(30) NOT NULL, PASSWORD VARCHAR(10) NOT NULL, STATUS VARCHAR(5) NOT NULL)";$table_sql = "CREATE TABLE IF NOT EXISTS <name_of_database>.status_table(ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(30) NOT NULL, STATUS VARCHAR(5) NOT NULL)";if ($_dbx->query($table_sql) === FALSE){echo "Table not created: ".$_dbx->error;} This creates a new database table as specified by the user with the above fields. The above query creates two tables with the following fields: ID, NAME, EMAIL and PASSWORD.

STEP 3: Now we create a session page that holds the current variable entered into the system so that the session can be closed if inactive. Save as signup.php

<?php
SESSION_START();
include "db_config.php";

if ($_SERVER['REQUEST_METHOD'] == 'POST'){
$name = $_POST['name'];
$email = $_POST['email'];
$password = md5($_POST['pass']);

$table_input = "INSERT INTO .(ID, NAME, EMAIL, PASSWORD, STATUS) VALUES (NULL, '$name','$email','$password','$status')";
if ($_dbx->query($table_input) === TRUE){
echo "Record Updated";
header("location:member.php");//this takes you to home after successful registration
}else{
 echo "Unable to record".$_dbx->error;
}
}
$_dbx->close();
?>

include "db_config.php"; This is a PHP fuction used to call in another page into a particular page, hence all the content of the called page will be in the existing page that it was called. In this case, the db_config.php page was included in the signup.php page.
if ($_SERVER['REQUEST_METHOD'] == 'POST'){$name = $_POST['name']; $email = $_POST['email']; $password = md5($_POST['pass']); Checks if the Register button has been clicked, if it has been clicked, the name, email and password fields will be assigned to the following variables respectively $name, $email and $password using those specified name attribute values.
$table_input = "INSERT INTO <name_of_database>.<name_of_table>(ID, NAME, EMAIL,PASSWORD, STATUS) VALUES (NULL, '$name','$email','$password','$status')";if ($_dbx->query($table_input) === TRUE){echo "Record Updated";header("location:member.php");//this takes you to home after successful registration}else{ echo "Unable to record".$_dbx->error;} This creates a new user information, that is it register a new user into the database if the query is successful, but if it is not successful, it will display an error message.
header("location:member.php"); Redirect to the script to a page call member.php that is if the query was successful.
$_dbx->close(); Closes the earlier created connection.