php pdo fetch from db with code examples

PHP PDO fetch from DB with code examples

As a web developer, one of the most important tasks you’ll undertake is fetching data from a database. In PHP, you can use PDO (PHP Data Objects) to fetch data from databases like MySQL, PostgreSQL, SQLite and more. PDO is a powerful tool that provides a consistent interface for accessing various databases and allows developers to write database-independent code.

In this article, we’ll introduce you to PDO and show you how to fetch data from a database using simple code examples.

What is PDO?

PDO is a PHP extension that provides a lightweight, consistent interface for accessing databases in PHP. The purpose of PDO is to provide a unified API for various databases like MySQL, PostgreSQL, SQLite, Oracle, and more. It provides a number of benefits over the traditional MySQL extension, like support for transactions, prepared statements, and more.

Advantages of PDO

Some of the advantages of PDO are:

Database independence: Since PDO is able to work with multiple database types, you can easily switch between them without having to change the code.
Security: PDO provides support for prepared statements, which helps prevent SQL injection attacks.
Consistency: PDO provides a consistent interface across databases.
Performance: PDO is lightweight and fast.

PDO connection to a database

Before we begin fetching data from a database, we need to establish a connection to it using the PDO constructor. Here’s an example:

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

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

In this example, we’re connecting to a MySQL database named “mydatabase” that’s running on the same server as the PHP script. The username and password provided in the code are the credentials required to access the database.

Once a connection has been established, we can start fetching data from the database.

PDO fetch modes

PDO fetch modes determine how the fetch method should return the results. There are five default fetch modes:

PDO::FETCH_ASSOC: returns an associative array where the field names are the keys.
PDO::FETCH_BOTH: returns an array with both associative and numeric keys.
PDO::FETCH_NUM: returns a numeric array indexed by column number.
PDO::FETCH_OBJ: returns an object with property names that correspond to the column names.
PDO::FETCH_CLASS: returns an instance of the specified class with properties that correspond to the column names.

We’ll be using PDO::FETCH_ASSOC for our examples in this article.

PDO fetch methods

PDO provides a number of methods for fetching data from a database. Here are some of them:

PDO::query

The query method is used to execute a SQL statement that returns a result set, such as SELECT. Here’s an example:

$stmt = $conn->query('SELECT * FROM users');

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // Do something with the row
}

This code executes a SELECT statement on a table named “users” and retrieves all the rows in the table. It then loops through each row, assigning it to the $row variable.

PDO::prepare

The prepare method is used to prepare a SQL statement for execution. This method returns a PDOStatement object, which we can use to execute the statement. Here’s an example:

$stmt = $conn->prepare('SELECT * FROM users WHERE id = :id');
$stmt->execute(['id' => 1]);

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // Do something with the row
}

This code prepares a SELECT statement that selects a user by their ID. It then executes the statement, passing in an ID of 1. Finally, it loops through the results, assigning each row to the $row variable.

PDO::fetchAll

The fetchAll method is used to retrieve all the rows returned by a SELECT statement as an array. Here’s an example:

$stmt = $conn->prepare('SELECT * FROM users');
$stmt->execute();

$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

This code executes a SELECT statement and retrieves all the rows as an array. The array is saved to the $rows variable for later use.

PDO::fetchColumn

The fetchColumn method is used to retrieve a single column from a result set. Here’s an example:

$stmt = $conn->prepare('SELECT COUNT(*) FROM users WHERE active = 1');
$stmt->execute();

$count = $stmt->fetchColumn();

This code executes a SELECT statement that counts the number of active users in a table. It then retrieves the single column of data returned by the statement (in this case, the count) and saves it to the $count variable.

Conclusion

In this article, we’ve shown you how to fetch data from a database using PDO in PHP. We’ve covered the basics of establishing a database connection, setting fetch modes, and using a number of fetch methods to retrieve data. We hope this helps you get started with using PDO to fetch data in your PHP applications!

PDO Connection to a database

In order to establish a connection to the database with PDO, we must use the PDO constructor. The constructor requires three parameters:

Data Source Name or DSN: a string that contains the database driver name, the host name where the database server is located, and the name of the database.
Username: a username to connect the database.
Password: password to connect the database.

The code below is an example of a MySQL connection:

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

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

In this code block, we have provided the DSN, username, and password to connect to the MySQL database with PDO. We also added try-catch blocks to handle connection errors, in this case, it will output the error message.

After successfully connecting to the database, we can start fetching the data.

PDO fetch methods

The PDO extension comes with a variety of fetching methods to enable us to fetch data from a database in many ways. Here’s a brief overview of PDO fetch methods:

PDO::query() Method

$stmt = $conn->query('SELECT * FROM users');

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // Do something with the row
}

The PDO::query() method is used to fetch a complete set of rows returned by a SELECT statement. The method returns a PDOStatement object which can be used to access individual rows from the result set using the fetch() method. It executes the SQL query and retrieves all the rows in the result set.

PDO::fetch() Method

$stmt = $conn->prepare('SELECT * FROM users WHERE user_id = :user_id');
$stmt->execute(['user_id' => 1]);

$row = $stmt->fetch(PDO::FETCH_ASSOC);

The PDO::fetch() method is used to retrieve a single row from the result set. It returns an array with the row’s data, or it returns FALSE if there is no more data in the set. Here, we prepared the SQL query with the prepare() method. The execute() method then executes the query by passing an array of values. The fetch() method retrieves a single row from the result set and returns it as an associative array.

PDO::fetchAll() Method

$stmt = $conn->prepare('SELECT * FROM users WHERE active = :active');
$stmt->execute(['active' => 1]);

$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

The PDO::fetchAll() method is used to retrieve all the rows returned by a SELECT statement and returns an array that contains all the rows. The method takes only one argument, which is the fetch style. In the code above, we have set the fetch style to PDO::FETCH_ASSOC so that it will return an associative array.

PDO::fetchColumn() Method

$stmt = $conn->prepare('SELECT COUNT(*) FROM users WHERE active = :active');
$stmt->execute(['active' => 1]);

$count = $stmt->fetchColumn();

The PDO::fetchColumn() method is used to retrieve a single column from a result set. It returns the value of a single column from the first row in the result set, or FALSE if there are no rows. Only the column value is returned, not the entire row.

Conclusion

Using PDO for fetching data from a database provides several advantages. Not only it simplifies the development process by providing a uniform API but also ensures the security of the data by preventing SQL injection. PDO is quite intuitive and easy to use in comparison to the traditional MySQL extension. We hope the examples provided in this article give you a good start towards using PDO for your database operations.

php pdo fetch from db with code examples

Popular questions

Tag

Leave a Reply Cancel reply

Popular questions

Tag

Leave a Reply Cancel reply

Related Posts