This topic describes how to connect to OceanBase Cloud using the PDO driver and perform basic operations such as creating tables and inserting data. PDO, short for PHP Data Objects, is a lightweight, consistent interface that provides a data access abstraction layer in PHP applications.
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 details, see Create a cluster instance and Create a tenant.
Procedure
- Check and install the PHP and php-mysql environment.
- Create a sample file named
podtest.phpand configure the database connection information. - Run the
podtest.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 using the Ext driver section.
Step 2: Modify the database connection information in the podtest.php file
In a Linux environment, for example, you can use the vi podtest.php or vim podtest.php command to edit the podtest.php file and modify the database connection information in the file to match your actual configuration.
[root]# vim podtest.php
<?php
$servername = "t********.********.oceanbase.cloud";
$port = "3306";
$username = "mysql001";
$password = "********";
$dbname = "test";
// Create a connection
try {
$conn = new PDO("mysql:host=$servername;port=$port;dbname=$dbname", $username, $password);
// Set the PDO error mode to exceptions
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connection succeeded";
} catch(PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
?>
Where:
$servername: the connection address of OceanBase Cloud.$port: the connection port of OceanBase Cloud. The default value is 3306.$username: the account for accessing the database.$password: the password of the account.$dbname: the name of the database.
Step 3: Run the podtest.php file and verify the results
Intentionally enter an incorrect database name in the podtest.php file. Running the php podtest.php command returns the following error message:
[root]# php podtest.php
Connection failed: SQLSTATE[42000] [1049] Unknown database 'test123'
Enter the correct information to get a success message:
[root]# php podtest.php
Connection succeeded
Test table creation and CRUD operations. Example:
[root]# vim podtest.php
<?php
$servername = "t********.********.oceanbase.cloud"; // Server address
$port = "3306";
$username = "mysql001"; // Username
$password = "********"; // Password
$dbname = "test"; // Name of the database to connect to
// Create a connection.
try {
$conn = new PDO("mysql:host=$servername;port=$port;dbname=$dbname", $username, $password);
// Set the PDO error mode to exceptions.
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connection succeeded";
} catch(PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
// Create a 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 the 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();
}
// Drop the table.
try {
$sql = "DROP TABLE myguests";
$conn->exec($sql);
echo "Table myguests deleted successfully";
} catch(PDOException $e) {
echo "Error dropping table: " . $e->getMessage();
}
// Close the connection.
$conn = null;
?>
Upon successful execution, the following example output is returned:
[root]# php podtest.php
Connection succeeded. Table myguests created successfully. New record inserted successfully. id: 1 - Name: John Doe<br>Table myguests deleted successfully