Using PHP MySQL Extensions (mysqlnd, mysqli, PDO) in ServBay

ServBay is a powerful integrated local web development environment that greatly simplifies the interaction between PHP applications and databases. It comes with key MySQL database extensions—mysqlnd, mysqli, and PDO_MySQL—pre-installed and enabled by default for all supported PHP versions. This means you can start connecting to and operating on MySQL or MariaDB databases in your PHP projects without any manual setup.

Supported PHP MySQL Extensions in ServBay

ServBay integrates the following three mainstream MySQL extensions into its managed PHP environments, ensuring both compatibility and high performance:

1. MySQL Native Driver (mysqlnd): PHP’s native driver that acts as the underlying library for mysqli and PDO_MySQL. It provides the core communication between PHP and MySQL/MariaDB servers, often delivering better performance and memory management.
2. MySQLi (mysqli): MySQL Improved Extension offering both object-oriented and procedural interfaces specifically for connecting to MySQL and MariaDB databases. It supports advanced features such as prepared statements, transactions, stored procedures, and multi-query execution.
3. PDO MySQL (PDO_MySQL): MySQL driver for PHP Data Objects (PDO). PDO provides a unified database access abstraction layer, enabling developers to interact with various databases (including MySQL, MariaDB, PostgreSQL, SQLite, etc.) using a consistent API, enhancing portability.

Enabled by Default

In ServBay, the mysqlnd, mysqli, and PDO_MySQL extensions are enabled by default for all PHP versions. You do not need to take any extra steps to activate them. ServBay has pre-configured the necessary php.ini settings so you can immediately use these extensions in your code to connect to databases.

Detailed Guide to Each PHP MySQL Extension

Understanding the differences among these three extensions helps you choose the best one for your project:

1. MySQL Native Driver (mysqlnd)

   • Primary Role: mysqlnd is a PHP-native driver, implemented in C, that serves as the foundational layer for PHP to communicate with MySQL/MariaDB servers.
   • Key Features: Since PHP 5.3, mysqlnd has replaced libmysqlclient as the default underlying library for both mysqli and PDO_MySQL. It is integrated directly into PHP, removing the need for external client libraries like libmysqlclient.
   • Main Advantages: Compared to the deprecated libmysqlclient, mysqlnd generally offers better performance, more efficient memory usage, and advanced features such as asynchronous queries, improved persistent connections, and a rich plugin API. Normally, you don’t interact directly with the mysqlnd API; instead, you leverage its benefits through mysqli or PDO.

2. MySQLi (mysqli)

   • Primary Role: MySQL Improved Extension is designed specifically for MySQL (version 4.1.3 and above) and MariaDB databases.
   • Key Features: Offers both object-oriented and procedural programming interfaces. Supports advanced MySQL/MariaDB features, including prepared statements (for strong SQL injection prevention), transaction control, stored procedures, and multi-statement execution.
   • Main Advantages: Feature-rich, highly optimized for MySQL/MariaDB, and delivers robust performance. If your project exclusively uses MySQL or MariaDB and needs advanced features unique to those databases, mysqli is a reliable choice.

3. PDO MySQL (PDO_MySQL)

   • Primary Role: PHP Data Objects (PDO) provides a lightweight, consistent interface for accessing multiple databases; PDO_MySQL is its driver for MySQL/MariaDB.
   • Key Features: Enables database operations via a unified API (such as the PDO and PDOStatement classes). Enforces the use of prepared statements (via prepare() and execute()), which is a best practice for preventing SQL injection. Supports transactions.
   • Main Advantages: Database portability is PDO’s biggest strength. If your application might switch to PostgreSQL, SQLite, or other supported databases in the future, PDO requires minimal code adjustments. Its object-oriented interface produces cleaner, safer, and often more readable code. For new projects, PDO is generally recommended.

PHP Code Examples: Connecting and Operating on Databases

The following examples illustrate how to connect to a ServBay-managed database (either MariaDB or MySQL) and perform basic operations in your PHP code using both mysqli and PDO.

Prerequisites:

• Ensure MySQL or MariaDB services are running in ServBay.
• Assume you have created a database named servbay_db.
• Assume you have created a user named servbay_user with password your_password, and granted appropriate privileges on the servbay_db database.
• Assume the servbay_db database contains a table named users with columns id (INT, PK, AI), name (VARCHAR), email (VARCHAR), and age (INT).

You can set these up using ServBay’s built-in phpMyAdmin or any other database management tool.

Example Code (Using mysqli)

<?php
// --- mysqli Example ---

// Database connection parameters
$servername = "127.0.0.1"; // or 'localhost'
$port = 3306; // Default port for MySQL/MariaDB in ServBay
$username = "servbay_user";
$password = "your_password"; // Replace with your actual password
$dbname = "servbay_db";

// Create a mysqli connection (object-oriented style)
$conn = new mysqli($servername, $username, $password, $dbname, $port);

// Check if the connection was successful
if ($conn->connect_error) {
    die("mysqli Connection failed: " . $conn->connect_error);
}
echo "mysqli Connected successfully<br>";

// Insert data (using prepared statements to prevent SQL injection)
$stmt = $conn->prepare("INSERT INTO users (name, email, age) VALUES (?, ?, ?)");
$name = "ServBay Demo";
$email = "[email protected]";
$age = 5;
// "ssi" indicates types: string, string, integer
$stmt->bind_param("ssi", $name, $email, $age);

if ($stmt->execute()) {
    echo "mysqli: New record created successfully<br>";
} else {
    echo "mysqli Error: " . $stmt->error . "<br>";
}
$stmt->close();

// Query data
$sql = "SELECT id, name, email, age FROM users WHERE name = 'ServBay Demo'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "mysqli Queried data:<br>";
    // Output data
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"]. " - Age: " . $row["age"]. "<br>";
    }
} else {
    echo "mysqli: 0 results found<br>";
}

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

Example Code (Using PDO)

<?php
// --- PDO Example ---

// Database connection parameters
$host = '127.0.0.1'; // or 'localhost'
$port = 3306; // Default port for MySQL/MariaDB in ServBay
$dbname = 'servbay_db';
$username = 'servbay_user';
$password = 'your_password'; // Replace with your actual password
$charset = 'utf8mb4';

// Data Source Name (DSN)
$dsn = "mysql:host=$host;port=$port;dbname=$dbname;charset=$charset";

// PDO connection options
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // Throw exceptions instead of warnings
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,       // Fetch associative arrays by default
    PDO::ATTR_EMULATE_PREPARES   => false,                  // Use native prepared statements
];

try {
    // Create the PDO instance
    $pdo = new PDO($dsn, $username, $password, $options);
    echo "PDO Connected successfully<br>";

    // Insert data (using prepared statements)
    $sql = "INSERT INTO users (name, email, age) VALUES (:name, :email, :age)";
    $stmt = $pdo->prepare($sql);

    $name = 'ServBay PDO Demo';
    $email = '[email protected]';
    $age = 10;

    // Bind parameters and execute
    $stmt->execute([
        ':name' => $name,
        ':email' => $email,
        ':age' => $age
    ]);
    echo "PDO: New record created successfully<br>";

    // Query data
    $stmt = $pdo->prepare("SELECT id, name, email, age FROM users WHERE name = :name");
    $stmt->execute([':name' => 'ServBay PDO Demo']);

    // Fetch all results
    $users = $stmt->fetchAll(); // Uses default FETCH_ASSOC

    if ($users) {
        echo "PDO Queried data:<br>";
        foreach ($users as $row) {
            echo "id: " . $row['id'] . " - Name: " . $row['name'] . " - Email: " . $row['email'] . " - Age: " . $row['age'] . "<br>";
        }
    } else {
        echo "PDO: 0 results found<br>";
    }

} catch (\PDOException $e) {
    // Catch and display connection or query errors
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

// The PDO connection will automatically close at the end of the script; no need to explicitly call close()
// $pdo = null; // You can set to null to force closure if you wish
?>

Placing the Code: Save either of the above examples as a .php file (such as db_test_mysqli.php or db_test_pdo.php) and place it in your ServBay site root directory (e.g., /Applications/ServBay/www/myproject/). Then open your browser and navigate to the corresponding URL (e.g., http://myproject.servbay.demo/db_test_mysqli.php). Ensure your site is properly configured and running in ServBay.

Frequently Asked Questions (FAQ)

Q: Do I need to manually install PHP MySQL extensions in ServBay?

A: No. ServBay comes with all PHP versions pre-installed and enabled with the critical mysqlnd, mysqli, and PDO_MySQL MySQL database extensions. You can use them directly in your PHP code without any manual installation or configuration.

Q: Which should I choose between mysqli and PDO?

A:

• PDO is recommended: If your application may need to support multiple database systems (not just MySQL/MariaDB), or you prefer more modern, object-oriented, and portable code, PDO is your best choice. It enforces the use of prepared statements, leading to more secure code.
• Consider mysqli: If your project will only ever use MySQL or MariaDB, and you want to take advantage of database-specific advanced features, mysqli is a powerful and efficient choice. It offers both object-oriented and procedural interfaces.

For new projects, PDO is generally the better choice.

Q: How do I determine the connection parameters for MySQL/MariaDB services in ServBay?

A:

• Host: Typically 127.0.0.1 or localhost.
• Port: Default port for MySQL/MariaDB in ServBay is usually 3306. You can check the exact port in ServBay’s package management interface.
• Username / Password: Use the database user credentials you created in MySQL/MariaDB. For first-time users, you might need to create a new user or use the root account (not recommended for production). You can manage users and privileges via ServBay’s built-in phpMyAdmin or other tools.
• Database Name: The name of the database you wish to connect to.

Q4: I have a legacy project using the deprecated mysql_* functions. Does ServBay support this?

A: Yes. To ease legacy development and migration, ServBay specifically includes support for the old mysql_* functions in its PHP 5.6 environment (such as mysql_connect). These functions were deprecated in PHP 5.5 and removed in PHP 7.0. With ServBay’s PHP 5.6, you can run code that depends on these legacy functions without hunting for even older PHP versions.

However, it is strongly recommended:

• Use this feature only for temporary compatibility or legacy project maintenance.
• For new projects or when refactoring old code, always switch to the more modern, secure mysqli or PDO extensions. The mysql_* functions are insecure (prone to SQL injection) and lack modern database features.

Conclusion

By pre-installing and enabling mysqlnd, mysqli, and PDO_MySQL extensions for all PHP versions—and providing compatibility for legacy mysql_* functions in PHP 5.6—ServBay dramatically simplifies the PHP developer’s database workflow. You don’t need to worry about the tedious process of installing and configuring extensions. Instead, you can immediately choose the extension best suited for your project (PDO or mysqli recommended) to connect to and operate on MySQL or MariaDB databases running in ServBay.

This out-of-the-box convenience allows you to focus on building your application’s logic, leveraging ServBay’s stable and efficient local environment to quickly develop, test, and maintain your web services.