PHP Fundamentals

MySQLi Introduction

13 min Lesson 36 of 45

What is MySQLi?

MySQLi (MySQL Improved) is a PHP extension that provides an interface for interacting with MySQL databases. It offers both procedural and object-oriented approaches, improved performance, and enhanced security features.

Why Use MySQLi?

Security: Supports prepared statements to prevent SQL injection
Performance: Better performance than the old MySQL extension
Features: Access to all MySQL features including transactions
Flexibility: Both procedural and object-oriented interfaces

Note: The old mysql_* functions were deprecated in PHP 5.5 and removed in PHP 7.0. Always use MySQLi or PDO for database operations.

MySQLi vs PDO

Feature	MySQLi	PDO
Database Support	MySQL only	12+ databases
API Style	Procedural + OOP	OOP only
Prepared Statements	Yes	Yes
Performance	Slightly faster	Very good

Connecting to MySQL Database

There are two ways to connect using MySQLi: procedural and object-oriented.

Object-Oriented Approach (Recommended)

<?php
// Database connection parameters
$host = 'localhost';
$username = 'root';
$password = '';
$database = 'my_database';

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

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

echo "Connected successfully";

// Close connection when done
$conn->close();
?>

Procedural Approach

<?php
// Database connection parameters
$host = 'localhost';
$username = 'root';
$password = '';
$database = 'my_database';

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

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

echo "Connected successfully";

// Close connection when done
mysqli_close($conn);
?>

Tip: The object-oriented approach is more modern and cleaner. We'll use it throughout this tutorial.

Setting Character Encoding

Always set the character encoding to UTF-8 to handle international characters properly:

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

// Set charset to utf8mb4 (supports emojis and all Unicode characters)
$conn->set_charset("utf8mb4");

// Alternative method
// $conn->query("SET NAMES utf8mb4");
?>

Error Handling Options

MySQLi provides different ways to handle errors:

1. Manual Error Checking (Default)

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

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

2. Exception Mode (Recommended)

<?php
// Enable exception mode
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    $conn = new mysqli($host, $username, $password, $database);
    $conn->set_charset("utf8mb4");
    echo "Connected successfully";
} catch (mysqli_sql_exception $e) {
    die("Connection failed: " . $e->getMessage());
}
?>

Warning: Never display detailed error messages to users in production. Log errors instead and show generic error messages.

Complete Database Connection Class

Here's a reusable database connection class:

<?php
class Database {
    private $host = 'localhost';
    private $username = 'root';
    private $password = '';
    private $database = 'my_database';
    private $conn;

    public function __construct() {
        // Enable exception mode
        mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

        try {
            $this->conn = new mysqli(
                $this->host,
                $this->username,
                $this->password,
                $this->database
            );

            // Set charset
            $this->conn->set_charset("utf8mb4");

        } catch (mysqli_sql_exception $e) {
            // Log error (in production)
            error_log("Database connection failed: " . $e->getMessage());

            // Show generic message to user
            die("Database connection failed. Please try again later.");
        }
    }

    public function getConnection() {
        return $this->conn;
    }

    public function close() {
        if ($this->conn) {
            $this->conn->close();
        }
    }

    public function __destruct() {
        $this->close();
    }
}

// Usage
$db = new Database();
$conn = $db->getConnection();

// Your database operations here

// Connection closes automatically when script ends
?>

Using Configuration File

Store database credentials in a separate configuration file:

config.php

<?php
// Database configuration
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASS', '');
define('DB_NAME', 'my_database');
define('DB_CHARSET', 'utf8mb4');

// Error reporting (disable in production)
define('DISPLAY_ERRORS', true);

if (DISPLAY_ERRORS) {
    ini_set('display_errors', 1);
    error_reporting(E_ALL);
}
?>

database.php

<?php
require_once 'config.php';

// Enable exception mode
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    $conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
    $conn->set_charset(DB_CHARSET);
} catch (mysqli_sql_exception $e) {
    error_log("Database error: " . $e->getMessage());
    die("Database connection failed");
}
?>

Security Note: Store config.php outside the web root or protect it with .htaccess to prevent direct access.

Exercise: Create Your First Database Connection

Create a MySQL database named learning_php
Create a config.php file with your database credentials
Create a database.php file that connects to the database
Create a test.php file that includes database.php and displays "Connected successfully"
Test the connection by accessing test.php in your browser
Intentionally enter wrong credentials and verify proper error handling

Best Practices

Use exception mode: Enable mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT)
Set UTF-8 encoding: Always use set_charset("utf8mb4")
Separate configuration: Store credentials in a separate file
Close connections: Close database connections when done (or use __destruct())
Handle errors gracefully: Log errors and show generic messages to users
Use prepared statements: Never concatenate user input into SQL queries

Summary

MySQLi is the modern way to interact with MySQL databases in PHP
It offers both procedural and object-oriented approaches
Always enable exception mode for better error handling
Set character encoding to utf8mb4 for full Unicode support
Store database credentials securely in configuration files
Never display detailed error messages to users in production