Chapter 8: Working with Databases in PHP

In this chapter, we will explore working with databases in PHP. We will cover connecting to databases, executing SQL queries, fetching and manipulating data, and handling errors.

8.1 Connecting to databases:

PHP provides extensions and functions to connect to various databases, such as MySQL, PostgreSQL, SQLite, and more.

We need to install and enable the relevant database extension in PHP before establishing a connection.

Example (MySQL):

php

""

<?php

   $host = "localhost";

   $username = "root";

   $password = "password";

   $database = "my_database";

   

   // Establishing a MySQL database connection

   $connection = mysqli_connect($host, $username, $password, $database);

   

   // Checking the connection

   if (!$connection) {

       die("Connection failed: " . mysqli_connect_error());

   }

   

   echo "Connected successfully!";

?>

8.2 Executing SQL queries:

We can execute SQL queries using functions like mysqli_query(), mysqli_prepare(), or object-oriented approaches like PDO.

SQL queries can be used to create, read, update, or delete data in the database.

Example:

php

""

<?php

   // Executing an SQL query

   $sql = "SELECT * FROM users";

   $result = mysqli_query($connection, $sql);

   

   // Checking the query result

   if ($result) {

       // Process the result

   } else {

       echo "Query execution failed: " . mysqli_error($connection);

   }

?>

8.3 Fetching and manipulating data:

We can fetch and manipulate data from the database using functions like mysqli_fetch_assoc(), mysqli_fetch_array(), or fetch() methods in PDO.

These functions allow us to retrieve data row by row and perform operations on the retrieved data.

Example:

php

""

<?php

   // Fetching data from the result set

   while ($row = mysqli_fetch_assoc($result)) {

       echo "Name: " . $row["name"] . ", Email: " . $row["email"];

   }

?>

8.4 Handling errors:

Error handling in database operations involves checking the success of queries and handling potential errors.

We can use functions like mysqli_error(), mysqli_errno(), or try-catch blocks in PDO to handle errors and exceptions.

Example:

php

""

<?php

   // Executing an SQL query

   $result = mysqli_query($connection, $sql);

   

   // Checking for errors

   if (!$result) {

       echo "Query execution failed: " . mysqli_error($connection);

   }

?>

By following the explanations and examples in this chapter, you will gain a solid understanding of working with databases in PHP, allowing you to connect to databases, execute queries, fetch and manipulate data, and handle errors effectively.