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="chrisjosh@example.com">
</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 thesignup.phppage.method="post"refers to how the form will be processed. Forms are mostly processed usingpostmethods because it hides important form details, unlike thegetmethod that displays the details.nameThe name attribute contained in all the form elements will be used for processing each form element with its own specifiednameby 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 <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;
}
?>
$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,EMAILandPASSWORD.
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 <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;
}
}
$_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, thedb_config.phppage was included in thesignup.phppage.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, thename,emailandpasswordfields will be assigned to the following variables respectively$name,$emailand$passwordusing 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 callmember.phpthat is if the query was successful.$_dbx->close();Closes the earlier created connection.
Curriculum
This is my first tutorial of this series. Expect more to come.
Posted on Utopian.io - Rewarding Open Source Contributors
Thank you for the contribution. It has been approved.
You can contact us on Discord.
[utopian-moderator]
thanks for the approval.hope to do alote more.
BR
JAVAPOINT
Deleted comment.
hello you have issue in any of this ?
Hey @javapoint I am @utopian-io. I have just upvoted you!
Achievements
Suggestions
Get Noticed!
Community-Driven Witness!
I am the first and only Steem Community-Driven Witness. Participate on Discord. Lets GROW TOGETHER!
Up-vote this comment to grow my power and help Open Source contributions like this one. Want to chat? Join me on Discord https://discord.gg/Pc8HG9x
I like that the PHP script creates database automatically using PHP and MySQLi but about other free database software, which scripts are there to support those.