Aim

To select all records from a table and display them in tabular form.

Theory

Reading rows with PDO is a two-step: run the query, then fetch. $pdo->query("SELECT ...") returns a statement object; fetch() pulls one row per call (memory-friendly for huge results), while fetchAll() returns every row at once as an array — fine for small lab tables. The fetch mode decides each row's shape: PDO::FETCH_ASSOC gives a column-name-keyed array ($r["name"]), whereas the default FETCH_BOTH duplicates every value under both a name and a number. Listing columns explicitly (SELECT id, name, email, course) beats SELECT * in real code — stable order, no surprise columns after an ALTER — and ORDER BY id makes the output deterministic.

Displaying is just iteration over the same $rows array; only the renderer differs by medium. On the CLI, alignment must be computed by hand: find each column's widest value, then pad every cell to that width. On the web the identical loop would emit <table>, <tr> and <td> tags instead — with one extra rule: every cell must pass through htmlspecialchars(), because database content is stored input and echoing it raw invites stored XSS.

Requirements

• XAMPP/WAMP with Apache and PHP 8.x (or PHP CLI for the compiler run)
• PHP PDO extension (pdo_sqlite; pdo_mysql when pointed at MySQL)
• Code editor (VS Code); browser or terminal

Procedure

1. Start Apache from the XAMPP Control Panel (not needed for a pure CLI run).
2. Save the snippet as p26_select_display.php in C:\xampp\htdocs\wbplab.
3. Run php p26_select_display.php in a terminal — the bordered ASCII table aligns perfectly there.
4. Or open http://localhost/wbplab/p26_select_display.php; a browser collapses whitespace, so for web use either wrap the output in <pre> or replace renderTable()'s echoes with <table border='1'> markup around the same foreach.

Explanation of the Code

• The script first seeds its own data: CREATE TABLE students plus three prepared-statement inserts (Aman/BCA, Riya/BBA, Karan/BCom) so the SELECT has rows to show.
• query(...)->fetchAll(PDO::FETCH_ASSOC) fetches all rows, ordered by id.
• renderTable() handles the empty case first, printing (no records).
• array_keys($rows[0]) derives the headers from the first row's keys — the function adapts to any result set, not just students.
• The $w loop computes each column's display width: start at the header's length, then max() against every value's length.
• $border is built as + plus str_repeat("-", $w[$c] + 2) per column (the +2 covers the padding spaces); it prints above the header, below it, and after the last data row.
• Each cell is str_pad()-ed to its column width, so the | separators line up regardless of value lengths.
• Finally count($rows) reports the total.

Expected Output

The terminal shows All records from 'students': followed by a bordered table: a +----+-------+ ... + border line, the header row | id | name | email | course |, the border again, three aligned data rows — | 1 | Aman | aman@example.com | BCA |, | 2 | Riya | riya@example.com | BBA |, | 3 | Karan | karan@example.com | BCom | — a closing border, then Total: 3 record(s).. The email column is the widest (17 characters, set by karan@example.com), and every pipe lines up thanks to the padding.

🎯 Viva Questions

1. fetch() vs fetchAll()? fetch() returns one row per call (streaming, low memory); fetchAll() returns the whole result as an array in one go.
2. What does PDO::FETCH_ASSOC do? Returns each row keyed by column name only — the default FETCH_BOTH duplicates values under numeric indexes too.
3. **Why list columns instead of SELECT ?* Guaranteed column order, no accidental extra columns after schema changes, and less data transferred.
4. How would this render on a web page? The same foreach over $rows, but emitting <table>, <tr> and <td> tags instead of padded text.
5. Why is htmlspecialchars() mandatory when echoing rows into HTML? Stored values may contain markup or script; escaping on output prevents stored XSS.
6. What role does str_pad() play here? It pads every cell to its column's maximum width so the | separators align vertically.

CO Mapping

CO1, CO2