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

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:

  1. You don't have to export/import the database every time you want to share a project folder
  2. It is easy to make changes to the database tables during development
  3. 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

  1. <?php
  2. $db = new mysqli("localhost","root","");
  3. if($db->connect_errno > 0){
  4. die('Unable to connect to database [' . $db->connect_error . ']'); } //this line and above lines connect to the server
  5.  
  6. $db->query("CREATE DATABASE IF NOT EXISTS `Schools`"); // this line checks if the database has been created if not it create a database
  7.  
  8. mysqli_select_db($db,"Schools"); //this line select the created database
  9.  
  10. $stable="CREATE TABLE IF NOT EXISTS Administrator (id int(11) NOT NULL auto_increment,
  11. Firstname varchar(30)NOT NULL,Sirname varchar(30)NOT NULL,
  12. Username varchar(30)NOT NULL,Password varchar(30)NOT NULL,
  13. Email varchar(30)NOT NULL,
  14. PRIMARY KEY(id) )";
  15. $db->query($stable); //the above lines create table and its columns if not available in the server
  16.  
  17.  
  18. $stable1="CREATE TABLE IF NOT EXISTS Students (id int(11) NOT NULL auto_increment,
  19. Firstname varchar(300)NOT NULL,
  20. Sirname varchar(300)NOT NULL,
  21. Gender Varchar(30)NOT NULL,
  22. DOB varchar(300)NOT NULL,
  23. Address varchar(30)NOT NULL,
  24. Guardian_Name varchar(300)NOT NULL,
  25. Guardian_Phone varchar(300)NOT NULL,
  26. PRIMARY KEY(id) )";
  27. $db->query($stable1); //the above lines create table and its columns if not available in the server
  28.  
  29. $stable2="CREATE TABLE IF NOT EXISTS Subjects(id int(11) NOT NULL auto_increment,
  30. Subject_Name varchar(300)NOT NULL,
  31. Subject_Code varchar(300)NOT NULL,
  32. Subject_Teacher varchar(300)NOT NULL,
  33. Subject_Hours varchar(300)NOT NULL,
  34. Subject_Grade varchar(300)NOT NULL,
  35. PRIMARY KEY(id) )";
  36. $db->query($stable2); //the above lines create table and its columns if not available in the server
  37.  
  38. $stable3="CREATE TABLE IF NOT EXISTS Teachers (id int(11) NOT NULL auto_increment,
  39. Teacher_Title Varchar(30)NOT NULL,
  40. Firstname varchar(300)NOT NULL,
  41. Sirname varchar(300)NOT NULL,
  42. Email varchar(300)NOT NULL,
  43. Phone Varchar(30)NOT NULL,
  44. PRIMARY KEY(id) )";
  45. $db->query($stable3); //the above lines create table and its columns if not available in the server
  46.  
  47. $stable4="CREATE TABLE IF NOT EXISTS School (id int(11) NOT NULL auto_increment,
  48. School_Name varchar(300)NOT NULL,
  49. School_Email varchar(300)NOT NULL,
  50. School_Phone varchar(300)NOT NULL,
  51. PRIMARY KEY(id) )";
  52. $db->query($stable4); //the above lines create table and its columns if not available in the server
  53.  
  54.  
  55. $sql="SELECT * FROM Administrator ";
  56. $result=mysqli_query($db,$sql); //this line check if the table administratr is available
  57. $count=mysqli_num_rows($result); //this line count the number of rows/entries in the table administrator
  58. if($count==0) //this line checks if the number of rows is equal to zero which means there is no entry in the database
  59. {
  60. $enter="INSERT INTO Administrator (Password,Email,Firstname,Sirname,Username) VALUES('1234554321','[email protected]','Patrick','Mvuma','pamzey')";
  61. $db->query($enter); //this line insert values into the table after line 60 confirmed that there was no entry in the table
  62. }
  63.  
  64. $sql="SELECT * FROM School ";
  65. $result=mysqli_query($db,$sql); //this line check if the table school is available
  66. $count=mysqli_num_rows($result); //this line count the number of rows/entries in the table school
  67. if($count==0) //this line checks if the number of rows is equal to zero which means there is no entry in the database
  68. {
  69. $enter="INSERT INTO School (School_Name,School_Email,School_Phone) VALUES('Univesirty Of Malawi','[email protected]','265-999-563-178')";
  70. $db->query($enter); //this line insert values into the table after line 69 confirmed that there was no entry in the table
  71. }
  72.  
  73. $sql="SELECT * FROM Teachers ";
  74. $result=mysqli_query($db,$sql); //this line check if the table Teachers is available
  75. $count=mysqli_num_rows($result); //this line count the number of rows/entries in the table Teachers
  76. if($count==0) //this line checks if the number of rows is equal to zero which means there is no entry in the database
  77. {
  78. $enter="INSERT INTO Teachers (Teacher_Title,Firstname,Sirname,Phone,Email) VALUES('Mrs','Sithembile','Chimaliro','265-999-876-600','[email protected]')";
  79. $db->query($enter); //this line insert values into the table after line 78 confirmed that there was no entry in the table
  80. }
  81. $sql="SELECT * FROM Subjects ";
  82. $result=mysqli_query($db,$sql); //this line check if the table Subjects is available
  83. $count=mysqli_num_rows($result); //this line count the number of rows/entries in the table Subjects
  84. if($count==0) //this line checks if the number of rows is equal to zero which means there is no entry in the database
  85. {
  86. $enter="INSERT INTO Subjects (Subject_Name,Subject_Code,Subject_Grade,Subject_Teacher,Subject_Hours) VALUES('Mathematics','MAT1','100%','Mr James Banda','5/week')";
  87. $db->query($enter); //this line insert values into the table after line 86 confirmed that there was no entry in the table
  88. }
  89. $sql="SELECT * FROM Students ";
  90. $result=mysqli_query($db,$sql); //this line check if the table Students is available
  91. $count=mysqli_num_rows($result); //this line count the number of rows/entries in the table Students
  92. if($count==0) //this line checks if the number of rows is equal to zero which means there is no entry in the database
  93. {
  94. $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')";
  95. $db->query($enter); //this line insert values into the table after line 94 confirmed that there was no entry in the table
  96. }
  97. ?>

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.

Database

Web App Interface Snapshots

Index Page

images

Subject Table Modal Form for Insertion

images

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
  1. Open your XAMPP/WAMP's Control Panel and start the Apache and MySQL.
  2. Extract the downloaded source code zip file.
  3. 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.
  4. 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.

Comments

Submitted byoleteacheron Sat, 07/21/2018 - 19:19

Very informative script. Makes it very easy to setup the database. How about doing same tutorial for SQLite? Thanks for sharing! Susan
Submitted byAnonymous (not verified)on Mon, 08/02/2021 - 14:34

Nice, thanks

Add new comment