PHP - Fetching Data with PDO

Fetching data with PDO (PHP Data Objects) is a critical aspect of working with databases in advanced PHP applications. PDO offers a secure and efficient way to retrieve data from databases while minimizing the risk of SQL injection. In this explanation, I'll cover how to fetch data using PDO in detail.

Preparing the Connection:

Before fetching data, you need to establish a connection to the database using PDO. Here's an example of how you can set up a PDO connection:

$dsn = "mysql:host=localhost;dbname=mydatabase";
$username = "your_username";
$password = "your_password";
try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Replace localhost, mydatabase, your_username, and your_password with your actual database details.

Fetching Data using PDO:

Here's an example of fetching data using PDO:

$sql = "SELECT * FROM users";
$stmt = $pdo->query($sql);
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // Process each row
   echo "Username: " . $row['username'] . "
";
    echo "Email: " . $row['email'] . "
";
}
?>

In this code:

$sql contains the SQL query you want to execute.

query() method is used to execute the query and return a statement object.

The fetch() method retrieves rows from the result set one by one in the specified fetch mode (PDO::FETCH_ASSOC in this case).

Fetch Modes:

PDO provides various fetch modes to determine how fetched data is presented:

PDO::FETCH_ASSOC: Fetches rows as associative arrays.

PDO::FETCH_OBJ: Fetches rows as objects.

PDO::FETCH_BOTH: Fetches rows as both associative arrays and objects.

Fetching All Rows at Once:

You can also fetch all rows from the result set at once using fetchAll():

$sql = "SELECT * FROM users";
$stmt = $pdo->query($sql);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
    // Process each row
   echo "Username: " . $row['username'] . "
";
    echo "Email: " . $row['email'] . "
";
}
?>

Parameterized Queries:

For added security, use parameterized queries to fetch data based on user input. This helps prevent SQL injection:

$username = $_GET['username']; // User input
$sql = "SELECT * FROM users WHERE username = :username";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':username', $username);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
if ($row) {
   echo "Username: " . $row['username'] . "
";
   echo "Email: " . $row['email'] . "
";
} else {
    echo "User not found.";
}
?>