Php pdo query fetch all

fetch

Summary: in this tutorial, you’ll learn how to use the PHP fetch() method of the PDOStatement object to fetch a row from the result set.

Introduction to the PHP fetch() method

The fetch() is a method of the PDOStatement class. The fetch() method allows you to fetch a row from a result set associated with a PDOStatement object.

Internally, the fetch() method fetches a single row from a result set and moves the internal pointer to the next row in the result set. Therefore, the subsequent call to the fetch() method will return the next row from the result set.

To fetch all rows from a result set one by one, you typically use the fetch() method in a while loop.

The following shows the syntax of the fetch() method:

public function fetch( int $mode = PDO::FETCH_DEFAULT, int $cursorOrientation = PDO::FETCH_ORI_NEXT, int $cursorOffset = 0 ): mixedCode language: PHP (php)

The fetch() method accepts three optional parameters. The most important one is the first parameter $mode.

The $mode parameter determines how the fetch() returns the next row. The $mode parameter accepts one of the PDO::FETCH_* constants. The most commonly used modes are:

• PDO::FETCH_BOTH – returns an array indexed by both column name and 0-indexed column number.
• PDO::FETCH_ASSOC – returns an array indexed by column name
• PDO::FETCH_CLASS – returns a new class instance by mapping the columns to the object’s properties.

The fetch() method returns a value depending on the $mode parameter. It returns false on failure.

Using the PHP fetch() method with the query() method

If a query doesn’t accept a parameter, you can fetch a row from the result set as follows:

• First, execute the query by calling the query() method of the PDO object.
• Then, fetch each row from the result set using the fetch() method and a while loop:

The following example shows how to use the fetch() method to select each row from the books table:

 // connect to the database to get the PDO instance $pdo = require 'connect.php'; // execute a query $sql = 'SELECT book_id, title FROM books'; $statement = $pdo->query($sql); // fetch the next row while (($row = $statement->fetch(PDO::FETCH_ASSOC)) !== false) < echo $row['title'] . '
'; >Code language: PHP (php)

First, connect to the bookdb database using the connect.php script.

$pdo = require 'connect.php';Code language: PHP (php)

Second, execute a query that selects the book_id and title from the books table:

$sql = 'SELECT book_id, title FROM books'; $statement = $pdo->query($sql);Code language: PHP (php)

Third, fetch each row from the result set until there’s no more row to fetch and display the book title in each iteration.

while (($row = $statement->fetch(PDO::FETCH_ASSOC)) !== false) < echo $row['title'] . '
'; >Code language: PHP (php)

Using the fetch() method with a prepared statement

When a query accepts one or more parameters, you can fetch the next row from the result set as follows:

• First, execute the prepared statement.
• Second, fetch the next row from the result set using the fetch() method.

The following example shows how to fetch() to fetch a book from the books table with publisher id 1:

 // connect to the database to get the PDO instance $pdo = require 'connect.php'; $sql = 'SELECT book_id, title FROM books WHERE publisher_id =:publisher_id'; // prepare the query for execution $statement = $pdo->prepare($sql); // execute the query $statement->execute([ ':publisher_id' => 1 ]); // fetch the next row while (($row = $statement->fetch(PDO::FETCH_ASSOC)) !== false) < echo $row['title'] . PHP_EOL; >Code language: PHP (php)

Summary

Источник

fetchAll

Summary: in this tutorial, you’ll learn how to use the PHP fetchAll() method of the PDOStatement object to return an array containing all rows of a result set.

Introduction to the PHP fetchAll() method

The fetchAll() is a method of the PDOStatement class. The fetchAll() method allows you to fetch all rows from a result set associated with a PDOStatement object into an array.

The following shows the syntax of the fetchAll() method:

public function fetchAll(int $mode = PDO::FETCH_DEFAULT): arrayCode language: PHP (php)

The $mode parameter determines how the fetchAll() returns the next row. The $mode parameter accepts one of the PDO::FETCH_* constants. The most commonly used modes are:

• PDO::FETCH_BOTH – returns an array indexed by both column name and 0-indexed column number. This is the default.
• PDO::FETCH_ASSOC – returns an array indexed by column name
• PDO::FETCH_CLASS – returns a new class instance by mapping the columns to the object’s properties.

The fetchAll() method returns an array that contains all rows of a result set.

If the result set is empty, the fetchAll() method returns an empty array. If the fetchAll() fails to fetch data, it’ll return false .

It’s important to notice that if you have a large result set, the fetchAll() may consume a lot of server memory and possibly network resources. To avoid this, you should execute a query that retrieves only necessary data from the database server.

Using the PHP fetchAll() method with the query() method

If a query doesn’t accept a parameter, you can fetch all rows from the result set as follows:

• First, execute the query by calling the query() method of the PDO object.
• Then, fetch all rows from the result set into an array using the fetchAll() method.

The following example illustrates how to use the fetchAll() method to select all rows from the publishers table:

 // connect to the database to get the PDO instance $pdo = require 'connect.php'; $sql = 'SELECT publisher_id, name FROM publishers'; // execute a query $statement = $pdo->query($sql); // fetch all rows $publishers = $statement->fetchAll(PDO::FETCH_ASSOC); // display the publisher name foreach ($publishers as $publisher) < echo $publisher['name'] . '
'; >Code language: PHP (php)

1.McGraw-Hill Education 2.Penguin/Random House 3.Hachette Book Group 4.Harper Collins 5.Simon and SchusterCode language: plaintext (plaintext)

$pdo = require 'connect.php';Code language: PHP (php)

Second, execute a query that selects the publisher_id and name from the publishers table:

$sql = 'SELECT publisher_id, name FROM publishers'; $statement = $pdo->query($sql);Code language: PHP (php)

Third, fetch all rows from the result set into an array:

// display the publishers foreach ($publishers as $publisher) < echo $publisher['name'] . '
'; >Code language: PHP (php)

Using the fetchAll() method with a prepared statement

If a query accepts one or more parameters, you can:

• First, execute a prepared statement.
• Second, fetch all rows from the result set into an array using the fetchAll() method.

The following example shows how to use fetchAll() to fetch all publishers with the id greater than 2:

 // connect to the database to get the PDO instance $pdo = require 'connect.php'; $sql = 'SELECT publisher_id, name FROM publishers WHERE publisher_id > :publisher_id'; // execute a query $statement = $pdo->prepare($sql); $statement->execute([ ':publisher_id' => 2 ]); // fetch all rows $publishers = $statement->fetchAll(PDO::FETCH_ASSOC); // display the publishers foreach ($publishers as $publisher) < echo $publisher['publisher_id'] . '.' . $publisher['name'] . '
'; >Code language: PHP (php)

3.Hachette Book Group 4.Harper Collins 5.Simon and SchusterCode language: plaintext (plaintext)

Summary

• Use the fetchAll() method to fetch a row from the result set associated with a PDOStatement object.

Источник