PHP provides multiple ways to connect to MySQL databases, each with its advantages. Below are all the connection types, key objects, and sample codes for each approach.

---

🔹 1. MySQL Connection Types in PHP

Method	Description
MySQLi (Procedural)	Simple, faster for basic operations
MySQLi (Object-Oriented)	Recommended for structured applications
PDO (PHP Data Objects)	Best for security & flexibility (supports multiple databases)

---

🔹 2. MySQLi Procedural Connection

💡 MySQLi procedural style uses simple functions to connect.

📝 Sample Code:

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "test_db";

// Create connection
$conn = mysqli_connect($server, $username, $password, $database);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully!";
?>

✔ mysqli_connect() establishes a connection.
✔ mysqli_connect_error() handles errors.

---

🔹 3. MySQLi Object-Oriented Connection

💡 This approach uses an object-oriented style for better structure.

📝 Sample Code:

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "test_db";

// Create connection
$conn = new mysqli($server, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully!";
?>

✔ Uses $conn->connect_error instead of mysqli_connect_error().
✔ More structured and scalable for larger applications.

---

🔹 4. PDO (PHP Data Objects) Connection

💡 PDO is the best approach for security, flexibility, and support for multiple databases (MySQL, PostgreSQL, SQLite, etc.).

📝 Sample Code:

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "test_db";

try {
    $conn = new PDO("mysql:host=$server;dbname=$database", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully!";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

✔ More secure using try-catch exception handling.
✔ Works with different database systems, not just MySQL.
✔ Best for large applications.

---

🔹 5. Key Objects in PHP-MySQL Connection

(1) MySQLi Objects (Procedural & OOP)

Object	Description
$conn	Represents the database connection
$result	Stores the result of a query
$stmt	Used for prepared statements

(2) PDO Objects

Object	Description
$conn	PDO database connection instance
$stmt	PDO statement for queries
$errorInfo	Stores error messages

---

🔹 6. Performing CRUD Operations in PHP & MySQL

🔸 (A) INSERT Data (MySQLi Procedural)

$sql = "INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')";
if (mysqli_query($conn, $sql)) {
    echo "Record inserted successfully!";
} else {
    echo "Error: " . mysqli_error($conn);
}

---

🔸 (B) SELECT Data (MySQLi Object-Oriented)

$sql = "SELECT id, name, email FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . "<br>";
    }
} else {
    echo "0 results";
}

---

🔸 (C) UPDATE Data (PDO)

$sql = "UPDATE users SET email='newemail@example.com' WHERE id=1";
$stmt = $conn->prepare($sql);
$stmt->execute();
echo "Record updated!";

---

🔸 (D) DELETE Data (PDO with Prepared Statement for Security)

$sql = "DELETE FROM users WHERE id=:id";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':id', $id);
$id = 1;
$stmt->execute();
echo "Record deleted!";

---

🔹 7. Closing the Connection

✔ MySQLi Procedural:

mysqli_close($conn);

✔ MySQLi OOP:

$conn->close();

✔ PDO:

$conn = null;

---

🔹 8. Which Connection Type Should You Use?

Method	Best For
MySQLi Procedural	Simple applications, quick scripts
MySQLi OOP	Medium to large applications
PDO	Best for security, flexibility, and multiple database support

---

🚀 Final Thoughts

🔹 Use PDO if you want the most secure and scalable solution.
🔹 Use MySQLi Object-Oriented if working with only MySQL and prefer structured programming.
🔹 Use MySQLi Procedural for simple and quick scripts.

Want help with a real-world PHP & MySQL project? Drop a comment! 💬🚀