PHP MySQL Connection – Step-by-Step Guide

Introduction to PHP MySQL Connection

PHP allows seamless interaction with MySQL databases for data storage, retrieval, and manipulation. There are two primary ways to connect to MySQL in PHP:

MySQLi (MySQL Improved Extension)
PDO (PHP Data Objects)

Prerequisites

PHP installed on your local server (e.g., XAMPP or WAMP)
MySQL Database set up
Basic understanding of PHP and SQL

MySQL Database Configuration

Create a Sample Database and Table

CREATE DATABASE testdb;

USE testdb;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

Connect to MySQL Using MySQLi

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "testdb";

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

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

Connect to MySQL Using PDO (PHP Data Objects)

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "testdb";

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

Best Practices for Secure Connection

Use PDO for secure and flexible database interaction
Implement prepared statements to prevent SQL Injection
Use try-catch blocks for error handling
Avoid hardcoding passwords in production (use environment variables)

Closing the Database Connection

MySQLi

$conn->close(); // For Object-Oriented  
mysqli_close($conn); // For Procedural

PDO

$conn = null;

Complete MySQLi Code Example

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "testdb";

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

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

echo "Connected successfully";

$conn->close();
?>

Complete PDO Code Example

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "testdb";

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

$conn = null;
?>