CRUD Operations in PHP PDO with Exception Management with examples

CRUD stands for Create, Read, Update, and Delete. These are the basic operations used to interact with a database.

Create (Insert):

- Inserts new data into the database

Syntax:


INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);


Example:


$sql = "INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')";
mysqli_query($conn, $sql);


Read (Select):

- Retrieves data from the database

Syntax:


SELECT column1, column2, ... FROM table_name WHERE condition;


Example:


$sql = "SELECT * FROM users WHERE id = 1";
$result = mysqli_query($conn, $sql);
$user = mysqli_fetch_assoc($result);
echo $user['name']; // outputs "John Doe"


Update:

- Modifies existing data in the database

Syntax:


UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;


Example:


$sql = "UPDATE users SET name = 'Jane Doe' WHERE id = 1";
mysqli_query($conn, $sql);


Delete:

- Deletes data from the database

Syntax:


DELETE FROM table_name WHERE condition;


Example:


$sql = "DELETE FROM users WHERE id = 1";
mysqli_query($conn, $sql);


PHP CRUD Example:


$conn = mysqli_connect("localhost", "username", "password", "database");

// Create
$sql = "INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')";
mysqli_query($conn, $sql);

// Read
$sql = "SELECT * FROM users WHERE id = 1";
$result = mysqli_query($conn, $sql);
$user = mysqli_fetch_assoc($result);
echo $user['name']; // outputs "John Doe"

// Update
$sql = "UPDATE users SET name = 'Jane Doe' WHERE id = 1";
mysqli_query($conn, $sql);

// Delete
$sql = "DELETE FROM users WHERE id = 1";
mysqli_query($conn, $sql);


PDO (PHP Data Objects) is a database extension that provides a consistent interface for accessing databases.

Create (Insert):

- Inserts new data into the database

Syntax:


try {
  $pdo = new PDO('mysql:host=localhost;dbname=database', 'username', 'password');
  $sql = "INSERT INTO users (name, email) VALUES (:name, :email)";
  $stmt = $pdo->prepare($sql);
  $stmt->execute(['name' => 'John Doe', 'email' => 'john@example.com']);
} catch (PDOException $e) {
  echo "Error: " . $e->getMessage();
}


Read (Select):

- Retrieves data from the database

Syntax:


try {
  $pdo = new PDO('mysql:host=localhost;dbname=database', 'username', 'password');
  $sql = "SELECT * FROM users WHERE id = :id";
  $stmt = $pdo->prepare($sql);
  $stmt->execute(['id' => 1]);
  $user = $stmt->fetch();
  echo $user['name']; // outputs "John Doe"
} catch (PDOException $e) {
  echo "Error: " . $e->getMessage();
}


Update:

- Modifies existing data in the database

Syntax:


try {
  $pdo = new PDO('mysql:host=localhost;dbname=database', 'username', 'password');
  $sql = "UPDATE users SET name = :name WHERE id = :id";
  $stmt = $pdo->prepare($sql);
  $stmt->execute(['name' => 'Jane Doe', 'id' => 1]);
} catch (PDOException $e) {
  echo "Error: " . $e->getMessage();
}


Delete:

- Deletes data from the database

Syntax:


try {
  $pdo = new PDO('mysql:host=localhost;dbname=database', 'username', 'password');
  $sql = "DELETE FROM users WHERE id = :id";
  $stmt = $pdo->prepare($sql);
  $stmt->execute(['id' => 1]);
} catch (PDOException $e) {
  echo "Error: " . $e->getMessage();
}


Exception Management:

- Use try-catch blocks to handle PDO exceptions
- Use PDOException class to catch exceptions

Example:


try {
  // PDO code
} catch (PDOException $e) {
  echo "Error: " . $e->getMessage();
  // Log error or perform error handling
}


Best Practices:

1. Use prepared statements to prevent SQL injection
2. Validate and sanitize user input
3. Use error handling to catch PDO exceptions
4. Use transactions for multiple queries
5. Optimize queries for performance
6. Test CRUD operations thoroughly

By understanding CRUD operations in PHP PDO with exception management, you can perform database operations securely and handle errors efficiently.