This topic introduces how to build an application by using PDO and OceanBase Database. It also covers the use of the application for fundamental database operations, including table creation and data insertion.
PDO, short for PHP Data Objects, is a lightweight, consistent interface used in PHP applications to provide a data access abstraction layer.
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
- Check and install the php and php-mysql environment.
- Create a
podtest.phpsample file and configure the database connection information. - Run the
podtest.phpfile 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 a podtest.php sample file and configure the database connection information
In the Linux environment, for example, you can use the command vi podtest.php or vim podtest.php to edit the podtest.php file, and modify the database connection information in the file based on your specific needs.
Here is an example:
[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 "EXCEPTION"
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connection succeeded";
} catch(PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
?>
where
$servernameindicates the IP address of the MySQL server.$portindicates the port number of the MySQL server.$usernameindicates the MySQL username for connecting to the database.$passwordindicates the MySQL password for connecting to the database.$dbnameindicates the name of the database to connect to.
Step 3: Run the podtest.php file and perform verification
If you provide an incorrect database name in the podtest.php file, the following error message will be reported when you execute the php podtest.php command:
[root]# php podtest.php
Connection failed: SQLSTATE[42000] [1049] Unknown database 'test123'
If the information is correct, the following information will be displayed, indicating that the connection is successful:
[root]# php podtest.php
Connection succeeded
Create a table and perform CRUD operations for testing. Here is an example:
[root]# vim podtest.php
<?php
$servername = "172.30.xxx.xxx"; // IP address of the MySQL server
$port = "2881"; // Port number of the MySQL server
$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 "EXCEPTION"
$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 is created successfully";
} catch(PDOException $e) {
echo "Table creation error: " . $e->getMessage();
}
// Insert data
try {
$sql = "INSERT INTO myguests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
$conn->exec($sql);
echo "New record is inserted successfully";
} catch(PDOException $e) {
echo "Data insertion error: " . $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 result";
}
} catch(PDOException $e) {
echo "Table query error: " . $e->getMessage();
}
// Delete table
try {
$sql = "DROP TABLE myguests";
$conn->exec($sql);
echo "Table myguests is deleted successfully";
} catch(PDOException $e) {
echo "Table deletion error: " . $e->getMessage();
}
// Close the connection
$conn = null;
?>
The following information will be returned if the execution is successful:
[root]# php podtest.php
Connection succeeded. Table myguests is created successfully. New record is inserted successfully. id: 1 - Name: John Doe<br>Table myguests is deleted successfully.