**Course Title:** Modern PHP Development: Best Practices and Advanced Techniques **Section Title:** Working with Databases (MySQL/MariaDB) **Topic:** CRUD operations (Create, Read, Update, Delete) using SQL **Introduction to CRUD Operations** ===================================== CRUD (Create, Read, Update, Delete) operations are the basic building blocks for interacting with databases. In this topic, we'll explore how to perform CRUD operations using SQL in a MySQL/MariaDB database. **Create Operation** -------------------- The Create operation is used to insert new data into the database. This is achieved using the `INSERT` statement. **Example: Creating a new user** ```sql INSERT INTO users (name, email, password) VALUES ('John Doe', 'johndoe@example.com', 'password123'); ``` **Tips and Variations:** * You can specify the columns you want to insert data into, as shown above. * If you don't specify the columns, you'll need to provide values for all columns in the table. * You can insert multiple rows at once by separating the `VALUES` tuples with commas. **Read Operation** ----------------- The Read operation is used to retrieve data from the database. This is achieved using the `SELECT` statement. **Example: Retrieving all users** ```sql SELECT * FROM users; ``` **Tips and Variations:** * The `*` symbol is a wildcard that selects all columns. * You can specify specific columns to select, like `SELECT name, email FROM users;`. * You can filter results using the `WHERE` clause, like `SELECT * FROM users WHERE name = 'John Doe';`. **Update Operation** ------------------ The Update operation is used to modify existing data in the database. This is achieved using the `UPDATE` statement. **Example: Updating a user's email** ```sql UPDATE users SET email = 'johndoe2@example.com' WHERE name = 'John Doe'; ``` **Tips and Variations:** * You can update multiple columns by separating the `SET` assignments with commas. * You can update multiple rows at once by using a different `WHERE` clause. **Delete Operation** ------------------ The Delete operation is used to remove data from the database. This is achieved using the `DELETE` statement. **Example: Deleting a user** ```sql DELETE FROM users WHERE name = 'John Doe'; ``` **Tips and Variations:** * Be cautious with `DELETE` statements, as they permanently remove data. * Always use a `WHERE` clause to specify the rows you want to delete. **Practical Example: CRUD operations with PHP and MySQL/MariaDB** ----------------------------------------------------------------- Let's create a simple PHP script that performs CRUD operations on a MySQL/MariaDB database: ### Connect to the database ```php $dsn = 'mysql:host=localhost;dbname=example'; $username = 'your_username'; $password = 'your_password'; try { $pdo = new PDO($dsn, $username, $password); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (PDOException $e) { echo 'Connection failed: ' . $e->getMessage(); } ``` ### Create a new user ```php $newUser = [ 'name' => 'Jane Doe', 'email' => 'janedoe@example.com', 'password' => 'password123' ]; $stmt = $pdo->prepare('INSERT INTO users (name, email, password) VALUES (:name, :email, :password)'); $stmt->execute($newUser); ``` ### Retrieve all users ```php $stmt = $pdo->query('SELECT * FROM users'); $users = $stmt->fetchAll(); foreach ($users as $user) { echo $user['name'] . ' - ' . $user['email'] . "\n"; } ``` ### Update a user's email ```php $user_update = [ 'email' => 'janedoe2@example.com', 'name' => 'Jane Doe' ]; $stmt = $pdo->prepare('UPDATE users SET email = :email WHERE name = :name'); $stmt->execute($user_update); ``` ### Delete a user ```php $stmt = $pdo->prepare('DELETE FROM users WHERE name = :name'); $stmt->execute(['name' => 'Jane Doe']); ``` **Conclusion** ---------- In this topic, you've learned how to perform CRUD operations using SQL in a MySQL/MariaDB database. You've seen practical examples in PHP and learned how to connect to a database, create a new user, retrieve all users, update a user's email, and delete a user. **Recommended Reading** * [MySQL Documentation: INSERT](https://dev.mysql.com/doc/refman/8.0/en/insert.html) * [MySQL Documentation: SELECT](https://dev.mysql.com/doc/refman/8.0/en/select.html) * [MySQL Documentation: UPDATE](https://dev.mysql.com/doc/refman/8.0/en/update.html) * [MySQL Documentation: DELETE](https://dev.mysql.com/doc/refman/8.0/en/delete.html) * [PDO Documentation](https://php.net/manual/en/book.pdo.php) **Leave a comment or ask for help** Do you have any questions or need further clarification on CRUD operations? Please leave a comment below, and we'll be happy to assist you. **What's Next** In the next topic, we'll cover "Prepared Statements and Parameterized Queries to Prevent SQL Injection". This topic will show you how to improve the security of your database interactions by using prepared statements and parameterized queries.