PHP Use PDO To Get Query Results

PDO (PHP Data Objects) is an abstraction layer that provides a consistent interface for interacting with various types of databases in PHP. After you've connected to your database using PDO (see the previous tutorials for details), you can use it to execute SQL queries and fetch the results.

Here is an example of how you can use PDO to get query results:

// Assuming you have already created a PDO instance $pdo
$stmt = $pdo->query('SELECT * FROM users');

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

In this example, we're executing a SQL query to select all records from the users table. The query method returns a PDOStatement object. We then loop over the result set using the fetch method of the PDOStatement object, which fetches the next row from the result set.

By default, the fetch method returns an array with both associative and numeric keys. If you want to get an associative array only, you can pass PDO::FETCH_ASSOC to the fetch method, like this:

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

To fetch all results at once, you can use the fetchAll method:

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

foreach ($rows as $row) {
    echo $row['name'] . "\n";
}

When you're executing a SQL query that includes user-supplied data, you should use prepared statements to prevent SQL injection attacks:

$stmt = $pdo->prepare('SELECT * FROM users WHERE email = ?');
$stmt->execute([$email]);
$user = $stmt->fetch();

In this example, the ? in the SQL query is a placeholder that gets replaced with the value of the $email variable when the statement is executed.

Remember, after you're done using the PDOStatement object, it's a good idea to call the closeCursor method to free up the resources associated with the statement:

$stmt->closeCursor();

Executing SQL queries and fetching results with PDO:

Use query() to execute SQL queries and fetch results.

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

while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    echo $row['username'] . "<br>";
}

Binding parameters in prepared statements with PDO:

Use prepared statements and bind parameters for secure queries.

$sql = "SELECT * FROM users WHERE id = :id";
$stmt = $pdo->prepare($sql);

$id = 1;
$stmt->bindParam(':id', $id, PDO::PARAM_INT);

$stmt->execute();

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

Fetching single and multiple rows from PDO query results:

Use fetch() for single rows and fetchAll() for multiple rows.

// Fetch single row
$user = $result->fetch(PDO::FETCH_ASSOC);

// Fetch all rows
$users = $result->fetchAll(PDO::FETCH_ASSOC);

Handling different result set formats with PDO in PHP:

PDO supports different result set formats (associative, numeric, both).

// Associative array
$userAssoc = $result->fetch(PDO::FETCH_ASSOC);

// Numeric array
$userNumeric = $result->fetch(PDO::FETCH_NUM);

// Both associative and numeric
$userBoth = $result->fetch(PDO::FETCH_BOTH);

Retrieving and working with metadata from PDO results:

Access result set metadata using getColumnMeta().

$stmt = $pdo->prepare("SELECT * FROM users");
$stmt->execute();

$meta = $stmt->getColumnMeta(0);
echo "Column name: " . $meta['name'];

Fetch styles and modes in PHP PDO queries:

PDO provides various fetch styles and modes.

// Fetch as an object
$userObj = $result->fetch(PDO::FETCH_OBJ);

// Fetch as a custom class
$userClass = $result->fetch(PDO::FETCH_CLASS, 'User');

Using fetchAll() and fetchColumn() with PDO:

fetchAll() retrieves all rows, and fetchColumn() retrieves a single column.

// Fetch all rows
$users = $result->fetchAll(PDO::FETCH_ASSOC);

// Fetch a single column
$count = $result->fetchColumn();

Streaming large result sets with PDO in PHP:

Use PDO::MYSQL_ATTR_USE_BUFFERED_QUERY to stream large result sets.

$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

$sql = "SELECT * FROM large_table";
$result = $pdo->query($sql);

while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    // Process row without loading the entire result set into memory
}