{"id":404,"date":"2021-01-30T22:00:00","date_gmt":"2021-01-30T22:00:00","guid":{"rendered":"https:\/\/rishikantsri.in\/blog\/?p=404"},"modified":"2023-10-12T06:51:28","modified_gmt":"2023-10-12T06:51:28","slug":"php-pdo-for-database-operations","status":"publish","type":"post","link":"https:\/\/rishikantsri.in\/blog\/php-pdo-for-database-operations\/","title":{"rendered":"PHP PDO for Database Operations"},"content":{"rendered":"\n

PHP Data Objects (PDO) is a robust and versatile database abstraction layer that provides a consistent and secure interface for interacting with various databases. It’s an essential tool for PHP developers when working with databases. In this comprehensive guide, we’ll cover the latest usage, syntax, error handling, and security considerations, as well as the pros and cons of using PHP PDO. Additionally, we’ll provide a complete example of creating, reading, updating, and deleting (CRUD) operations to help you get started with PDO.<\/p>\n\n\n\n

<\/p>\n\n\n\n

PDO Syntax<\/h2>\n\n\n\n

Let’s start by examining the core syntax of PHP PDO, which allows you to connect to databases and execute SQL queries. Below are the fundamental components of the PDO syntax.<\/p>\n\n\n\n

1. Database Connection<\/h3>\n\n\n\n

To establish a connection to a database, you need to provide the Data Source Name (DSN), a username, and a password. Additionally, you can set options for error handling and other configurations.<\/p>\n\n\n\n

PHP<\/span><\/span><\/path><\/path><\/svg><\/span>
$dsn <\/span>=<\/span> <\/span>"mysql:host=localhost;dbname=mydatabase"<\/span>;<\/span><\/span>\n$username <\/span>=<\/span> <\/span>"username"<\/span>;<\/span><\/span>\n$password <\/span>=<\/span> <\/span>"password"<\/span>;<\/span><\/span>\n$options <\/span>=<\/span> [<\/span>PDO<\/span>::<\/span>ATTR_ERRMODE <\/span>=><\/span> <\/span>PDO<\/span>::<\/span>ERRMODE_EXCEPTION];<\/span><\/span>\n<\/span>\ntry<\/span> {<\/span><\/span>\n    $pdo <\/span>=<\/span> <\/span>new<\/span> <\/span>PDO<\/span>($dsn<\/span>,<\/span> $username<\/span>,<\/span> $password<\/span>,<\/span> $options);<\/span><\/span>\n} <\/span>catch<\/span> (<\/span>PDOException<\/span> $e) {<\/span><\/span>\n    <\/span>die<\/span>(<\/span>"Database connection failed: "<\/span> <\/span>.<\/span> $e<\/span>-><\/span>getMessage<\/span>()<\/span>);<\/span><\/span>\n}<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
2. Executing a Query<\/h3>\n\n\n\n
After establishing a database connection, you can execute SQL queries. In the following example, we select all records from a “users” table and fetch the results as an associative array.<\/p>\n\n\n\n
PHP<\/span><\/span><\/path><\/path><\/svg><\/span>
$sql <\/span>=<\/span> <\/span>"<\/span>SELECT<\/span> <\/span>*<\/span> <\/span>FROM<\/span> users"<\/span>;<\/span><\/span>\n$stmt <\/span>=<\/span> $pdo<\/span>-><\/span>query<\/span>(<\/span>$sql<\/span>)<\/span>;<\/span><\/span>\n$result <\/span>=<\/span> $stmt<\/span>-><\/span>fetchAll<\/span>(<\/span>PDO<\/span>::<\/span>FETCH_ASSOC<\/span>)<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
3. Prepared Statements<\/h3>\n\n\n\n
Prepared statements are a secure way to execute SQL queries and are particularly important for preventing SQL injection attacks. Here’s how you use prepared statements in PDO:<\/p>\n\n\n\n
PHP<\/span><\/span><\/path><\/path><\/svg><\/span>
$sql <\/span>=<\/span> <\/span>"<\/span>SELECT<\/span> <\/span>*<\/span> <\/span>FROM<\/span> users <\/span>WHERE<\/span> username <\/span>=<\/span> :username"<\/span>;<\/span><\/span>\n$stmt <\/span>=<\/span> $pdo<\/span>-><\/span>prepare<\/span>(<\/span>$sql<\/span>)<\/span>;<\/span><\/span>\n$username <\/span>=<\/span> <\/span>'john_doe'<\/span>;<\/span><\/span>\n$stmt<\/span>-><\/span>bindParam<\/span>(<\/span>':username'<\/span>,<\/span> $username<\/span>,<\/span> <\/span>PDO<\/span>::<\/span>PARAM_STR<\/span>)<\/span>;<\/span><\/span>\n$stmt<\/span>-><\/span>execute<\/span>()<\/span>;<\/span><\/span>\n$result <\/span>=<\/span> $stmt<\/span>-><\/span>fetchAll<\/span>(<\/span>PDO<\/span>::<\/span>FETCH_ASSOC<\/span>)<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Error Handling in PDO<\/h2>\n\n\n\n
PHP PDO offers robust error handling through exception handling. When an error occurs, a PDOException<\/code> is thrown, which you can catch and handle according to your application’s requirements. Here’s an example of how to handle database connection errors:<\/p>\n\n\n\n
PHP<\/span><\/span><\/path><\/path><\/svg><\/span>
try<\/span> {<\/span><\/span>\n    <\/span>\/\/ Attempt to establish a database connection<\/span><\/span>\n} <\/span>catch<\/span> (<\/span>PDOException<\/span> $e) {<\/span><\/span>\n    <\/span>die<\/span>(<\/span>"Database connection failed: "<\/span> <\/span>.<\/span> $e<\/span>-><\/span>getMessage<\/span>()<\/span>);<\/span><\/span>\n}<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Exception handling allows you to gracefully manage errors by providing detailed information for debugging and troubleshooting.<\/p>\n\n\n\n
Security Considerations<\/h2>\n\n\n\n
Ensuring the security of your application and database is of paramount importance. When using PDO, here are some security considerations to keep in mind:<\/p>\n\n\n\n
1. Prepared Statements<\/h3>\n\n\n\n
Prepared statements are your primary defense against SQL injection attacks. They automatically escape and quote values, preventing the malicious injection of SQL code.<\/p>\n\n\n\n
2. Parameter Binding<\/h3>\n\n\n\n
Always bind parameters in your prepared statements to prevent injection attacks. This ensures that user inputs are safely incorporated into your SQL queries.<\/p>\n\n\n\n
3. Avoid Dynamic SQL<\/h3>\n\n\n\n
Avoid dynamically generating SQL queries with user inputs without thorough validation. Ensure that user inputs are validated and sanitized before using them in queries.<\/p>\n\n\n\n
4. Secure Configuration<\/h3>\n\n\n\n
Your database server should be configured securely, including strong authentication mechanisms and access controls. Regularly update your database server software to patch known vulnerabilities.<\/p>\n\n\n\n
Pros and Cons of PHP PDO<\/h2>\n\n\n\n
Understanding the advantages and disadvantages of using PHP PDO is crucial when deciding whether to adopt it for your projects.<\/p>\n\n\n\n
Pros<\/h3>\n\n\n\n
    \n
  1. Database Independence<\/strong>: PDO allows you to switch between different databases easily by changing the DSN. This flexibility is valuable when working with multiple database systems.<\/li>\n\n\n\n
  2. Prepared Statements<\/strong>: Prepared statements provided by PDO offer strong protection against SQL injection attacks, enhancing the security of your application.<\/li>\n\n\n\n
  3. Error Handling<\/strong>: PDO’s exception-based error handling provides detailed information for debugging, making it easier to identify and address issues.<\/li>\n<\/ol>\n\n\n\n
    Cons<\/h3>\n\n\n\n
      \n
    1. Complexity<\/strong>: PDO may seem more complex for simple database tasks when compared to other, more straightforward database access methods.<\/li>\n\n\n\n
    2. Performance<\/strong>: In some cases, PDO may be slower than using native database extensions or libraries explicitly designed for a particular database system. However, the difference in performance is often negligible for most applications.<\/li>\n<\/ol>\n\n\n\n
      Complete Example: CRUD Operations with PHP PDO<\/h2>\n\n\n\n
      Let’s explore a complete example of a PHP PDO-based CRUD system for user management. In a real-world application, you would typically have a table called “users” with columns like “id,” “username,” “password,” and “email.” Here’s a simplified example of CRUD operations:<\/p>\n\n\n\n
      Create (Insert)<\/h3>\n\n\n\n
      PHP<\/span><\/span><\/path><\/path><\/svg><\/span>
      <?<\/span>php<\/span><\/span>\n\/\/ Database connection<\/span><\/span>\n$dsn <\/span>=<\/span> <\/span>"mysql:host=localhost;dbname=your_database_name"<\/span>;<\/span><\/span>\n$username <\/span>=<\/span> <\/span>"your_username"<\/span>;<\/span><\/span>\n$password <\/span>=<\/span> <\/span>"your_password"<\/span>;<\/span><\/span>\n$options <\/span>=<\/span> [<\/span>PDO<\/span>::<\/span>ATTR_ERRMODE <\/span>=><\/span> <\/span>PDO<\/span>::<\/span>ERRMODE_EXCEPTION];<\/span><\/span>\n<\/span>\ntry<\/span> {<\/span><\/span>\n    $pdo <\/span>=<\/span> <\/span>new<\/span> <\/span>PDO<\/span>($dsn<\/span>,<\/span> $username<\/span>,<\/span> $password<\/span>,<\/span> $options);<\/span><\/span>\n} <\/span>catch<\/span> (<\/span>PDOException<\/span> $e) {<\/span><\/span>\n    <\/span>die<\/span>(<\/span>"Database connection failed: "<\/span> <\/span>.<\/span> $e<\/span>-><\/span>getMessage<\/span>()<\/span>);<\/span><\/span>\n}<\/span><\/span>\n<\/span>\nif<\/span> ($_SERVER[<\/span>"REQUEST_METHOD"<\/span>] <\/span>==<\/span> <\/span>"POST"<\/span>) {<\/span><\/span>\n    <\/span>\/\/ Data validation (e.g., you should add more checks here)<\/span><\/span>\n    $username <\/span>=<\/span> filter_input<\/span>(<\/span>INPUT_POST<\/span>,<\/span> <\/span>'username'<\/span>,<\/span> <\/span>FILTER_SANITIZE_STRING<\/span>)<\/span>;<\/span><\/span>\n    $email <\/span>=<\/span> filter_input<\/span>(<\/span>INPUT_POST<\/span>,<\/span> <\/span>'email'<\/span>,<\/span> <\/span>FILTER_VALIDATE_EMAIL<\/span>)<\/span>;<\/span><\/span>\n    $password <\/span>=<\/span> $_POST[<\/span>'password'<\/span>];<\/span><\/span>\n<\/span>\n    <\/span>if<\/span> (empty<\/span>(<\/span>$username<\/span>)<\/span> <\/span>||<\/span> empty<\/span>(<\/span>$email<\/span>)<\/span> <\/span>||<\/span> empty<\/span>(<\/span>$password<\/span>)<\/span>) {<\/span><\/span>\n        <\/span>die<\/span>(<\/span>"Please fill out all required fields."<\/span>);<\/span><\/span>\n    }<\/span><\/span>\n<\/span>\n    <\/span>\/\/ Password hashing<\/span><\/span>\n    $hashedPassword <\/span>=<\/span> password_hash<\/span>(<\/span>$password<\/span>,<\/span> PASSWORD_BCRYPT<\/span>)<\/span>;<\/span><\/span>\n<\/span>\n    <\/span>\/\/ Insert data into the database using prepared statement<\/span><\/span>\n    $sql <\/span>=<\/span> <\/span>"<\/span>INSERT INTO<\/span> users (username, email, <\/span>password<\/span>) <\/span>VALUES<\/span> (:username, :email, :<\/span>password<\/span>)"<\/span>;<\/span><\/span>\n    $stmt <\/span>=<\/span> $pdo<\/span>-><\/span>prepare<\/span>(<\/span>$sql<\/span>)<\/span>;<\/span><\/span>\n    $stmt<\/span>-><\/span>bindParam<\/span>(<\/span>':username'<\/span>,<\/span> $username<\/span>,<\/span> <\/span>PDO<\/span>::<\/span>PARAM_STR<\/span>)<\/span>;<\/span><\/span>\n    $stmt<\/span>-><\/span>bindParam<\/span>(<\/span>':email'<\/span>,<\/span> $email<\/span>,<\/span> <\/span>PDO<\/span>::<\/span>PARAM_STR<\/span>)<\/span>;<\/span><\/span>\n    $stmt<\/span>-><\/span>bindParam<\/span>(<\/span>':password'<\/span>,<\/span> $hashedPassword<\/span>,<\/span> <\/span>PDO<\/span>::<\/span>PARAM_STR<\/span>)<\/span>;<\/span><\/span>\n<\/span>\n    <\/span>try<\/span> {<\/span><\/span>\n        $stmt<\/span>-><\/span>execute<\/span>()<\/span>;<\/span><\/span>\n        echo <\/span>"User registration successful!"<\/span>;<\/span><\/span>\n    } <\/span>catch<\/span> (<\/span>PDOException<\/span> $e) {<\/span><\/span>\n        <\/span>die<\/span>(<\/span>"User registration failed: "<\/span> <\/span>.<\/span> $e<\/span>-><\/span>getMessage<\/span>()<\/span>);<\/span><\/span>\n    }<\/span><\/span>\n}<\/span><\/span>\n?><\/span><\/span>\n<\/span>\n<!<\/span>DOCTYPE html<\/span>><\/span><\/span>\n<<\/span>html<\/span>><\/span><\/span>\n<<\/span>head<\/span>><\/span><\/span>\n    <\/span><<\/span>title<\/span>><\/span>Register User<\/span><\/<\/span>title<\/span>><\/span><\/span>\n<\/<\/span>head<\/span>><\/span><\/span>\n<<\/span>body<\/span>><\/span><\/span>\n    <\/span><<\/span>h1<\/span>><\/span>Register User<\/span><\/<\/span>h1<\/span>><\/span><\/span>\n    <\/span><<\/span>form method<\/span>=<\/span>"post"<\/span> action<\/span>=<\/span>""<\/span>><\/span><\/span>\n        <\/span><<\/span>label <\/span>for=<\/span>"username"<\/span>><\/span>Username<\/span>:<\/<\/span>label<\/span>><\/span><\/span>\n        <\/span><<\/span>input type<\/span>=<\/span>"text"<\/span> id<\/span>=<\/span>"username"<\/span> name<\/span>=<\/span>"username"<\/span> required<\/span>><<\/span>br<\/span>><\/span><\/span>\n        <\/span><\/span>\n        <\/span><<\/span>label <\/span>for=<\/span>"email"<\/span>><\/span>Email<\/span>:<\/<\/span>label<\/span>><\/span><\/span>\n        <\/span><<\/span>input type<\/span>=<\/span>"email"<\/span> id<\/span>=<\/span>"email"<\/span> name<\/span>=<\/span>"email"<\/span> required<\/span>><<\/span>br<\/span>><\/span><\/span>\n        <\/span><\/span>\n        <\/span><<\/span>label <\/span>for=<\/span>"password"<\/span>><\/span>Password:<\/span><\/<\/span>label<\/span>><\/span><\/span>\n        <\/span><<\/span>input type<\/span>=<\/span>"password"<\/span> id<\/span>=<\/span>"password"<\/span> name<\/span>=<\/span>"password"<\/span> required<\/span>><<\/span>br<\/span>><\/span><\/span>\n        <\/span><\/span>\n        <\/span><<\/span>input type<\/span>=<\/span>"submit"<\/span> value<\/span>=<\/span>"Register"<\/span>><\/span><\/span>\n    <\/span><\/<\/span>form<\/span>><\/span><\/span>\n<\/<\/span>body<\/span>><\/span><\/span>\n<\/<\/span>html<\/span>><\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
      Read (Select)<\/h3>\n\n\n\n
      PHP<\/span><\/span><\/path><\/path><\/svg><\/span>
      <?<\/span>php<\/span><\/span>\n\/\/ Database connection<\/span><\/span>\n$dsn <\/span>=<\/span> <\/span>"mysql:host=localhost;dbname=your_database_name"<\/span>;<\/span><\/span>\n$username <\/span>=<\/span> <\/span>"your_username"<\/span>;<\/span><\/span>\n$password <\/span>=<\/span> <\/span>"your_password"<\/span>;<\/span><\/span>\n$options <\/span>=<\/span> [<\/span>PDO<\/span>::<\/span>ATTR_ERRMODE <\/span>=><\/span> <\/span>PDO<\/span>::<\/span>ERRMODE_EXCEPTION];<\/span><\/span>\n<\/span>\ntry<\/span> {<\/span><\/span>\n    $pdo <\/span>=<\/span> <\/span>new<\/span> <\/span>PDO<\/span>($dsn<\/span>,<\/span> $username<\/span>,<\/span> $password<\/span>,<\/span> $options);<\/span><\/span>\n} <\/span>catch<\/span> (<\/span>PDOException<\/span> $e) {<\/span><\/span>\n    <\/span>die<\/span>(<\/span>"Database connection failed: "<\/span> <\/span>.<\/span> $e<\/span>-><\/span>getMessage<\/span>()<\/span>);<\/span><\/span>\n}<\/span><\/span>\n<\/span>\n\/\/ Select data from the database<\/span><\/span>\n$sql <\/span>=<\/span> <\/span>"<\/span>SELECT<\/span> id, username, email <\/span>FROM<\/span> users"<\/span>;<\/span><\/span>\n$stmt <\/span>=<\/span> $pdo<\/span>-><\/span>query<\/span>(<\/span>$sql<\/span>)<\/span>;<\/span><\/span>\n<\/span>\nif<\/span> ($stmt) {<\/span><\/span>\n    $users <\/span>=<\/span> $stmt<\/span>-><\/span>fetchAll<\/span>(<\/span>PDO<\/span>::<\/span>FETCH_ASSOC<\/span>)<\/span>;<\/span><\/span>\n} <\/span>else<\/span> {<\/span><\/span>\n    <\/span>die<\/span>(<\/span>"Failed to retrieve user data."<\/span>);<\/span><\/span>\n}<\/span><\/span>\n?><\/span><\/span>\n<\/span>\n<!<\/span>DOCTYPE html<\/span>><\/span><\/span>\n<<\/span>html<\/span>><\/span><\/span>\n<<\/span>head<\/span>><\/span><\/span>\n    <\/span><<\/span>title<\/span>><\/span>User List<\/span><\/<\/span>title<\/span>><\/span><\/span>\n<\/<\/span>head<\/span>><\/span><\/span>\n<<\/span>body<\/span>><\/span><\/span>\n    <\/span><<\/span>h1<\/span>><\/span>User List<\/span><\/<\/span>h1<\/span>><\/span><\/span>\n    <\/span><?<\/span>php <\/span>if<\/span> (empty<\/span>(<\/span>$users<\/span>)<\/span>) <\/span>:<\/span> <\/span>?><\/span><\/span>\n        <\/span><<\/span>p<\/span>><\/span>No<\/span> users found<\/span>.<\/span><\/<\/span>p<\/span>><\/span><\/span>\n    <\/span><?<\/span>php <\/span>else<\/span> <\/span>:<\/span> <\/span>?><\/span><\/span>\n        <\/span><<\/span>table<\/span>><\/span><\/span>\n            <\/span><<\/span>thead<\/span>><\/span><\/span>\n                <\/span><<\/span>tr<\/span>><\/span><\/span>\n                    <\/span><<\/span>th<\/span>><\/span>ID<\/span><\/<\/span>th<\/span>><\/span><\/span>\n                    <\/span><<\/span>th<\/span>><\/span>Username<\/span><\/<\/span>th<\/span>><\/span><\/span>\n                    <\/span><<\/span>th<\/span>><\/span>Email<\/span><\/<\/span>th<\/span>><\/span><\/span>\n                <\/span><\/<\/span>tr<\/span>><\/span><\/span>\n            <\/span><\/<\/span>thead<\/span>><\/span><\/span>\n            <\/span><<\/span>tbody<\/span>><\/span><\/span>\n                <\/span><?<\/span>php <\/span>foreach<\/span> ($users <\/span>as<\/span> $user) <\/span>:<\/span> <\/span>?><\/span><\/span>\n                    <\/span><<\/span>tr<\/span>><\/span><\/span>\n                        <\/span><<\/span>td<\/span>><?<\/span>php echo $user[<\/span>'id'<\/span>]; <\/span>?><\/<\/span>td<\/span>><\/span><\/span>\n                        <\/span><<\/span>td<\/span>><?<\/span>php echo $user[<\/span>'username'<\/span>]; <\/span>?><\/<\/span>td<\/span>><\/span><\/span>\n                        <\/span><<\/span>td<\/span>><?<\/span>php echo $user[<\/span>'email'<\/span>]; <\/span>?><\/<\/span>td<\/span>><\/span><\/span>\n                    <\/span><\/<\/span>tr<\/span>><\/span><\/span>\n                <\/span><?<\/span>php <\/span>endforeach<\/span>; <\/span>?><\/span><\/span>\n            <\/span><\/<\/span>tbody<\/span>><\/span><\/span>\n        <\/span><\/<\/span>table<\/span>><\/span><\/span>\n    <\/span><?<\/span>php <\/span>endif<\/span>; <\/span>?><\/span><\/span>\n<\/<\/span>body<\/span>><\/span><\/span>\n<\/<\/span>html<\/span>><\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
      Update (Update)<\/h3>\n\n\n\n
      PHP<\/span><\/span><\/path><\/path><\/svg><\/span>
      <?<\/span>php<\/span><\/span>\n\/\/ Database connection<\/span><\/span>\n$dsn <\/span>=<\/span> <\/span>"mysql:host=localhost;dbname=your_database_name"<\/span>;<\/span><\/span>\n$username <\/span>=<\/span> <\/span>"your_username"<\/span>;<\/span><\/span>\n$password <\/span>=<\/span> <\/span>"your_password"<\/span>;<\/span><\/span>\n$options <\/span>=<\/span> [<\/span>PDO<\/span>::<\/span>ATTR_ERRMODE <\/span>=><\/span> <\/span>PDO<\/span>::<\/span>ERRMODE_EXCEPTION];<\/span><\/span>\n<\/span>\ntry<\/span> {<\/span><\/span>\n    $pdo <\/span>=<\/span> <\/span>new<\/span> <\/span>PDO<\/span>($dsn<\/span>,<\/span> $username<\/span>,<\/span> $password<\/span>,<\/span> $options);<\/span><\/span>\n} <\/span>catch<\/span> (<\/span>PDOException<\/span> $e) {<\/span><\/span>\n    <\/span>die<\/span>(<\/span>"Database connection failed: "<\/span> <\/span>.<\/span> $e<\/span>-><\/span>getMessage<\/span>()<\/span>);<\/span><\/span>\n}<\/span><\/span>\n<\/span>\nif<\/span> ($_SERVER[<\/span>"REQUEST_METHOD"<\/span>] <\/span>==<\/span> <\/span>"POST"<\/span>) {<\/span><\/span>\n    <\/span>\/\/ Data validation (e.g., you should add more checks here)<\/span><\/span>\n    $userId <\/span>=<\/span> $_POST[<\/span>'user_id'<\/span>];<\/span><\/span>\n    $newEmail <\/span>=<\/span> filter_input<\/span>(<\/span>INPUT_POST<\/span>,<\/span> <\/span>'new_email'<\/span>,<\/span> <\/span>FILTER_VALIDATE_EMAIL<\/span>)<\/span>;<\/span><\/span>\n<\/span>\n    <\/span>if<\/span> (empty<\/span>(<\/span>$newEmail<\/span>)<\/span>) {<\/span><\/span>\n        <\/span>die<\/span>(<\/span>"Please provide a valid email address."<\/span>);<\/span><\/span>\n    }<\/span><\/span>\n<\/span>\n    <\/span>\/\/ Update data in the database using a prepared statement<\/span><\/span>\n    $sql <\/span>=<\/span> <\/span>"<\/span>UPDATE<\/span> users <\/span>SET<\/span> email <\/span>=<\/span> :new_email <\/span>WHERE<\/span> id <\/span>=<\/span> :user_id"<\/span>;<\/span><\/span>\n    $stmt <\/span>=<\/span> $pdo<\/span>-><\/span>prepare<\/span>(<\/span>$sql<\/span>)<\/span>;<\/span><\/span>\n    $stmt<\/span>-><\/span>bindParam<\/span>(<\/span>':new_email'<\/span>,<\/span> $newEmail<\/span>,<\/span> <\/span>PDO<\/span>::<\/span>PARAM_STR<\/span>)<\/span>;<\/span><\/span>\n    $stmt<\/span>-><\/span>bindParam<\/span>(<\/span>':user_id'<\/span>,<\/span> $userId<\/span>,<\/span> <\/span>PDO<\/span>::<\/span>PARAM_INT<\/span>)<\/span>;<\/span><\/span>\n<\/span>\n    <\/span>try<\/span> {<\/span><\/span>\n        $stmt<\/span>-><\/span>execute<\/span>()<\/span>;<\/span><\/span>\n        echo <\/span>"User data updated successfully!"<\/span>;<\/span><\/span>\n    } <\/span>catch<\/span> (<\/span>PDOException<\/span> $e) {<\/span><\/span>\n        <\/span>die<\/span>(<\/span>"User data update failed: "<\/span> <\/span>.<\/span> $e<\/span>-><\/span>getMessage<\/span>()<\/span>);<\/span><\/span>\n    }<\/span><\/span>\n}<\/span><\/span>\n?><\/span><\/span>\n<\/span>\n<!<\/span>DOCTYPE html<\/span>><\/span><\/span>\n<<\/span>html<\/span>><\/span><\/span>\n<<\/span>head<\/span>><\/span><\/span>\n    <\/span><<\/span>title<\/span>><\/span>Update User Data<\/span><\/<\/span>title<\/span>><\/span><\/span>\n<\/<\/span>head<\/span>><\/span><\/span>\n<<\/span>body<\/span>><\/span><\/span>\n    <\/span><<\/span>h1<\/span>><\/span>Update User Data<\/span><\/<\/span>h1<\/span>><\/span><\/span>\n    <\/span><<\/span>form method<\/span>=<\/span>"post"<\/span> action<\/span>=<\/span>""<\/span>><\/span><\/span>\n        <\/span><<\/span>label <\/span>for=<\/span>"user_id"<\/span>><\/span>User ID<\/span>:<\/<\/span>label<\/span>><\/span><\/span>\n        <\/span><<\/span>input type<\/span>=<\/span>"text"<\/span> id<\/span>=<\/span>"user_id"<\/span> name<\/span>=<\/span>"user_id"<\/span> required<\/span>><<\/span>br<\/span>><\/span><\/span>\n        <\/span><\/span>\n        <\/span><<\/span>label <\/span>for=<\/span>"new_email"<\/span>>New<\/span> <\/span>Email<\/span>:<\/<\/span>label<\/span>><\/span><\/span>\n        <\/span><<\/span>input type<\/span>=<\/span>"email"<\/span> id<\/span>=<\/span>"new_email"<\/span> name<\/span>=<\/span>"new_email"<\/span> required<\/span>><<\/span>br<\/span>><\/span><\/span>\n        <\/span><\/span>\n        <\/span><<\/span>input type<\/span>=<\/span>"submit"<\/span> value<\/span>=<\/span>"Update User Data"<\/span>><\/span><\/span>\n    <\/span><\/<\/span>form<\/span>><\/span><\/span>\n<\/<\/span>body<\/span>><\/span><\/span>\n<\/<\/span>html<\/span>><\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
      Delete (Delete)<\/h3>\n\n\n\n
      PHP<\/span><\/span><\/path><\/path><\/svg><\/span>
      <?<\/span>php<\/span><\/span>\n\/\/ Database connection<\/span><\/span>\n$dsn <\/span>=<\/span> <\/span>"mysql:host=localhost;dbname=your_database_name"<\/span>;<\/span><\/span>\n$username <\/span>=<\/span> <\/span>"your_username"<\/span>;<\/span><\/span>\n$password <\/span>=<\/span> <\/span>"your_password"<\/span>;<\/span><\/span>\n$options <\/span>=<\/span> [<\/span>PDO<\/span>::<\/span>ATTR_ERRMODE <\/span>=><\/span> <\/span>PDO<\/span>::<\/span>ERRMODE_EXCEPTION];<\/span><\/span>\n<\/span>\ntry<\/span> {<\/span><\/span>\n    $pdo <\/span>=<\/span> <\/span>new<\/span> <\/span>PDO<\/span>($dsn<\/span>,<\/span> $username<\/span>,<\/span> $password<\/span>,<\/span> $options);<\/span><\/span>\n} <\/span>catch<\/span> (<\/span>PDOException<\/span> $e) {<\/span><\/span>\n    <\/span>die<\/span>(<\/span>"Database connection failed: "<\/span> <\/span>.<\/span> $e<\/span>-><\/span>getMessage<\/span>()<\/span>);<\/span><\/span>\n}<\/span><\/span>\n<\/span>\nif<\/span> ($_SERVER[<\/span>"REQUEST_METHOD"<\/span>] <\/span>==<\/span> <\/span>"POST"<\/span>) {<\/span><\/span>\n    <\/span>\/\/ Data validation (e.g., you should add more checks here)<\/span><\/span>\n    $userIdToDelete <\/span>=<\/span> $_POST[<\/span>'user_id'<\/span>];<\/span><\/span>\n<\/span>\n    <\/span>if<\/span> (<\/span>!<\/span>is_numeric<\/span>(<\/span>$userIdToDelete<\/span>)<\/span> <\/span>||<\/span> $userIdToDelete <\/span><=<\/span> <\/span>0<\/span>) {<\/span><\/span>\n        <\/span>die<\/span>(<\/span>"Invalid user ID provided."<\/span>);<\/span><\/span>\n    }<\/span><\/span>\n<\/span>\n    <\/span>\/\/ Delete data from the database using a prepared statement<\/span><\/span>\n    $sql <\/span>=<\/span> <\/span>"<\/span>DELETE<\/span> <\/span>FROM<\/span> users <\/span>WHERE<\/span> id <\/span>=<\/span> :user_id"<\/span>;<\/span><\/span>\n    $stmt <\/span>=<\/span> $pdo<\/span>-><\/span>prepare<\/span>(<\/span>$sql<\/span>)<\/span>;<\/span><\/span>\n    $stmt<\/span>-><\/span>bindParam<\/span>(<\/span>':user_id'<\/span>,<\/span> $userIdToDelete<\/span>,<\/span> <\/span>PDO<\/span>::<\/span>PARAM_INT<\/span>)<\/span>;<\/span><\/span>\n<\/span>\n    <\/span>try<\/span> {<\/span><\/span>\n        $stmt<\/span>-><\/span>execute<\/span>()<\/span>;<\/span><\/span>\n        echo <\/span>"User deleted successfully!"<\/span>;<\/span><\/span>\n    } <\/span>catch<\/span> (<\/span>PDOException<\/span> $e) {<\/span><\/span>\n        <\/span>die<\/span>(<\/span>"User deletion failed: "<\/span> <\/span>.<\/span> $e<\/span>-><\/span>getMessage<\/span>()<\/span>);<\/span><\/span>\n    }<\/span><\/span>\n}<\/span><\/span>\n?><\/span><\/span>\n<\/span>\n<!<\/span>DOCTYPE html<\/span>><\/span><\/span>\n<<\/span>html<\/span>><\/span><\/span>\n<<\/span>head<\/span>><\/span><\/span>\n    <\/span><<\/span>title<\/span>><\/span>Delete User<\/span><\/<\/span>title<\/span>><\/span><\/span>\n<\/<\/span>head<\/span>><\/span><\/span>\n<<\/span>body<\/span>><\/span><\/span>\n    <\/span><<\/span>h1<\/span>><\/span>Delete User<\/span><\/<\/span>h1<\/span>><\/span><\/span>\n    <\/span><<\/span>form method<\/span>=<\/span>"post"<\/span> action<\/span>=<\/span>""<\/span>><\/span><\/span>\n        <\/span><<\/span>label <\/span>for=<\/span>"user_id"<\/span>><\/span>User ID<\/span>:<\/<\/span>label<\/span>><\/span><\/span>\n        <\/span><<\/span>input type<\/span>=<\/span>"text"<\/span> id<\/span>=<\/span>"user_id"<\/span> name<\/span>=<\/span>"user_id"<\/span> required<\/span>><<\/span>br<\/span>><\/span><\/span>\n        <\/span><\/span>\n        <\/span><<\/span>input type<\/span>=<\/span>"submit"<\/span> value<\/span>=<\/span>"Delete User"<\/span>><\/span><\/span>\n    <\/span><\/<\/span>form<\/span>><\/span><\/span>\n<\/<\/span>body<\/span>><\/span><\/span>\n<\/<\/span>html<\/span>><\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
      Keep in mind that this is a basic example for illustration purposes and doesn’t include input validation or thorough error handling. In a production environment, you should implement these to enhance security and data integrity.<\/p>\n\n\n\n
      Conclusion<\/h2>\n\n\n\n
      PHP PDO is a powerful tool for interacting with databases in a secure and consistent manner. It offers features like prepared statements, robust error handling, and support for various database systems. By following best practices for security and error handling, you can build secure and efficient database-driven applications using PHP PDO. Stay updated with the latest version of PDO and apply these techniques to ensure the reliability and security of your database interactions.<\/p>\n","protected":false},"excerpt":{"rendered":"
      PHP Data Objects (PDO) is a robust and versatile database abstraction layer that provides a consistent and secure interface for interacting with various databases. It’s an essential tool for PHP developers when working with databases. In this comprehensive guide, we’ll cover the latest usage, syntax, error handling, and security considerations, as well as the pros […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[48,10],"tags":[49,4],"_links":{"self":[{"href":"https:\/\/rishikantsri.in\/blog\/wp-json\/wp\/v2\/posts\/404"}],"collection":[{"href":"https:\/\/rishikantsri.in\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/rishikantsri.in\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/rishikantsri.in\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/rishikantsri.in\/blog\/wp-json\/wp\/v2\/comments?post=404"}],"version-history":[{"count":2,"href":"https:\/\/rishikantsri.in\/blog\/wp-json\/wp\/v2\/posts\/404\/revisions"}],"predecessor-version":[{"id":407,"href":"https:\/\/rishikantsri.in\/blog\/wp-json\/wp\/v2\/posts\/404\/revisions\/407"}],"wp:attachment":[{"href":"https:\/\/rishikantsri.in\/blog\/wp-json\/wp\/v2\/media?parent=404"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/rishikantsri.in\/blog\/wp-json\/wp\/v2\/categories?post=404"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/rishikantsri.in\/blog\/wp-json\/wp\/v2\/tags?post=404"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}