CRUD operations are essential for web applications to handle data manipulation easily. By combining PHP and MySQL, we can easily perform CRUD operations and manage our databases efficiently. This article will discuss the CRUD operations, provides steps to set up the MySQL database connection and give insights on the implementation of the CRUD operations in PHP.
Let’s start by first learning about CRUD.
What is CRUD?
CRUD stands for Create, Read, Update and Delete. These are the four basic yet most useful operations to perform data manipulation in databases or data storage systems.
Let’s understand each of these separately.
Create
Create Operation is the first operation that we can perform on the database. If we want to work with a database, we first need to create it. This operation is used to add new data. In SQL, we can use CREATE TABLE command to create a new table and the INSERT INTO command to insert data into the table.
Read
The read operation helps us to retrieve the data from the database. It is used to fetch the existing data and display it. We can retrieve all the data at once or can apply some criteria to get a subset of data. In SQL, a SELECT statement is used to read data.
Update
The update operation is used to update the already present data in the database. We can change the values of certain fields or update the entire row. In SQL, a UPDATE statement is used to update the data.
Delete
The delete operation is used to delete a specific row with some condition. It is also used to delete all rows and columns without deleting a schema. In SQL, a DELETE statement is used to delete the data from the database.
Setting up MySQL Database
The first step is to set up the MySQL Database. In case you haven’t installed MySQL on your computer, you can refer to this installation.
Now, create a new database and a table, which we will use to perform CRUD operations. Run the following commands to create a database and a table.
Command
CREATE DATABASE movies;
USE movies;
CREATE TABLE bollywood (
name VARCHAR(50) NOT NULL,
genre VARCHAR(100) NOT NULL,
rating INT(10)
);
Output
Connecting to MySQL Database
The next step is to connect your MySQL database to your PHP. We can create a connection using mysqli function by using the new keyword and passing the following arguments: IP address of the server, Username, Password to access the database and database name to connect to the database.
Write the following code in your PHP file to connect to the MySQL database.
<?php
$sname = "localhost";
$uname = "your_username";
$pass = "your_password";
$dbname = "movies";
// Create the connection
$conn = new mysqli($sname, $uname, $pass, $dbname);
// Check the connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>
You can also try this code with Online PHP Compiler
Now, let's perform the CRUD operations one by one.
Create Operation
To Create new records in SQL, the INSERT keyword is used. Follow the given steps to perform create operation in SQL.
Create the SQL query using the INSERT INTO statement followed by the table name and the columns in which the data will be inserted. The VALUES keyword is used to specify the values to be inserted for each corresponding column
Execute the SQL statement using the connection object $conn, which we created before starting the CRUD operations
Now, we need to check if the query was successful, which is done using the if statement. If the query is successful and the new record is inserted into the table without any errors, the script will output "Creation successful."
If any error occurs during the execution, it will return the error message along with the actual SQL query
To read data in SQL, the SELECT keyword is used. Follow the given steps to perform the read operation in SQL.
Create the SQL query using the SELECT statement that fetches all columns (*) from our bollywood table
Execute the SQL statement using the connection object $conn, which we created before starting the CRUD operations
Check if the query returns any results using the if statement. The result is stored in the $res variable
The while loop is used to iterate through each row of the result set returned by the query. We can fetch the data of the current row using $res->fetch_assoc()
Finally, we will print the data using an echo
Now, let's see the code to perform the same.
Code
<?php
$sql = "SELECT * FROM bollywood";
$res = $conn->query($sql);
if ($res->num_rows > 0) {
while ($row = $res->fetch_assoc()) {
echo " name: " . $row["name"] . ", genre: " . $row["genre"] . ", rating: " . $row["rating"] . "<br>";
}
}
else {
echo "No records found";
}
?>
You can also try this code with Online PHP Compiler
The CRUD operations provide basic operations to manage data in a database. They allow us to Create, Read, Update and Delete the data.
Is it necessary to use SQL to perform CRUD operations in PHP?
No, it is not necessary to use SQL to perform CRUD operations in PHP. We can also use NoSQL databases that use APIs for performing these operations.
Can we validate the input before performing CRUD operations?
In PHP, we can use server-side validation techniques to validate the input entered by the users. We can check data type, length, format, etc, using it.
Conclusion
In this article, we have CRUD Operations in PHP and MySQL. We first learnt about CRUD and then discussed the steps to set up MySQL and finally performed the CRUD operations in PHP.
But suppose you have just started your learning process and are looking for questions from tech giants like Amazon, Microsoft, Uber, etc. For placement preparations, you must look at the problems, interview experiences, and interview bundles.
117+ registered 