CRUD Actions with PDO

CRUD stands for Create, Read, Update, and Delete. These represent the four basic functions to interact with data stored in relational databases. When using PDO (PHP Data Objects) to perform these operations in PHP, you can achieve a higher level of security and flexibility.

As always, make sure you're connected to the database and have an instance of the PDO class before proceeding.

Creating Data

Let's break down the process of inserting data using PDO-prepared statements into simple steps.

Instead of writing a complete SQL query, you'll write a template with placeholders. These placeholders will be replaced with real values when the query is executed.

For example, if you're inserting data into a users table with columns name and email:

$sql = "INSERT INTO users (name, email) VALUES (:name, :email)";
$stmt = $pdo->prepare($sql);

:name and :email are named placeholders. Now, you'll associate real values with the named placeholders. This is where the magic of prepared statements comes in, ensuring the data is treated purely as data and not executable SQL, thus preventing SQL injection attacks.

$name = "John Doe";
$email = "john.doe@example.com";
 
$stmt->bindParam(':name', $name);
$stmt->bindParam(':email', $email);
 
$stmt = $pdo->prepare($sql);

The bindParam() method links a PHP variable to a named placeholder. When the statement is executed, the current value of the PHP variable is used in place of the placeholder.

After binding the values, you can execute the prepared statement. This will send the SQL query to the database with the actual data, causing the new record to be inserted.

$stmt->execute();

Retrieving Data

Let's break down the process of selecting (or retrieving) data using PDO-prepared statements.

Let's imagine we want to filter the users table. For example, selecting users with a specific email, you use a placeholder:

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

:email is a named placeholder. If you're filtering or using conditions, bind values to the placeholders.

$email_to_search = "example@email.com";
$stmt->bindParam(':email', $email_to_search);

bindParam() associates a PHP variable with a named placeholder. Now, it's time to run the query and fetch data from the database.

$stmt->execute();

Updating Data

Next, let's look at how we would update data. If you're updating a users table, or changing a user's email based on their ID, you would write:

$sql = "UPDATE users SET email = :new_email WHERE id = :user_id";
$stmt = $pdo->prepare($sql);

:new_email and :user_id are named placeholders representing the new email and the ID of the user you wish to update, respectively.

After preparing the query, we can provide actual values for our placeholders.

$new_email_value = "newemail@example.com";
$user_id_value = 5; // Let's assume we're updating the user with ID 5
 
$stmt->bindParam(':new_email', $new_email_value);
$stmt->bindParam(':user_id', $user_id_value);

Run the query to update the data in the database.

$stmt->execute();

Deleting Data

Last but not least, let's look at how to delete data with PDO prepared statements. If you want to delete a record from the users table based on a specific user ID, you will write:

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

:user_id is a named placeholder that will represent the ID of the user you want to delete.

Afterward, bind the actual value (the specific user ID in this case) to the placeholder.

$user_id_value = 10; // Let's say you want to delete the user with ID 10
$stmt->bindParam(':user_id', $user_id_value);

Lastly, run the prepared statement to delete the specified record from the database.

$stmt->execute();

Key Takeaways

• The steps for performing a query are mostly the same. Write the query with placeholders, prepare it, bind values, and then execute it.