How to Make Queries with PDO in PHP

A query with PDO allows you to send an SQL statement to the database and retrieve its result.

Once you have your connected $pdo object, the simplest way to execute an SQL query that does not contain user variables is by using the ->query() method.

This method returns a special object called PDOStatement, which holds the raw results, ready to be processed.

Executing the Query

Let’s assume we have a usuarios table.

// Execute the direct SQL query
// WARNING! Use query() ONLY if the SQL statement has no external variables.
$sql = "SELECT nombre, email, edad FROM usuarios";
$stmt = $pdo->query($sql);

At this point, $stmt does not contain the data (the arrays), but rather a “pointer” or cursor that points to the database, waiting for you to tell it how you want to read the information.

Ways to Read: fetch vs fetchAll

This is where many people get confused. How do I extract the data from that $stmt object? There are mainly two ways:

A. fetch(): One by one (The efficient way)

The fetch() method retrieves the next row from the result set. Each time you call it, the pointer moves down one line. When there are no more rows, it returns false.

It is ideal for use inside a while loop.

• Advantage: Very memory efficient. It only loads one row at a time into RAM. Ideal if you have 10,000 users.

echo "<ul>";
// "As long as I can fetch a row ($fila), keep executing the loop"
while ($fila = $stmt->fetch()) {
    // Since we configured PDO::FETCH_ASSOC, we access by column name
    echo "<li>" . $fila['nombre'] . " (" . $fila['email'] . ")</li>";
}
echo "</ul>";

B. fetchAll(): All at once (The convenient way)

This method downloads all rows from the database and puts them into a single giant PHP array.

• Advantage: You have all the data in one variable to use whenever you want.
• Disadvantage: Consumes a lot of memory. If the table has 100,000 rows, your server could crash (Memory Limit Error). Only use it when you know there are few results (e.g., categories, configurations).

// Bring everything into memory
$todosLosUsuarios = $stmt->fetchAll();

// Now we can use a normal foreach loop
foreach ($todosLosUsuarios as $usuario) {
    echo $usuario['nombre'] . "<br>";
}

Counting Results with COUNT(*)

Sometimes you don’t want the data, you just want to know how many there are.

$stmt = $pdo->query("SELECT COUNT(*) FROM productos WHERE stock > 0");
$cantidad = (int) $stmt->fetchColumn();

if ($cantidad > 0) {
    echo "We have $cantidad products for sale.";
} else {
    echo "No stock.";
}