Performing CRUD Operations with PHP and MySQL

When developing web applications, the ability to interact with databases is crucial. PHP, being a versatile server-side scripting language, seamlessly integrates with various databases. Among them, MySQL is one of the most widely used databases.

In this guide, we’ll explore how PHP interacts with MySQL for basic CRUD (Create, Read, Update, Delete) operations.

Connecting to MySQL Database

Using MySQLi Extension

$servername = "localhost";
$username = "userName";
$password = "password";

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

Using PHP Data Objects (PDO)

$servername = "localhost";
$username = "userName";
$password = "password";
$db = "DatabaseName";

$conn = new PDO("mysql:host=$servername;dbname=$db", $username, $password);

Closing a MySQL Database Connection

Using MySQLi

$conn->close();

Using PDO

$conn = null;

CRUD Operations

1. Creating a Table

$sql = "CREATE TABLE STUDENT (
id INT(5) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
pursuing VARCHAR(30)
)";

if ($conn->query($sql) === TRUE) {
  echo "Table created successfully";
} else {
  echo "Error creating table: " . $conn->error;
}

2. Inserting a Record

$sql = "INSERT INTO STUDENT (firstname, lastname, pursuing) VALUES ('John', 'Doe', 'Computer Science')";

if ($conn->query($sql) === TRUE) {
  echo "Record inserted successfully";
} else {
  echo "Error inserting record: " . $conn->error;
}

3. Inserting Multiple Records

$sql = "INSERT INTO STUDENT (firstname, lastname, pursuing) VALUES ('John', 'Doe', 'Computer Science');";
$sql .= "INSERT INTO STUDENT (firstname, lastname, pursuing) VALUES ('Jane', 'Smith', 'Mathematics');";

if ($conn->multi_query($sql) === TRUE) {
  echo "Multiple records inserted successfully";
} else {
  echo "Error inserting records: " . $conn->error;
}

4. Reading Records

$sql = "SELECT id, firstname, lastname, pursuing FROM STUDENT";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
  while ($row = $result->fetch_assoc()) {
    // Process each row
  }
} else {
  echo "0 records found";
}

5. Updating a Record

$sql = "UPDATE STUDENT SET pursuing = 'Physics' WHERE id = 1";

if ($conn->query($sql) === TRUE) {
  echo "Record updated successfully";
} else {
  echo "Error updating record: " . $conn->error;
}

6. Deleting Records

$sql = "DELETE FROM STUDENT WHERE id = 1";

if ($conn->query($sql) === TRUE) {
  echo "Record deleted successfully";
} else {
  echo "Error deleting record: " . $conn->error;
}

These examples demonstrate the basic CRUD operations with PHP and MySQL. It’s important to handle database connections securely, especially when dealing with user inputs, to prevent SQL injection attacks. Additionally, error handling and proper validation should be implemented for robust database interactions in real-world applications.

You may also like: