Tutorials and Tech Writeups

How to Insert Data Into MySQL Using PHP

This article teaches the basic steps on how to create and insert data into your MYSQL DATABASE..

following these steps make's it easier and quicker for you...

Open a new php file.
Start your server.
Create the html form.
Create a Database.
Create a connection to your Database file.
Connect your form to your database
Insert your Data to your form and submit

OPEN A NEW PHP FILE

Open your visual studio code or notepad++, create a new php file e.g form.php

START YOUR SERVER

You must have installed either WAMPP, XAMPP, LAMP et. After the installation, you start any of the server that you are provided with.Example "localhost".

CREATE THE HTML < FORM>

The html <form> element are use to input users input..The code below shows you an example of an html form.

<!DOCTYPE html>

<html>

<head>

<title>My Form</title>

</head>

<body>

<form>

<fieldset>

<label>firstname<label/>

<input type="text" name="first_name" placeholder="first_name" ><br>

<label>lastname<label/>

<input type="text" name="last_name" placeholder="last_name"><br>

<label>gender<label/>

<input type="text" name="gender" placeholder="gender"><br>

<label>phone_num<label/>

<input type="text" name="phone_num" placeholder="phone no"><br>

<label>email<label/>

<input type="text" name="email" placeholder="email"><br>

<input type="submit" value="submit" name="submit">

</fieldset>

</form>

</body>

</html>

CREATE A DATABASE

A database consist of one or more tables.A database can be created by writing a MYSQL code.The following code shows you how to create a database which can be use to store data..

<?php

$servername = "localhost";

$username = "username";

$password = "password";

$dbname = "myDB";

// Create connection

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

// Check connection

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

// sql to 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),

)";

if ($conn->query($sql) === TRUE) {

echo "Table MyGuests created successfully";

} else {

echo "Error creating table: " . $conn->error;

}

$conn->close();

NOTE;

AUTO INCREMENT - automatically increases the value by 1 each time a new record is added.
PRIMARY KEYS - the primary key of a relational table uniquely identifies each record in the table.

A database can also be created by typing the following parameters into your browser."localhost/phpmyadmin".phpMyAdmin is a free and open source administration tool for MySQL.

After loading the web page it shows you the picture above ↑.

After opening the webpage You click on "NEW" to create a new database.You name your database and give it a collation of your choice, as shown in the picture below.

CREATE A TABLE

When your database has been created, you will then have to create your table.As we all know that every tables contain rows and columns, as many as possible ,and every table created must also have an" id " mainly for identification of data stored in the table and other valuable information.eg "FIRST_ NAME","LAST_NAME"etc as shown in the image below.Note; your Id must be your "PRIMARY KEY" and must be set to "AUTO_INCREMENT, also avoid white space while naming :correct:"last_name" :wrong:"last name"

CREATE A CONNECTION TO YOUR DATABASE FILE

To create a connection to your database file, you would have to write a php script, and the script will be written inside your php file.The following code shows you how to connect your database file.

<?php

const DB_NAME="intro_php_mysql";//orany prefered name

const DB_USER="root";

const DB_PASS="";//input if u have set your password

const DB_HOST="localhost";//your host is mainly localhost

$connection=mysqli_connect(DB_HOST,DB_USER,DB_PASS,DB_NAME);

if(!$connection){

die("Database connection failed".mysqli_connect_error() ." ( ".mysqli_connect_errno() . ")");

}else{

echo "Connection Successful!!";

}

?>

CONNECTING YOUR FORM TO YOUR DATABASE

Your form must be connected to your database, soo that any parameters that are inserted into the form will be saved successfully to your database.the code belo shows how to connect your form to your database.

<?php

require_once("database.php");//connection to your database file

if(!isset($_POST['submit'])){

echo "you did not submit the form";

} else {

if (trim($_POST['first_name']) == "" || trim($_POST['last_name'])== "" || trim($_POST['gender']) == "" ){

echo " first name cannot be blank";

} else{

if (is_int(($_POST['first_name']))){

echo "first name cant be a number format";

}else {

$first_name = $_POST["first_name"];

$last_name=$_POST["last_name"];

$gender=$_POST["gender"];

$phone_num = $_POST["phone_num"];

$email=$_POST["email"];

$sql = " INSERT INTO assignment (";

$sql .= "first_name, last_name, phone_num, email";

$sql .= ")VALUES (";

$sql .="'{$first_name}', '{$last_name}', '{$phone_num}', '{$email}'";

$sql .= ")";

$result_set = mysqli_query( $connection , $sql);

if( mysqli_affected_rows($connection)> 0){

echo " Data was saved successfuly sir!!!";

// redirection

// header("Location: twitter.php");

//exit;

} else{

echo "data not saved" . mysqli_error($connection);

}

?>

<form action="form.php" method="post">

<fieldset>

<label>firstname<label/>

<input type="text" name="first_name" placeholder="first_name" />

<label>lastname<label/>

<input type="text" name="last_name" placeholder="last_name"/>

<label>gender<label/>

<input type="text" name="gender" placeholder="gender"/>

<label>phone_num<label/>

<input type="text" name="phone_num" placeholder="phone no"/>

<label>email<label/>

<input type="text" name="email" placeholder="email"/>

<input type="submit" value="submit" name="submit"/>

</fieldset>

</form>

After all these proccess your form should be like these:

INSERT YOUR DATA TO YOUR FORM AND SUBMIT

After inserting your data you then click on submit.It should give you a notification that your data was saved successfully.As shown in the image below: