PHP Use PDO To Execute SQL Statements

PDO (PHP Data Objects) in PHP provides a uniform interface for accessing various databases. Once you have established a connection to your database using PDO, you can execute SQL queries using this connection.

Here's an example of using PDO to execute a simple SQL statement:

// Assuming you have already created a PDO instance $pdo
$stmt = $pdo->query('SELECT * FROM users');

In this example, we're executing a SQL query to select all records from the users table. The query method returns a PDOStatement object.

If you need to run a SQL command like INSERT, UPDATE, or DELETE, you can use the exec method:

$count = $pdo->exec("INSERT INTO users (firstname, lastname) VALUES ('John', 'Doe')");
echo "Inserted $count row(s)";

The exec method executes an SQL statement and returns the number of rows affected by the statement.

However, when you're executing a SQL query that includes user-supplied data, you should use prepared statements to prevent SQL injection attacks. Here's an example:

$stmt = $pdo->prepare('INSERT INTO users (firstname, lastname) VALUES (?, ?)');
$stmt->execute(['John', 'Doe']);

In this example, the prepare method prepares the SQL statement for execution and returns a PDOStatement object. The ?s in the SQL query are placeholders that get replaced with the values in the array passed to the execute method.

You can also use named parameters instead of ? placeholders:

$stmt = $pdo->prepare('INSERT INTO users (firstname, lastname) VALUES (:firstname, :lastname)');
$stmt->execute([':firstname' => 'John', ':lastname' => 'Doe']);

In this example, :firstname and :lastname are named parameters that get replaced with the corresponding values in the array passed to the execute method.

Remember to always handle potential exceptions that might be thrown when executing SQL queries with PDO.

Executing prepared statements with PDO in PHP:

Use prepare() to create a prepared statement and execute() to execute it.

$sql = "SELECT * FROM users WHERE username = :username";
$stmt = $pdo->prepare($sql);

$username = "john_doe";
$stmt->bindParam(':username', $username);

$stmt->execute();

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

Binding parameters in PDO prepared statements:

Use bindParam() or bindValue() to bind parameters.

$sql = "SELECT * FROM users WHERE id = :id";
$stmt = $pdo->prepare($sql);

$id = 1;
$stmt->bindParam(':id', $id, PDO::PARAM_INT);

$stmt->execute();

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

Inserting, updating, and deleting records with PDO:

Use prepared statements for safe insert, update, and delete operations.

// Insert
$sqlInsert = "INSERT INTO users (username, email) VALUES (:username, :email)";
$stmtInsert = $pdo->prepare($sqlInsert);

$username = "john_doe";
$email = "john@example.com";

$stmtInsert->bindParam(':username', $username);
$stmtInsert->bindParam(':email', $email);

$stmtInsert->execute();

// Update
$sqlUpdate = "UPDATE users SET email = :newEmail WHERE username = :username";
$stmtUpdate = $pdo->prepare($sqlUpdate);

$newEmail = "new_email@example.com";

$stmtUpdate->bindParam(':newEmail', $newEmail);
$stmtUpdate->bindParam(':username', $username);

$stmtUpdate->execute();

// Delete
$sqlDelete = "DELETE FROM users WHERE username = :username";
$stmtDelete = $pdo->prepare($sqlDelete);

$stmtDelete->bindParam(':username', $username);

$stmtDelete->execute();

Executing raw SQL queries with PDO:

Use query() for raw SQL queries without parameters.

$sql = "SELECT * FROM users";
$result = $pdo->query($sql);

while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    echo $row['username'] . "<br>";
}

Handling multiple statements in a single PDO execution:

Use exec() to execute multiple statements in a single call.

$sql = "
    INSERT INTO users (username, email) VALUES ('user1', 'user1@example.com');
    INSERT INTO users (username, email) VALUES ('user2', 'user2@example.com');
";

$affectedRows = $pdo->exec($sql);

Managing transactions with PDO in PHP:

Use beginTransaction(), commit(), and rollBack() for transactions.

try {
    $pdo->beginTransaction();

    // SQL queries here

    $pdo->commit();
} catch (PDOException $e) {
    $pdo->rollBack();
    echo "Transaction failed: " . $e->getMessage();
}

Error handling and exception management in PDO executions:
- Use try-catch blocks for error handling.
```
try {
    // PDO code here
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
```

Securely executing user input with PDO in PHP:

Always use prepared statements to prevent SQL injection.

$sql = "SELECT * FROM users WHERE username = :username";
$stmt = $pdo->prepare($sql);

$username = $_POST['username'];
$stmt->bindParam(':username', $username);

$stmt->execute();

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