Skip to main content

Use the PDO driver to connect to seekdb

This topic describes how to use the PDO driver to connect to seekdb and perform basic operations such as table creation and data insertion. PDO stands for PHP Data Objects. It is a lightweight, consistent interface for providing data access abstraction in PHP applications.

Prerequisites​

  • You have installed PHP and php-mysql.
  • You have installed seekdb.

Procedure​

  1. Check and install the PHP and php-mysql environment.
  2. Create a podtest.php sample file and configure the database connection information.
  3. Run the podtest.php file and verify the connection.

Step 1: Check and install the PHP and php-mysql environment​

Install the PHP and php-mysql environment. For more information, see Ext driver connects to seekdb Step 1: Check and install the PHP and php-mysql environment.

Step 2: Modify the database connection information in the podtest.php file​

In a Linux environment, you can use the vi podtest.php or vim podtest.php command to edit the podtest.php file and modify the database connection information to match your actual setup.

[root]# vim podtest.php
<?php
$servername = "172.30.xx.xxx";
$port = "2881";
$username = "root";
$password = "xxxxxxx";
$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();
}
?>

Where:

  • $servername specifies the seekdb server address.
  • $port specifies the seekdb server port number.
  • $username specifies the seekdb username.
  • $password specifies the password for connecting to seekdb.
  • $dbname specifies the name of the database to connect to.

Step 3: Run the podtest.php file and verify the connection​

In the podtest.php file, intentionally enter an incorrect database name. When you execute the php podtest.php command, you will see the following error message:

[root]# php podtest.php
Connection failed: SQLSTATE[42000] [1049] Unknown database 'test123'

Enter the correct information and you will see the following message:

[root]# php podtest.php
Connection successful

Test the table creation and CRUD operations. Here is an example:

[root]# vim podtest.php
<?php
$servername = "172.30.xxx.xxx"; // seekdb server address
$port = "2881"; // seekdb server port number
$username = "root"; // seekdb username
$password = ""; // seekdb connection password
$dbname = "test"; // name of the database to connect to

// 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 "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 inserted successfully";
} catch(PDOException $e) {
    echo "Data insertion error: " . $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 "Table query error: " . $e->getMessage();
}

// Delete the table
try {
    $sql = "DROP TABLE myguests";
    $conn->exec($sql);
    echo "Table myguests deleted successfully";
} catch(PDOException $e) {
    echo "Table deletion error: " . $e->getMessage();
}

// Close the connection
$conn = null;
?>

The execution is successful, and the following output is returned:

[root]# php podtest.php
Connection successful Table myguests created successfully New record inserted successfully id: 1 - Name: John Doe<br>Table myguests deleted successfully

Contents

Edit this page