Connect to Database with PHP PDO and Perform Basic CRUD Operations

In this article, we'll cover how to connect to a database with PHP PDO (PHP Data Objects) and perform basic CRUD (Create, Read, Update, Delete) operations.

Connecting to the Database

First, we need to connect to the database. We'll use PHP PDO to connect to MySQL database. Here's how you can establish a connection:

                        $dsn = 'mysql:host=localhost;dbname=mydatabase';
                        $username = 'myusername';
                        $password = 'mypassword';
                        

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

In this example, we specify the host, database name, username, and password. We then create a new PDO object and set the `PDO::ATTR_ERRMODE` attribute to `PDO::ERRMODE_EXCEPTION`. This tells PDO to throw an exception if there's an error connecting to the database.

CRUD Operations

Once we've established a connection to the database, we can perform CRUD operations. Here are some examples of basic CRUD operations using PDO.

Create

To create a new record in the database, we need to use an `INSERT` statement. Here's an example:

                        $sql = "INSERT INTO users (name, email, password) VALUES (?, ?, ?)";
                        $stmt = $pdo->prepare($sql);
                        $stmt->execute(['John Doe', 'john@example.com', 'password123']);
                    

In this example, we prepare an `INSERT` statement with placeholders for the values we want to insert. We then execute the statement with an array of values.

Read

To read records from the database, we use a `SELECT` statement. Here's an example:

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

                        while ($row = $stmt->fetch()) {
                        echo $row['name'] . "\n";
                        }
                    

In this example, we prepare a `SELECT` statement and execute it with the `query()` method. We then iterate over the results with the `fetch()` method and output the `name` field.

Update

To update a record in the database, we use an `UPDATE` statement. Here's an example:

                        $sql = "UPDATE users SET name = ? WHERE id = ?";
                        $stmt = $pdo->prepare($sql);
                        $stmt->execute(['Jane Doe', 1]);
                    

In this example, we prepare an `UPDATE` statement with placeholders for the values we want to update. We then execute the statement with an array of values.

Delete

To delete a record from the database, we use a `DELETE` statement. Here's an example:

                        $sql = "DELETE FROM users WHERE id = ?";
                        $stmt = $pdo->prepare($sql);
                        $stmt->execute([1]);
                    

In this example, we prepare a `DELETE` statement with a placeholder for the value we want to delete. We then execute the statement with an array of values.

Conclusion

In this article, we covered how to connect to a database with PHP PDO and perform basic CRUD operations. While these examples are simple, they should give you a good starting point for working with databases in PHP. As you become more familiar with PDO and SQL, you can use more advanced features to build more complex applications.