Learn CRUD wih PHP and MySQL (WEB - 7)

 

Creating a web application that can perform CRUD (Create, Read, Update, Delete) operations is an essential skill for any PHP developer. CRUD functionality allows users to manage data effectively and efficiently. In this article, we will explore how to implement CRUD operations with PHP 7 and MySQL.

Before we dive into the code, let's define what CRUD operations are.

• Create: Adding new data to the database
• Read: Retrieving data from the database
• Update: Modifying existing data in the database
• Delete: Removing data from the database

In this article, we will build a simple PHP application that allows users to perform CRUD operations on a MySQL database.

Setting Up the Environment

Before we start writing the code, we need to set up our environment. We will need to install and configure the following software:

• PHP 7
• MySQL
• Apache

If you're using a Windows machine, you can install XAMPP or WAMP, which will set up all the required software for you. If you're using a Unix-based system, you can install each of the software components individually.

Once you have installed the required software, you can start creating our PHP application.

Creating the Database

We will start by creating the MySQL database that will store our data. We will create a simple table called "users" with the following columns:

- id (auto-incrementing integer)

- name (varchar)

- email (varchar)

- password (varchar)

Here is the SQL code to create the table:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Once we have created the table, we can move on to the PHP code.

Creating the PHP Application

We will create a simple PHP application that allows users to perform CRUD operations on the "users" table. We will create four PHP scripts, one for each CRUD operation.

Create Operation

The first PHP script will allow users to add new data to the "users" table. Here is the code:

<?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);
}

// Prepare and bind
$stmt = $conn->prepare("INSERT INTO users (name, email, password) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $name, $email, $password);

// set parameters and execute
$name = "John Doe";
$email = "johndoe@example.com";
$password = "password";
$stmt->execute();

echo "New records created successfully";

$stmt->close();
$conn->close();
?>

This code creates a new mysqli connection to the database, prepares an SQL statement to insert new data, binds the parameters to the SQL statement, sets the parameter values, and executes the statement. Finally, it prints a message indicating that the records were created successfully.

Read Operation

The second PHP script will allow users to retrieve data from the "users" table. Here is the code:

<?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 = "SELECT id, name, email FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"]. "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>

This code creates a new mysqli connection to the database, prepares an SQL statement to retrieve data, and executes the statement. It then loops through the result set and prints the data to the screen.

Update Operation

The third PHP script will allow users to update data in the "users" table. Here is the code:

<?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);
}

// prepare and bind
$stmt = $conn->prepare("UPDATE users SET name=?, email=?, password=? WHERE id=?");
$stmt->bind_param("sssi", $name, $email, $password, $id);

// set parameters and execute
$name = "Jane Doe";
$email = "janedoe@example.com";
$password = "newpassword";
$id = 1;
$stmt->execute();

echo "Record updated successfully";

$stmt->close();
$conn->close();
?>

This code creates a new mysqli connection to the database, prepares an SQL statement to update data, binds the parameters to the SQL statement, sets the parameter values, and executes the statement. Finally, it prints a message indicating that the record was updated successfully.

Delete Operation

The fourth PHP script will allow users to delete data from the "users" table. Here is the code:

<?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);
}

// prepare and bind
$stmt = $conn->prepare("DELETE FROM users WHERE id=?");
$stmt->bind_param("i", $id);

// set parameters and execute
$id = 1;
$stmt->execute();

echo "Record deleted successfully";

$stmt->close();
$conn->close();
?>

This code creates a new mysqli connection to the database, prepares an SQL statement to delete data, binds the parameter to the SQL statement, sets the parameter value, and executes the statement. Finally, it prints a message indicating that the record was deleted successfully.

 Conclusion

In this article, we have explored how to implement CRUD operations with PHP 7 and MySQL. We have created a simple PHP application that allows users to perform CRUD operations on a MySQL database. We have also provided sample code for each CRUD operation. With these skills, you can now create more advanced web applications that require data management.