This topic describes how to connect to OceanBase Cloud by using the MySQLi driver and perform basic operations such as creating tables and inserting data. MySQLi is an extension for PHP that provides procedural or object-oriented database access methods.
Applicable versions
All versions of OceanBase Cloud.
Prerequisites
- You have installed PHP and php-mysql.
- You have registered an OceanBase Cloud account and created a cluster instance and a tenant in MySQL mode. For more information, see Create a cluster instance and Create a tenant.
Procedure
- Check and install the PHP and php-mysql environment.
- Create a sample file named
obtest1.phpand configure the database connection information. - Run the
obtest1.phpfile and verify the results.
Step 1: Check and install the PHP and php-mysql environment
Install the PHP and php-mysql environment. For specific operations, refer to Step 1: Check and install the PHP and php-mysql environment in the Connect to OceanBase Cloud by using the Ext driver section.
Step 2: Modify the database connection information in the obtest1.php file
In a Linux environment, for example, you can use the vi obtest1.php or vim obtest1.php command to edit the obtest1.php file and modify the database connection information in the file to match your actual configuration.
Example:
[root]# vim obtest1.php
<?php
$servername = "t********.********.oceanbase.cloud";
$port = "3306";
$username = "mysql001";
$password = "********";
$dbname = "test";
// Create a connection
$conn = new mysqli($servername, $username, $password, $dbname, $port);
// Check if the connection succeeded
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 created successfully";
} else {
echo "Error creating table: " . $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 inserted successfully";
} else {
echo "Error inserting data: " . $conn->error;
}
// Query the 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 results";
}
// Drop the table.
$sql = "DROP TABLE myguests";
if ($conn->query($sql) === TRUE) {
echo "Table myguests dropped successfully";
} else {
echo "Error dropping table: " . $conn->error;
}
// Close the connection.
$conn->close();
?>
Where:
$servername: the connection address of OceanBase Cloud.$port: the port for connecting to OceanBase Cloud. The default value is 3306.$username: the account used to access the database.$password: the account password.$dbname: the name of the database.
Step 3: Run the obtest1.php file and verify the results
Run the php obtest1.php command to execute the obtest1.php file and verify that basic operations such as connecting to OceanBase Cloud, creating tables, and inserting data are successful.
An example is as follows:
[root]# php obtest1.php
The table myguests was created successfully. New record inserted successfully. id: 1 - Name: John Doe<br>The table myguests was deleted successfully.