🐘 PHP MySQL Integration – MySQLi & PDO Explained with Examples

---

🧲 Introduction – Why Learn PHP with MySQL?

Combining PHP (server-side scripting) and MySQL (relational database) is one of the most popular ways to build dynamic and database-driven websites. From blogs and eCommerce sites to user management and CMS systems, this duo powers a significant portion of the internet.

🎯 What You’ll Learn:

• How to connect PHP to MySQL using MySQLi and PDO
• How to perform CRUD operations (SELECT, INSERT, UPDATE, DELETE)
• Explanation of each code snippet
• Secure coding practices to avoid SQL injection
• Real-world examples of usage

---

⚙️ Connecting PHP with MySQL

✅ Method 1: Using MySQLi (MySQL Improved)

<?php
$conn = new mysqli("localhost", "username", "password", "database");

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

🧠 Explanation:

• new mysqli(...): Initializes a new MySQLi object to open a connection.
• "localhost": MySQL server location.
• "username", "password", "database": Your credentials and database name.
• connect_error: If the connection fails, this property contains the error.
• die(): Terminates the script with an error message.

---

✅ Method 2: Using PDO (PHP Data Objects)

<?php
try {
    $pdo = new PDO("mysql:host=localhost;dbname=database", "username", "password");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "✅ Connected successfully";
} catch (PDOException $e) {
    echo "❌ Connection failed: " . $e->getMessage();
}
?>

🧠 Explanation:

• new PDO(...): Creates a new PDO connection.
• setAttribute(...): Configures PDO to throw exceptions on errors.
• catch (PDOException $e): Catches and displays errors gracefully.

---

💾 CRUD Operations in PHP + MySQL

🔍 SELECT – Retrieving Data (MySQLi)

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

while ($row = $result->fetch_assoc()) {
    echo $row['name'] . " - " . $row['email'] . "<br>";
}
?>

🧠 Explanation:

• query($sql): Sends the SQL query to the database.
• fetch_assoc(): Fetches one row at a time as an associative array.
• echo: Outputs the name and email fields of each user.

---

➕ INSERT – Adding New Records (Prepared Statement – MySQLi)

<?php
$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);

$name = "Alice";
$email = "alice@example.com";
$stmt->execute();
?>

🧠 Explanation:

• prepare(): Prepares an SQL query with placeholders.
• bind_param("ss", ...): Binds PHP variables to placeholders. "ss" = 2 strings.
• execute(): Runs the query with bound values.

✅ Prevents SQL injection because it doesn’t allow SQL code to be injected into variables.

---

📝 UPDATE – Modifying Existing Records

<?php
$stmt = $conn->prepare("UPDATE users SET email = ? WHERE id = ?");
$stmt->bind_param("si", $email, $id);

$email = "alice_updated@example.com";
$id = 1;
$stmt->execute();
?>

🧠 Explanation:

• "UPDATE ... WHERE id = ?": Updates the row with the given id.
• "si": First is a string (email), second is an integer (id).
• Replaces the old email of user with ID 1.

---

❌ DELETE – Removing Records

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

$id = 1;
$stmt->execute();
?>

🧠 Explanation:

• DELETE FROM users WHERE id = ?: Deletes a row matching the id.
• "i" means integer.
• Deletes the user with ID = 1.

---

🛡️ Best Practices for PHP + MySQL

📌 Use Prepared Statements: Always bind values to prevent injection.

🔐 Secure Credentials:

• Store DB usernames/passwords in a .env file.
• Load them with getenv() or a config array.

📊 Use Specific User Privileges:

• Create a user with limited privileges (not root).
• Only allow access to required tables and commands.

🔁 Close Connections:

$conn->close(); // for MySQLi
unset($pdo);    // for PDO

---

📊 Real-World Applications

Use Case	Role of PHP + MySQL
Blog CMS	Store posts, comments, user info
E-commerce	Store orders, product inventory, customers
Admin Dashboard	Show charts, filter user data
Feedback Form	Capture input and store to database
Authentication	Store and verify login credentials

---

📌 Summary – Recap & Next Steps

PHP offers powerful tools to interact with MySQL using MySQLi and PDO. Learning both connection styles, practicing CRUD, and understanding prepared statements sets you up for full-stack development.

🔍 Key Takeaways

• Use MySQLi or PDO to connect to MySQL
• Always use prepared statements for security
• Understand and implement all CRUD operations
• Use environment files to secure your credentials

⚙️ Real-World Relevance
PHP and MySQL power a huge number of web systems. Mastery of their integration means you’re ready to build dynamic and secure data-driven applications.

---

❓ FAQ – PHP MySQL Integration

❓ Should I use MySQLi or PDO?
✅ PDO is more flexible (supports multiple DBMS). MySQLi is faster for MySQL-specific apps.

❓ How to prevent SQL injection in PHP?
✅ Use prepared statements (prepare() and bind_param()/bindValue()).

❓ Can I use MySQL in PHP without installing anything?
✅ Yes, if you’re using XAMPP, WAMP, or LAMP, both MySQL and PHP are included.

❓ Is it safe to store DB credentials in PHP code?
❌ No. Use .env or separate config files outside web root.

❓ What tool can help me manage MySQL easily?
✅ Tools like MySQL Workbench, DBeaver, and phpMyAdmin help visualize and manage DBs.

---

---

Share Now :