Insertion, Updation and Deletion of Data Using PHP
We will create simple PHP applications to perform all CRUD operations on a MySQL database table. CRUD is an acronym for Create, Read, Update, and Delete. These are the basic data manipulation operations for a database.
Creating a database using PHP
The basic steps to create a MySQL database using PHP are:- Establish a connection to MySQL server from using PHP script.
- If the connection is successful, write a SQL query to create a database and store it in a string variable.
- Execute the query.
- Close the connection.
<?php
$connection = mysqli_connect("localhost","root",""); // creating connection
$query = "CREATE DATABASE college"; // query to create database 'college'
$result = mysqli_query($connection, $query); // executing the query
if($result == TRUE)
{
echo "Database created successfully";
}
else
{
echo "Error creating database: ".mysqli_error($connection);
}
mysqli_close($connection); // Close the connection to the database.
?>
Creating a table in the database
The basic steps to create a MySQL database table using PHP are:- Establish a connection to MySQL server using PHP script.
- Select the database.
- Write an SQL query to create a table in the database and store it in a string variable.
- Execute the query.
- Close the connection.
<?php
$connection = mysqli_connect("localhost","root","","college"); // creating connection
$query = "CREATE TABLE student(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
date_of_birth DATE NOT NULL,
course VARCHAR(20) NOT NULL);"; // query to create table 'student' in the database 'college'
$result = mysqli_query($connection, $query); // executing the query
if($result == TRUE)
{
echo "Table created successfully";
}
else
{
echo "Error creating Table: ".mysqli_error($connection);
}
mysqli_close($connection); // Close the connection to the database.
?>
Inserting data in to the MySQL Database Table
The basic steps to add data to MySQL database table using PHP are:- Establish a connection to MySQL server using PHP script.
- Select the database.
- Write an SQL query to insert row data to the database table.
- Execute the query.
- Close the connection.
<?php
$connection = mysqli_connect("localhost","root","","college"); // creating connection
$query = "INSERT INTO student(name,date_of_birth,course)VALUES('Sriram','2000-05-01','BCA')"; // Insert query
$result = mysqli_query($connection, $query); // executing the query
if($result == TRUE)
{
echo "Data inserted successfully";
}
else
{
echo "Error inserting data : ".mysqli_error($connection);
}
mysqli_close($connection); // Close the connection to the database.
?>
Show data from the MySQL Database Table using SELECT
The basic steps to retrieve data from MySQL database table using PHP are:- Establish a connection to MySQL server using PHP script.
- Select the database.
- Write an SQL query to select data from the database table.
- Execute the query.
- Close the connection.
<?php
$connection = mysqli_connect("localhost","root","","college"); // creating connection
$query = "SELECT * FROM student"; // Select query
$result = mysqli_query($connection, $query); // executing the query
while($row_data = mysqli_fetch_array($result))
{
echo "Id: ".$row_data['id'];
echo "<br> Name: ".$row_data['name'];
echo "<br> Date of Birth: ".$row_data['date_of_birth'];
echo "<br> Course: ".$row_data['course'];
}
mysqli_close($connection); // Close the connection to the database.
?>
Update an existing data in the MySQL Database Table
The basic steps to update an existing data using PHP are:- Establish a connection to MySQL server using PHP script.
- Select the database.
- Write an SQL query to update data in the database table. Ensure that there is a ‘WHERE’ clause to target the data to be updated. Otherwise, the entire table will be affected.
- Execute the query.
- Close the connection.
<?php
$connection = mysqli_connect("localhost","root","","college"); // creating connection
$query = "UPDATE student set date_of_birth = '2000-06-01',course = 'BA' WHERE id = 1"; // Update query
$result = mysqli_query($connection, $query); // executing the query
if($result == TRUE)
{
echo "Data updated successfully";
}
else
{
echo "Error updating data : ".mysqli_error($connection);
}
mysqli_close($connection); // Close the connection to the database.
?>
Show data from the MySQL Database Table using SELECT
The basic steps to delete data from MySQL database table using PHP are:- Establish a connection to MySQL server using PHP script.
- Select the database.
- Write an SQL query to delete row data from the database table. Ensure that there is a ‘WHERE’ clause to target the data to be deleted. Otherwise, the entire data in the table will be deleted.
- Execute the query.
- Close the connection.
<?php
$connection = mysqli_connect("localhost","root","","college"); // creating connection
$query = "DELETE FROM student WHERE id = 1"; // Delete query
$result = mysqli_query($connection, $query); // executing the query
if($result == TRUE)
{
echo "Data deleted successfully";
}
else
{
echo "Error deleting data : ".mysqli_error($connection);
}
mysqli_close($connection); // Close the connection to the database.
?>