🐘 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

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.