PHP and PDO: How to execute SQL queries

PHP and PDO: How to execute SQL query statements

Overview:
PHP is a popular server-side scripting language that is widely used to develop web applications. In the process of developing web applications, interaction with databases is a very common requirement. In PHP, PDO (PHP Data Objects) is a powerful PHP extension for interacting with databases. This article will introduce how to use PHP and PDO to execute SQL query statements.

1. Connect to the database
   Before using PDO to execute SQL queries, you first need to connect to the database. You can use the PDO constructor to create a new PDO object and provide the connection information for the database as follows:

$dsn = 'mysql:host=localhost;dbname=test';
$username = 'root';
$password = '';

try {
    $pdo = new PDO($dsn, $username, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

The above code will use MySQL as the database example and connect to the database named test database. In actual use, the corresponding connection information needs to be modified according to the actual situation.

2. Execute SQL query statement
   After the connection is successful, you can use the query() method of the PDO object to execute the SQL query statement. For example, execute a simple SELECT statement to obtain all user records, as shown below:

$stmt = $pdo->query('SELECT * FROM users');
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($result as $row) {
    echo $row['name'] . ' - ' . $row['email'] . '<br>';
}

The above code executes a SELECT statement to obtain all records from the users table , and store the result in the $result variable. Then, by looping through the result set, output the 'name' and 'email' field values ​​of each row.

3. Use bound parameters
   When executing SQL query statements, in order to avoid security issues such as SQL injection attacks, it is recommended to use bound parameters. PDO provides the prepare() method to prepare SQL statements and execute them by binding parameters. For example, execute a SELECT statement with parameters to obtain the records of the specified user, as shown below:

$stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id');
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->execute();

$result = $stmt->fetch(PDO::FETCH_ASSOC);

echo $result['name'] . ' - ' . $result['email'];

The above code prepares a SELECT statement with parameters, where :id is parameter placeholder. Then, bind the actual parameter value to the placeholder through the bindParam() method, and specify the data type of the parameter. Finally, call the execute() method to execute the SQL query statement, and obtain the result of a row of records through the fetch() method.

Summary:
This article introduces how to use PHP and PDO to execute SQL query statements. First, you need to connect to the database using a PDO object. Then, you can use the query() method to execute the SQL query, or use the prepare() method to prepare the SQL statement and execute it using bound parameters. For simple queries, use the query() method; for queries that include parameters, it is a better choice to use the prepare() method and bind the parameters. By understanding and mastering the use of PDO, you can interact with the database more safely and efficiently.

The above is the detailed content of PHP and PDO: How to execute SQL queries. For more information, please follow other related articles on the PHP Chinese website!