Connect to OceanBase Database by using the MySQLi driver

This topic introduces how to build an application by using MySQLi and OceanBase Database. It also covers the use of the application for fundamental database operations, including table creation and data insertion.

MySQLi is a PHP extension that provides procedural or object-oriented database access methods.

Applicable versions

This content is applicable to all versions of OceanBase Database.

Prerequisites

• You have installed php and php-mysql.
• You have installed OceanBase Database and created a tenant of the MySQL-compatible mode.

Procedure

1. Check and install the php and php-mysql environment.
2. Create an obtest1.php sample file and configure the database connection information.
3. Run the obtest1.php file and perform verification.

Step 1: Check and install the php and php-mysql environment

For specific instructions on how to install the php and php-mysql environment, see Step 1: Check and install the php and php-mysql environment in Connect to OceanBase Database by using an EXT driver.

Step 2: Create an obtest1.php sample file and configure the database connection information

In the Linux environment, for example, you can use the command vi obtest1.php or vim obtest1.php to edit the obtest1.php file, and modify the database connection information in the file based on your specific needs.

Here is an example:

[root]# vim obtest1.php
<?php
$servername = "11.158.xx.xx";
$port = "6001";
$username = "root@mysql";
$password = "";
$dbname = "test";

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

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

// Create a table
$sql = "CREATE TABLE myguests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";
if ($conn->query($sql) === TRUE) {
    echo "Table myguests is created successfully";
} else {
    echo "Table creation error: " . $conn->error;
}

// Insert data
$sql = "INSERT INTO myguests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
if ($conn->query($sql) === TRUE) {
    echo "New record is inserted successfully";
} else {
    echo "Data insertion error: " . $conn->error;
}

// Query table
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // Output data
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
    }
} else {
    echo "0 result";
}

// Delete table
$sql = "DROP TABLE myguests";
if ($conn->query($sql) === TRUE) {
    echo "Table myguests is deleted successfully";
} else {
    echo "Table deletion error: " . $conn->error;
}

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

where

• $servername indicates the IP address of the MySQL server.
• $port indicates the port number of the MySQL server.
• $username indicates the MySQL username for connecting to the database.
• $password indicates the MySQL password for connecting to the database.
• $dbname indicates the name of the database to connect to.

Step 3: Run the obtest.php file and perform verification

Execute the php obtest1.php command to run the obtest1.php file and check whether the connection to OceanBase Database is successful.

Here is an example:

[root]# php obtest1.php
Table myguests is created successfully. New record is inserted successfully. id: 1 - Name: John Doe<br>Table myguests is deleted successfully.