Getting Started with PHP: PDO Database

PHP is a very popular server-side programming language. Its flexibility and ease of use make it one of the preferred languages ​​for web development. In web application development, the process of interacting with databases is very important. PDO (PHP Data Object) is an extension library of PHP that provides a common interface to interact with various relational databases, including MySQL, PostgreSQL, Oracle, etc. This article will introduce how to use PDO to operate a database and is suitable for beginners.

1. Install PDO extension

Before using PDO, you need to confirm whether PHP has installed the PDO extension. You can view the PHP extension list through the phpinfo() function. If there is no PDO extension in the extension list, you need to set extension=pdo.so and extension=pdo_mysql.so in the php.ini file, and then restart the web server to enable the PDO extension.

2. Connecting to the database

It is very convenient to use PDO to connect to the database. You only need to provide relevant information about the database. The following is a sample code for connecting to a MySQL database:

$dbname = "test";
$host = "localhost";
$user = "root";
$pass = "password";

$dsn = "mysql:host=" . $host . ";dbname=" . $dbname;
$options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8");

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

In the above code, the relevant information of the database is first set, including the database name, host, user name and password. Then, use DSN (Data Source Name) to transfer the database information to PDO. Three options are set in the options array:

• ATTR_ERRMODE, which represents the PDO error handling mode and uses exceptions to throw error messages.
• ATTR_DEFAULT_FETCH_MODE, indicates how to obtain the result set, the default is an associative array.
• MYSQL_ATTR_INIT_COMMAND means initializing the SQL command and setting the character set to utf8.

The try-catch block captures exceptions when connecting to the database and outputs error information.

3. Execute SQL statements

After successfully connecting to the database, you can execute SQL statements to perform data operations. PDO provides three main PDO statement objects:

• PDO::query(), which executes a SQL query statement and returns a result set. Applies to SELECT statements.
• PDO::prepare(), prepares SQL statements. Applicable to SELECT, UPDATE, INSERT, DELETE and other statements.
• PDO::exec(), executes a SQL statement and returns the number of affected rows. Applicable to UPDATE, INSERT, DELETE and other statements.

The following is a sample code that uses PDO::query() to query records and output:

$sql = "SELECT * FROM user";
try {
    $stmt = $pdo->query($sql);
    while ($row = $stmt->fetch()) {
        echo "Name: " . $row['name'] . "<br>";
        echo "Age: " . $row['age'] . "<br>";
    }
} catch (PDOException $e) {
    echo "Query failed: " . $e->getMessage();
}

In the above code, a SQL query statement is first defined to query the user table record of. Then use the query() method to execute the SQL statement, and the $stmt variable returns the PDOStatement object containing the query results. Then use the fetch() method to traverse the result set and output the name and age recorded in each row. If an error occurs while executing the query, the try-catch block catches the exception and outputs the error message.

The following is a sample code for inserting records using PDO::prepare():

$sql = "INSERT INTO user(name, age) VALUES(:name, :age)";
try {
    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(':name', $name);
    $stmt->bindParam(':age', $age);

    $name = "Alice";
    $age = 30;
    $stmt->execute();

    echo "Record inserted successfully.";
} catch (PDOException $e) {
    echo "Insert failed: " . $e->getMessage();
}

In the above code, a SQL insert statement is first defined to insert a record into the user table. Use the prepare() method to preprocess SQL statements, and the $stmt variable returns the PDOStatement object containing the prepared statement. Then use the bindParam() method to bind the placeholder with the actual parameters. Then set the parameter values, and finally use the execute() method to execute the SQL statement. If an error occurs while performing the insertion, the try-catch block catches the exception and outputs an error message.

4. Close the database connection

When the database connection is no longer needed, the database connection should be closed in time to release resources. You can use the PDO::null() method to close, for example:

$pdo = null;

The above is a simple example of using PDO for database operations. Using PDO is not only convenient, but also highly portable and secure. By learning the methods described in this article, you can quickly get started and start using PDO for your own web application development.

The above is the detailed content of Getting Started with PHP: PDO Database. For more information, please follow other related articles on the PHP Chinese website!