How to Use ServBay's Built-In MySQL (MariaDB) Module

As a powerful integrated web development tool, ServBay comes with a MySQL module that is very easy to enable. MariaDB is a fork of MySQL, fully compatible with MySQL, and widely used in modern web development. With ServBay, developers can easily enable the MySQL module to use MariaDB for data storage and management in PHP applications.

Differences Between MySQL and MariaDB

Both MySQL and MariaDB are popular relational database management systems (RDBMS), but there are some differences:

1. Origin:

   • MySQL: Developed by MySQL AB, later acquired by Sun Microsystems, and eventually by Oracle.
   • MariaDB: Created by MySQL's founder Michael Widenius as a fork of MySQL, aiming to remain open-source and free.

2. Compatibility:

   • MySQL: Widely used and relied upon by many large applications and enterprise systems.
   • MariaDB: Fully compatible with MySQL, supporting almost all MySQL features and syntax.

3. Performance and Features:

   • MySQL: May have better optimization and performance in certain specific scenarios.
   • MariaDB: Driven by the open-source community, introducing many new features and performance improvements such as better thread pooling, virtual columns, and storage engines.

Version of MySQL (MariaDB) Module Integrated with ServBay

ServBay supports multiple PHP versions, and for each version, the corresponding MySQL module is pre-installed and enabled by default. Specifically, ServBay includes the following three MySQL modules:

1. MySQL Native Driver (mysqlnd): PHP's native driver for communicating with MySQL databases, offering better performance and memory management.
2. MySQLi: MySQL Improved Extension, providing both object-oriented and procedural interfaces, supporting prepared statements, transactions, stored procedures, and multiple queries.
3. PDO (PHP Data Objects): A lightweight, object-oriented database access interface supporting multiple databases, including MySQL, PostgreSQL, SQLite, etc.

How to Enable the MySQL Module

By default, the MySQL module is enabled with no additional configuration required.

Differences Among the Three MySQL Modules

1. MySQL Native Driver (mysqlnd):

   • Introduction: mysqlnd is a native PHP driver for communicating with MySQL databases.
   • Features: It provides better performance and memory management and is the default driver for PHP 5.3 and above.
   • Advantages: More efficient memory usage, faster query execution, support for asynchronous queries, and plugin-based extensibility.

2. MySQLi:

   • Introduction: MySQL Improved Extension (MySQLi) is an enhanced version of the MySQL extension, providing both object-oriented and procedural interfaces.
   • Features: Supports prepared statements, transactions, stored procedures, and multiple queries.
   • Advantages: Safer query execution (preventing SQL injection), richer functionality, and better performance.

3. PDO (PHP Data Objects):

   • Introduction: PDO is a lightweight, object-oriented database access interface supporting multiple databases, including MySQL, PostgreSQL, SQLite, etc.
   • Features: Provides a unified API interface, supporting prepared statements and transactions.
   • Advantages: Cross-database compatibility, safer query execution (preventing SQL injection), and more concise code.

Using MySQL in PHP Code

Once the MySQL module is enabled, you can use either MySQLi or PDO in your PHP code to perform database operations. Below are two simple examples, one using the standard MySQLi method and the other using the PDO method.

Example Code (Standard MySQLi Method)

<?php
// Connect to MariaDB database
$conn = new mysqli('localhost', 'servbay_user', 'your_password', 'servbay_db');

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

// Insert data
$sql = "INSERT INTO users (name, email, age) VALUES ('ServBay', '[email protected]', 30)";
if ($conn->query($sql) === TRUE) {
    echo "Data inserted successfully";
} else {
    echo "Error in inserting data: " . $conn->error;
}

// Query data
$sql = "SELECT * FROM users WHERE name = 'ServBay'";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "Queried data: ";
        print_r($row);
    }
} else {
    echo "No results found";
}

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

Example Code (PDO Method)

<?php
try {
    // Create a new PDO instance
    $dsn = 'mysql:host=localhost;dbname=servbay_db';
    $username = 'servbay_user';
    $password = 'your_password';
    $options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];

    $pdo = new PDO($dsn, $username, $password, $options);

    // Insert data
    $stmt = $pdo->prepare("INSERT INTO users (name, email, age) VALUES (:name, :email, :age)");
    $stmt->execute([
        ':name' => 'ServBay',
        ':email' => '[email protected]',
        ':age' => 30
    ]);
    echo "Data inserted successfully";

    // Query data
    $stmt = $pdo->prepare("SELECT * FROM users WHERE name = :name");
    $stmt->execute([':name' => 'ServBay']);
    $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo "Queried data: ";
    print_r($data);

} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

// Close database connection (PDO will automatically close the connection)
?>

Conclusion

ServBay provides a convenient way to manage and enable the MySQL module. With simple configuration and restart operations, developers can quickly enable the MySQL module across different PHP versions, allowing them to use MariaDB for data storage and management in PHP applications. MariaDB's high performance, powerful querying capabilities, and data integrity make it an indispensable database solution in modern web development. With ServBay and MariaDB, developers can build efficient and reliable web applications.