This topic provides instructions on how to connect to OceanBase Cloud using PHP, using MySQL EXT driver, MySQLi driver, and PDO driver as examples. Basic operations such as creating tables, inserting data, and querying data will be covered.
Prerequisites
You have registered an OceanBase Cloud account and have created a cluster instance. For details, refer to Create a cluster instance.
Procedure
Step 1: Obtain an OceanBase Cloud connection string
Log in to the OceanBase Cloud console. On the Instances page, expand the target instance and click Create under the target instance.
In the pop-up window, click Connect with Public IP.
In the Connect with Public IP window, complete the following settings to generate the connection string:
- Under 1. Add an IP address to the allowlist, click Add to add your exit IP address(es) used for the connection to the allowlist.
- (Optional) Under 2. Download the CA certificate to connect securely to the instance, download the CA certificate and complete the verification.
- Under 3. Connect to your instance, click the drop-down list for Database and Account to create a database and an account for the connection. Select MySQL CLI as the connection method.
Notice
Please keep your password in a secure place after creating your account.
Step 2: Check and install PHP and php-mysql environment
Execute the following commands in a Linux environment to install the PHP environment.
sudo yum install php sudo yum install php-mysql sudo yum install php-mysqliExecute the following command to check if the installation was successful.
[root]# php -v PHP 5.4.16 (cli) (built: Dec 13 2015 00:05:14) Copyright (c) 1997-2013 The PHP Group Zend Engine v2.4.0, Copyright (c) 1998-2013 Zend Technologies
Step 3: Modify database connection information in the code file and run the application
Below is an example using a file named obtest.php to demonstrate how to add the connection string information obtained in Step 1: Obtain an OceanBase Cloud connection string to the code file of the application. You can use the code file of your choice.
MySQL EXT Driver
MySQLi Driver
PDO Driver
Modify database connection information in the obtest.php file
The sample code for the database connection section is as follows:
[root]# vim obtest.php
<?php
$servername = "t5******.aws-ap-southeast-1.oceanbase.cloud";
$port = "3306";
$username = "test";
$password = "xxxxxxx";
$dbname = "test";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname, $port);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
?>
$servername: Taken from the-hparameter in the connection string, which is the hostname of OceanBase Cloud database, for example,t5******.aws-ap-southeast-1.oceanbase.cloud.$port: Taken from the-Pparameter in the connection string, which is the OceanBase Cloud database connection port.$username: Taken from the-uparameter in the connection string, which is the account name, for example,test.$password: Taken from the-pparameter in the connection string, which is the account password.$dbname: Taken from the-Dparameter in the connection string, which is the name of the database to be accessed.
The complete sample code of the obtest.php file is as follows:
[root]# vim obtest.php
<?php
$servername = "t5******.aws-ap-southeast-1.oceanbase.cloud";
$port = "3306";
$username = "test";
$password = "xxxxxxx";
$dbname = "test";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname, $port);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// Create 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 (mysqli_query($conn, $sql)) {
echo "Table myguests created successfully";
} else {
echo "Error creating table: " . mysqli_error($conn);
}
// Insert data
$sql = "INSERT INTO myguests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
if (mysqli_query($conn, $sql)) {
echo "New record inserted successfully";
} else {
echo "Error inserting data: " . mysqli_error($conn);
}
// Close connection
mysqli_close($conn);
?>
Run obtest.php
Execute the php obtest.php command to run the file. The following result is returned, indicating that the database connection is successful and operations such as table creation and data insertion are successful.
[root]# php obtest.php
Table myguests created successfullyNew record inserted successfullyid: 1 - Name: John Doe<br>Table myguests deleted successfully
Modify database connection information in the obtest.php file
The sample code for the database connection section is as follows:
[root]# vim obtest.php
<?php
$servername = "t5******.aws-ap-southeast-1.oceanbase.cloud";
$port = "3306";
$username = "test";
$password = "******";
$dbname = "test";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname, $port);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>
$servername: Taken from the-hparameter in the connection string, which is the hostname of OceanBase Cloud database, for example,t5******.aws-ap-southeast-1.oceanbase.cloud.$port: Taken from the-Pparameter in the connection string, which is the OceanBase Cloud database connection port.$username: Taken from the-uparameter in the connection string, which is the account name, for example,test.$password: Taken from the-pparameter in the connection string, which is the account password.$dbname: Taken from the-Dparameter in the connection string, which is the name of the database to be accessed.
The complete sample code for the obtest.php file is as follows:
[root]# vim obtest.php
<?php
$servername = "t5******.aws-ap-southeast-1.oceanbase.cloud";
$port = "3306";
$username = "test";
$password = "******";
$dbname = "test";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname, $port);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Create 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 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";
}
// Delete table
$sql = "DROP TABLE myguests";
if ($conn->query($sql) === TRUE) {
echo "Table myguests deleted successfully";
} else {
echo "Error deleting table: " . $conn->error;
}
// Close connection
$conn->close();
?>
Run obtest.php
Execute the php obtest.php command to run the file. The following result is returned, indicating that the database connection is successful and operations such as table creation, data insertion, and querying are successful.
[root]# php obtest.php
Table myguests created successfullyNew record inserted successfullyid: 1 - Name: John Doe<br>Table myguests deleted successfully
Modify database connection information in the obtest.php file
The sample code for the database connection section is as follows:
[root]# vim obtest.php
<?php
$servername = "t5******.aws-ap-southeast-1.oceanbase.cloud";
$port = "3306";
$username = "test";
$password = "******";
$dbname = "test";
// Create connection
try {
$conn = new PDO("mysql:host=$servername;port=$port;dbname=$dbname", $username, $password);
// Set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connection successful";
} catch(PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
?>
$servername: Taken from the-hparameter in the connection string, which is the hostname of OceanBase Cloud database, for example,t5******.aws-ap-southeast-1.oceanbase.cloud.$port: Taken from the-Pparameter in the connection string, which is the OceanBase Cloud database connection port.$username: Taken from the-uparameter in the connection string, which is the account name, for example,test.$password: Taken from the-pparameter in the connection string, which is the account password.$dbname: Taken from the-Dparameter in the connection string, which is the name of the database to be accessed.
The complete sample code for the obtest.php file is as follows:
[root]# vim obtest.php
<?php
$servername = "t5******.aws-ap-southeast-1.oceanbase.cloud";
$port = "3306";
$username = "test";
$password = "******";
$dbname = "test";
// Create connection
try {
$conn = new PDO("mysql:host=$servername;port=$port;dbname=$dbname", $username, $password);
// Set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connection successful";
} catch(PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
// Create table
try {
$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
)";
$conn->exec($sql);
echo "Table myguests created successfully";
} catch(PDOException $e) {
echo "Error creating table: " . $e->getMessage();
}
// Insert data
try {
$sql = "INSERT INTO myguests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
$conn->exec($sql);
echo "New record inserted successfully";
} catch(PDOException $e) {
echo "Error inserting data: " . $e->getMessage();
}
// Query table
try{
$sql = "SELECT id, firstname, lastname FROM myguests";
$stmt = $conn->prepare($sql);
$stmt->execute();
$result = $stmt->setFetchMode
(PDO::FETCH_ASSOC);
if ($stmt->rowCount() > 0) {
// Output data
while($row = $stmt->fetch()) {
echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}
} else {
echo "0 results";
}
} catch(PDOException $e) {
echo "Error querying table: " . $e->getMessage();
}
// Delete table
try {
$sql = "DROP TABLE myguests";
$conn->exec($sql);
echo "Table myguests deleted successfully";
} catch(PDOException $e) {
echo "Error deleting table: " . $e->getMessage();
}
// Close connection
$conn = null;
?>
Run obtest.php
Execute the php obtest.php command to run the file. The following result is returned, indicating that the database connection is successful and operations such as table creation, data insertion, querying, and table deletion are successful.
[root]# php obtest.php
Connection successfulTable myguests created successfullyNew record inserted successfullyid: 1 - Name: John Doe<br>Table myguests deleted successfully