Auto Creation of Database Tables and Insertion of Values in PHP Free Source Code

Submitted by Patrick Mvuma on Monday, August 2, 2021 - 13:25.

Language

Hello! Greetings from Malawi the Warm Heart Of Africa. This is a simple demo project I just want to share with you so that you can learn how you create databases and tables automatically when the index.php or home.php is initiated. In this web application, the database data/tables will be automatically created when the application has been initiated or browse for the first time. In that case, you will learn how to create a web application that can be easy to deploy to a server.

Creating a simple file with your database creation script as well as tables has a number of advantages such as the following:

You don't have to export/import the database every time you want to share a project folder
It is easy to make changes to the database tables during development
Your database is always safe in case the server has been uninstalled from your machine

This project you will also learn how to insert data into tables using modal forms because they are awesome and make your system standout.

Sample Code

        <?php
        $db = new mysqli("localhost","root","");
        if($db->connect_errno > 0){
             die('Unable to connect to database [' . $db->connect_error . ']');  } //this line and above lines connect to the server
         
         $db->query("CREATE DATABASE IF NOT EXISTS `Schools`");  // this line checks if the database has been created if not it create a database
         
                 mysqli_select_db($db,"Schools"); //this line select the created database
                  
                 $stable="CREATE TABLE IF NOT EXISTS Administrator (id int(11) NOT NULL auto_increment,   
                      Firstname varchar(30)NOT NULL,Sirname varchar(30)NOT NULL,
                      Username varchar(30)NOT NULL,Password varchar(30)NOT NULL,
                      Email varchar(30)NOT NULL,
                     PRIMARY KEY(id) )";
                     $db->query($stable);   //the above lines create table and its columns if not available in the server
                         
                                                                         
                         $stable1="CREATE TABLE IF NOT EXISTS Students (id int(11) NOT NULL auto_increment,
                                      Firstname varchar(300)NOT NULL, 
                                      Sirname varchar(300)NOT NULL,
                                      Gender Varchar(30)NOT NULL,
                                      DOB varchar(300)NOT NULL,                                                               
                                      Address varchar(30)NOT NULL,                                 
                                      Guardian_Name varchar(300)NOT NULL,
                                      Guardian_Phone varchar(300)NOT NULL,       
                                      PRIMARY KEY(id) )";
                             $db->query($stable1);   //the above lines create table and its columns if not available in the server
                             
                        $stable2="CREATE TABLE IF NOT EXISTS Subjects(id int(11) NOT NULL auto_increment,
                                      Subject_Name varchar(300)NOT NULL, 
                                      Subject_Code varchar(300)NOT NULL,
                                      Subject_Teacher varchar(300)NOT NULL,
                                      Subject_Hours varchar(300)NOT NULL,
                                      Subject_Grade varchar(300)NOT NULL,
                                      PRIMARY KEY(id) )";
                             $db->query($stable2);  //the above lines create table and its columns if not available in the server
                              
                 $stable3="CREATE TABLE IF NOT EXISTS Teachers (id int(11) NOT NULL auto_increment,
                                      Teacher_Title Varchar(30)NOT NULL,
                                      Firstname varchar(300)NOT NULL, 
                                      Sirname varchar(300)NOT NULL,                                  
                                      Email varchar(300)NOT NULL, 
                                      Phone Varchar(30)NOT NULL,                                 
                                      PRIMARY KEY(id) )";
                                   $db->query($stable3);   //the above lines create table and its columns if not available in the server                                               
                                                               
                         $stable4="CREATE TABLE IF NOT EXISTS School (id int(11) NOT NULL auto_increment,
                                      School_Name varchar(300)NOT NULL, 
                                      School_Email varchar(300)NOT NULL,
                                      School_Phone varchar(300)NOT NULL,                                  
                                      PRIMARY KEY(id) )";
                             $db->query($stable4);       //the above lines create table and its columns if not available in the server                               
                    
                
                                        $sql="SELECT * FROM Administrator ";  
                                        $result=mysqli_query($db,$sql);  //this line check if the table administratr is available
                                        $count=mysqli_num_rows($result);     //this line count the number of rows/entries in the table administrator              
                           if($count==0)       //this line checks if the number of rows is equal to zero which means there is no entry in the database
                             {
                                $enter="INSERT INTO Administrator (Password,Email,Firstname,Sirname,Username) VALUES('1234554321','[email protected]','Patrick','Mvuma','pamzey')";
                                      $db->query($enter);  //this line insert values into the table after line 60 confirmed that there was no entry in the table
                            }
                          
                                        $sql="SELECT * FROM School ";  
                                        $result=mysqli_query($db,$sql);  //this line check if the table school is available
                                        $count=mysqli_num_rows($result);     //this line count the number of rows/entries in the table school              
                           if($count==0)       //this line checks if the number of rows is equal to zero which means there is no entry in the database
                             {
                                $enter="INSERT INTO School (School_Name,School_Email,School_Phone) VALUES('Univesirty Of Malawi','[email protected]','265-999-563-178')";
                                      $db->query($enter);  //this line insert values into the table after line 69 confirmed that there was no entry in the table
                             }  
                       
                        $sql="SELECT * FROM Teachers ";  
                                        $result=mysqli_query($db,$sql);  //this line check if the table Teachers is available
                                        $count=mysqli_num_rows($result);     //this line count the number of rows/entries in the table Teachers              
                           if($count==0)       //this line checks if the number of rows is equal to zero which means there is no entry in the database
                             {
                                $enter="INSERT INTO Teachers (Teacher_Title,Firstname,Sirname,Phone,Email) VALUES('Mrs','Sithembile','Chimaliro','265-999-876-600','[email protected]')";
                                      $db->query($enter);  //this line insert values into the table after line 78 confirmed that there was no entry in the table
                             }
                      $sql="SELECT * FROM Subjects ";  
                                        $result=mysqli_query($db,$sql);  //this line check if the table Subjects is available
                                        $count=mysqli_num_rows($result);     //this line count the number of rows/entries in the table Subjects             
                           if($count==0)       //this line checks if the number of rows is equal to zero which means there is no entry in the database
                             {
                                $enter="INSERT INTO Subjects (Subject_Name,Subject_Code,Subject_Grade,Subject_Teacher,Subject_Hours) VALUES('Mathematics','MAT1','100%','Mr James Banda','5/week')";
                                      $db->query($enter);  //this line insert values into the table after line 86 confirmed that there was no entry in the table
                             }  
                 $sql="SELECT * FROM Students ";  
                                        $result=mysqli_query($db,$sql);  //this line check if the table Students is available
                                        $count=mysqli_num_rows($result);     //this line count the number of rows/entries in the table Students             
                           if($count==0)       //this line checks if the number of rows is equal to zero which means there is no entry in the database
                             {
                                $enter="INSERT INTO Students (Firstname,Sirname,Gender,DOB,Address,Guardian_Name,Guardian_Phone) VALUES('Maxwell','Subili','Male','18/05/1988','P.O.Box 34','Mwandida','265-888-345-908')";
                                      $db->query($enter);  //this line insert values into the table after line 94 confirmed that there was no entry in the table
                             }                                                  
        ?>

The code above is the database connection script that automatically created the database, tables, and columns if not existing yet. Below image is the result of the above script.

Web App Interface Snapshots

Index Page

Subject Table Modal Form for Insertion

How to Run

Requirements

Download and Install any local web server such as XAMPP/WAMP.
Download the provided source code zip file. (download button is located below)

Installation/Setup

Open your XAMPP/WAMP's Control Panel and start the Apache and MySQL.
Extract the downloaded source code zip file.
If you are using XAMPP, copy the extracted source code folder and paste it into the XAMPP's "htdocs" directory. And If you are using WAMP, paste it into the "www" directory.
Browse the Web App in a browser. i.e. http://localhost/Auto_Database_Creation.

DEMO

If you have any questions regarding this project or anything find me onwww.patrickmvuma.com and if you have a website that you own and you would love to know how many people visit your website per day use my web application on this address https://websitelistener.com

Enjoy :)

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.