PHP MySQL Connections

PHP MySQL Connections – Connect PHP with MySQL DB

PHP provides two main ways to connect to and interact with MySQL databases:

  1. MySQLi (MySQL Improved)
  2. PDO (PHP Data Objects)

1. MySQLi (MySQL Improved)

Key Features:

  • Works only with MySQL databases.
  • Offers both procedural and object-oriented styles.
  • Provides built-in prepared statement support to prevent SQL injection.

1.1 MySQLi Procedural Connection

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test_db";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

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

1.2 MySQLi Object-Oriented Connection

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test_db";

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

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

1.3 MySQLi Prepared Statements

Prepared statements secure your application by separating SQL queries from data.

Example:

<?php
$conn = new mysqli("localhost", "root", "", "test_db");

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

// Prepare a statement
$stmt = $conn->prepare("SELECT id, username FROM users WHERE email = ?");
$stmt->bind_param("s", $email);

// Set parameter
$email = "example@example.com";
$stmt->execute();
$result = $stmt->get_result();

// Fetch data
while ($row = $result->fetch_assoc()) {
    echo "ID: " . $row["id"] . " - Username: " . $row["username"] . "<br>";
}

$stmt->close();
$conn->close();
?>

2. PDO (PHP Data Objects)

Key Features:

  • Works with multiple database systems (MySQL, PostgreSQL, SQLite, etc.).
  • Supports only object-oriented style.
  • Built-in prepared statements for SQL injection prevention.

2.1 Basic PDO Connection

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test_db";

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

2.2 PDO Prepared Statements

Prepared statements in PDO provide secure and flexible query execution.

Example: Fetching Data

<?php
$conn = new PDO("mysql:host=localhost;dbname=test_db", "root", "");

// Prepare and execute a statement
$stmt = $conn->prepare("SELECT id, username FROM users WHERE email = :email");
$stmt->bindParam(':email', $email);

// Set parameter
$email = "example@example.com";
$stmt->execute();

// Fetch data
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo "ID: " . $row["id"] . " - Username: " . $row["username"] . "<br>";
}
?>

Example: Inserting Data

<?php
$conn = new PDO("mysql:host=localhost;dbname=test_db", "root", "");

$stmt = $conn->prepare("INSERT INTO users (username, email) VALUES (:username, :email)");
$stmt->bindParam(':username', $username);
$stmt->bindParam(':email', $email);

// Set parameters
$username = "JohnDoe";
$email = "john@example.com";
$stmt->execute();

echo "New record created successfully";
?>

3. Comparison: MySQLi vs PDO

Feature MySQLi PDO
Database Support Only MySQL Supports multiple DB types (MySQL, SQLite, PostgreSQL, etc.)
API Style Procedural & Object-Oriented Object-Oriented only
Prepared Statements Supported Supported
Ease of Use Easy for MySQL beginners Versatile and flexible
Portability Limited to MySQL Works with various databases

 

4. Which Should You Use?

  • If you’re working only with MySQL: MySQLi is simpler and performs well.
  • If you might use other databases in the future: PDO is a better choice for portability.

5. Common Errors and Debugging Tips

  1. Connection Issues:
    Ensure the database server is running, and the credentials are correct.
  2. Prepared Statement Errors:
    Double-check query syntax and parameter bindings.
  3. Error Reporting:
    Use mysqli_report() for MySQLi or set PDO::ATTR_ERRMODE for PDO.

Example: Enabling MySQLi Error Reporting

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = new mysqli("localhost", "root", "", "test_db");
?>

Example: Enabling PDO Error Mode

<?php
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
?>