This topic describes how to connect to OceanBase AI Database by 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 AI Database.
Prerequisites
- You have installed php and php-mysql.
- You have installed OceanBase AI Database and created a user 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 AI Database by 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 = "172.30.xx.xxx";
$port = "2881";
$username = "root@mysql";
$password = "xxxxxxx";
$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();
}
?>
wherein:
$servernameis the address of the MySQL server.$portis the port number of the MySQL server.$usernameis the username for MySQL.$passwordis the password for connecting to MySQL.$dbnameis the name of the database to connect to.
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 will return the following error message:
[root]# php podtest.php
Connection failed: SQLSTATE[42000] [1049] Unknown database 'test123'
Enter the correct information, and a success message is displayed:
[root]# php podtest.php
Connection succeeded
Test table creation and CRUD operations. Example:
[root]# vim podtest.php
<?php
$servername = "172.30.xxx.xxx"; // MySQL server address
$port = "2881"; // MySQL server port number
$username = "root@mysql"; // MySQL username
$password = ""; // MySQL 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 dropped successfully";
} catch(PDOException $e) {
echo "Error dropping table: " . $e->getMessage();
}
// Close the connection
$conn = null;
?
If the execution is successful, 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.