PHP CRUD Example | MySQLi Bootstrap Source Code

We will learn on php crud operations in this example using MySQLi Database.

CRUD stands for Create, Read, Update and Delete the data from database.

These are the basic operations for database.

Create for insert data, Read for select data, Update for update the data which is already exist, Delete for delete data.

Using php crud operations developer will make their website dynamic.

Here we have the tutorial for all basic php crud operations.

For this, We have a database name “demonuts”.

In this database we create one table named “crud_tbl” on this table we perform CRUD operations.

mysqli_query() function is used for execute any operation over database using PHP script.

This table have 4 columns. which are following :

id (int)

name (varchar)

age (int)

city (varchar)

Create (Insert) Data :

Using this operation we add data to database.

User visit the website and want to send some feedback then this operation is going to use.

SYNTAX

INSERT INTO table_name ( field1, field2,...)
   VALUES
   ( value1, value2,...);

Read (Select) Data :

Into this read (select) function user get all the data from respective table into database.

SYNTAX

SELECT column_name(s) FROM table_name

This syntax is going to use when we want to read data of specific column of table.

SELECT * FROM table_name

Using this syntax we read all columns of table from database.

Update Data :

For update data which is already available into table in database we use update query.

SYNTAX :

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

Delete Data :

By using this operation we delete the data form table into database.

SYNTAX :

DELETE FROM table_name
WHERE some_column = some_value

Now we are moving towards the example of CRUD so we can get the actual idea of all operations.

Database connection :

We require database connection to perform every operation on mysql using PHP script.

SYNTAX

$connection = mysqli_connect('$servername, $dbusername, $dbpassword, $dbname');

Put the following code at the beginning of your PHP script page.

$connection = mysqli_connect('localhost', 'root', '', 'demonuts');

First we create one index.php file which is a landing page for us.

Here we have two options first for add new data or second is for read all data from database.

index.php

<!DOCTYPE html>
<html>
	<head>
		<title>CRUD Operations</title>
		<link rel="stylesheet" type="text/css" href="style.css">
	</head>
	<body>
		<div class="input-group">
		<a href="insert.php"><button class="btn" type="add" name="add" >Add new Data</button></a>
		
		<a href="read.php"><button class="btn" type="read" name="read" >Get All Data</button></a>
		
		</div>
	</body>
</html>

We get output like this –

For making some design layout we need to add some style sheet.

So we create a style.css which contain styles for every classes.

Following code require to paste under the <title> section in your PHP script page.

which connect style to your page.

<link rel="stylesheet" type="text/css" href="style.css">

style.css

body {
    font-size: 19px;
}
table{
    width: 50%;
    margin: 30px auto;
    border-collapse: collapse;
    text-align: left;
}
tr {
    border-bottom: 1px solid #cbcbcb;
}
th, td{
    border: none;
    height: 30px;
    padding: 2px;
}
tr:hover {
    background: #F5F5F5;
}

form {
    width: 45%;
    margin: 50px auto;
    text-align: left;
    padding: 20px; 
    border: 1px solid #bbbbbb; 
    border-radius: 5px;
}

.input-group {
    margin: 10px 0px 10px 0px;
}
.input-group label {
    display: block;
    text-align: left;
    margin: 3px;
}
.input-group input {
    height: 30px;
    width: 93%;
    padding: 5px 10px;
    font-size: 16px;
    border-radius: 5px;
    border: 1px solid gray;
}
.btn {
    padding: 10px;
    font-size: 15px;
    color: white;
    background: #9e0e24;
    border: none;
    border-radius: 5px;
}
.edit_btn {
    text-decoration: none;
    padding: 2px 5px;
    background: #9e0e24;
    color: white;
    border-radius: 3px;
}

.del_btn {
    text-decoration: none;
    padding: 2px 5px;
    color: white;
    border-radius: 3px;
    background: #9e0e24;
}

When we click on “Add new data” we redirected to the insert page.

If we click on “Get all data” we redirected to the read page which is having all the data and also have the options for update and delete.

First, we take a tour to add new data. For that create a new PHP script page insert.php

insert.php

<?php
	//database connection
	$connection = mysqli_connect('localhost', 'root', '', 'demonuts');
?>
<?php
	// insert data
	$name="";
	$age="";
	$city="";
	
	if(isset($_POST['save']))
	{
		$name = $_POST['name'];
		$age = $_POST['age'];
		$city = $_POST['city'];
		
		// insert query
		$insert="INSERT INTO crud_tbl(id,name,age,city) VALUES ('','$name','$age','$city')";
		$query = mysqli_query($connection, $insert);
		if(! $query ) 
		{
		die('Could not enter data: ' . mysqli_error());
		}
		else
		{
		echo "Data insert successfully";
		}
	}
?>
<html>
	<head>
		<title>CRUD Operations</title>
		<link rel="stylesheet" type="text/css" href="style.css">
	</head>
	<body>
		
		<form method="post" action="insert.php" >
		<h3 align="center"><I>ADD NEW DATA</I></h3>
		<div class="input-group">
			<label>Name</label>
			<input type="text" name="name" value="">
		</div>
		<div class="input-group">
			<label>Age</label>
			<input type="text" name="age" value="">
		</div>
		<div class="input-group">
			<label>City</label>
			<input type="text" name="city" value="">
		</div>
		<div class="input-group">
			<button class="btn" type="submit" name="save" align="center">Save</button>
			<a href="index.php"><button type="button" name="back" class="btn">Back</button></a>
		</div>
	</form>
	</body>
</html>

Using this code we successfully insert data using PHP script.

We have the insert page like this –

Here, data is saved into the respected table into database.

Now we are further going to read data from database.

For retrieve data from database, we create one PHP script page read.php.

When click on “Get all data” button from index.php page we redirected to read page.

Where we get all the data from database which are inserted successfully.

read.php

<?php
	//database connection
	$connection = mysqli_connect('localhost', 'root', '', 'demonuts');
?>
<?php
// Delete data
	if (isset($_GET['del'])) 
	{
		$id = $_GET['del'];
		mysqli_query($connection, "DELETE FROM crud_tbl WHERE id=$id");
	}
?>
<html>
	<head>
		<title>Read All Data</title>
		<link rel="stylesheet" type="text/css" href="style.css">
	</head>
	<body>
		<table>
	<thead>
		<tr>
			<th>Id</th>
			<th>Name</th>
			<th>Age</th>
			<th>City</th>
			<th colspan="2">Action</th>
		</tr>
	</thead>
	<?php $results = mysqli_query($connection, "SELECT * FROM crud_tbl");
		  while ($row = mysqli_fetch_array($results)) { ?>
		<tr>
			<td><?php echo $row['id']; ?></td>
			<td><?php echo $row['name']; ?></td>
			<td><?php echo $row['age']; ?></td>
			<td><?php echo $row['city']; ?></td>
			<td>
				<a href="edit.php?edit=<?php echo $row['id']; ?>" class="edit_btn" >Edit</a>
			</td>
			<td>
				<a href="read.php?del=<?php echo $row['id']; ?>" class="del_btn">Delete</a>
			</td>
		</tr>
	<?php } ?>
	<tr>
			<td><a href="index.php"><button type="button" name="back" class="btn">Back</button></a></td>
		</tr>
</table>

	</body>
</html>

Here we get two buttons beside the data which are for edit specific data and delete specific data.

We get the output like this –

For delete the data add the below code above the html code starts.

here we add delete query into read.php file.

<?php
// Delete data
	if (isset($_GET['del'])) 
	{
		$id = $_GET['del'];
		mysqli_query($connection, "DELETE FROM crud_tbl WHERE id=$id");
	}
?>

Update data :

<?php
	//database connection
	$connection = mysqli_connect('localhost', 'root', '', 'demonuts');
?>
<?php
// update the data
	if (isset($_POST['update'])) {
	//$id = $_GET['edit'];
	$id = $_GET['edit']; 
	$name = $_POST['name'];
	$age = $_POST['age'];
	$city = $_POST['city'];

	mysqli_query($connection, "UPDATE crud_tbl SET name='$name', age='$age', city='$city' WHERE id=$id");
	echo "Data updated...";
	//$_SESSION['message'] = "Address updated!"; 
	//header('location: index.php');
}
?>
<html>
	<head>
		<title>CRUD Operations</title>
		<link rel="stylesheet" type="text/css" href="style.css">
	</head>
	<body>
		<?php 
			//  get the appropriate data of id
				$id = $_GET['edit']; 
				$results = mysqli_query($connection, "SELECT * FROM crud_tbl WHERE id=$id");
					$row = mysqli_fetch_array($results);
					 $name = $row['name'];
					 $age = $row['age'];
					 $city = $row['city'];
		?>	
		<form method="post">
		<h3 align="center"><I>Edit DATA</I></h3>
		<div class="input-group">
			<label>Name</label>
			<input type="text" name="name" value="<?php echo $name; ?>">
		</div>
		<div class="input-group">
			<label>Age</label>
			<input type="text" name="age" value="<?php echo $age; ?>">
		</div>
		<div class="input-group">
			<label>City</label>
			<input type="text" name="city" value="<?php echo $city; ?>">
		</div>
		<div class="input-group">
			<button class="btn" type="submit" name="update" align="center">Edit</button>
			<a href="read.php"><button type="button" name="back" class="btn">Back</button></a>
		</div>
	</form>
	</body>
</html>

Here we have the update page like this –

Which id you want to delete is showing into the edit form.

After edit detail click on “edit” your data will be updated into respected database.

Best and latest Android Development course