LEARN TO CODE | PHP | 03 FILES AND DATABASES
03 Files and databases: challenges
Demonstrate the complete journey from stored data through PHP to browser output, including validated, targeted database changes.
START HERE | INDEPENDENT PRACTICE
Use the Section 03 example files, a current PHP runtime with PDO MySQL and a local MariaDB/MySQL database. Run the setup script in a fresh practice environment and keep its account details consistent with private/config.php.
Attempt tasks before revealing guidance. Check stored data as well as page messages and record expected versus actual outcomes. Ticks last while this page is open.
CHALLENGE 01 | A KNOWN TEXT FILE
Read the private welcome file and preserve its line breaks as escaped text.
Need a hint?
Use a fixed __DIR__-based path.
Reveal guidance
Use files.php. Keep the path independent of request input, check false distinctly from empty text and escape within pre. A pre element does not disable HTML interpretation.
CHALLENGE 02 | JSON SYNTAX VERSUS SHAPE
Test malformed JSON, a valid scalar root and a string capacity.
Need a hint?
Decoding and validation are different stages.
Reveal guidance
Malformed JSON throws JsonException. A scalar root fails the list check; a string capacity fails is_int. Do not report any of them as a successfully loaded empty directory.
CHALLENGE 03 | CONNECT WITH THE RIGHT ACCOUNT
Create the practice schema and application user, then load connection.php.
Need a hint?
PHP server, driver and database service are separate.
Reveal guidance
Confirm mysql is in the PDO driver list, the database service is running and config.php matches the account’s host, port and password. Use the application account, not the administrator, in PHP.
CHALLENGE 04 | KEEP SECRETS OUT OF THE RESPONSE
Explain why config.php belongs outside public and test that the browser cannot directly retrieve it under the intended document-root setup.
Need a hint?
The server’s root, not a folder name alone, determines what it serves.
Reveal guidance
Start with -t public. A request for /private/config.php should not expose that sibling file. Do not print configuration in diagnostics. Keep the filled config out of shared source control.
CHALLENGE 05 | FOLLOW A PREPARED SEARCH
Search for robot and an apostrophe-bearing string, then explain each stage.
Need a hint?
Trace form → PHP value → bound pattern → result rows → escaped output.
Reveal guidance
Use search.php. Apostrophes remain data and do not alter SQL syntax. Pattern characters still have LIKE meaning because this example intentionally offers pattern search.
CHALLENGE 06 | LOOK UP ONE ID
Write a positive-ID lookup and distinguish invalid input from a valid but absent ID.
Need a hint?
Validate first, then use prepare/execute and fetch === false.
Reveal guidance
Return a validation message for malformed or negative input. For an accepted ID, bind the value to a fixed WHERE comparison. fetch returning false means no matching record. Do not use SELECT rowCount as the existence test.
CHALLENGE 07 | SAFE SORT CHOICES
Offer name or capacity sorting without accepting arbitrary SQL.
Need a hint?
Map options to complete fixed query strings.
Reveal guidance
Use the fixed allow-list from 03.3. A bound placeholder cannot stand for a column identifier or direction keyword. Unknown choices should fall back or be rejected deliberately.
CHALLENGE 08 | CREATE AND CHECK
Add a club containing & in its name, then try a fractional capacity.
Need a hint?
Validate before INSERT and escape after SELECT.
Reveal guidance
The valid club is stored and displayed as ordinary text after a redirect. The invalid capacity fails the integer/range validation and leaves stored records unchanged.
CHALLENGE 09 | UPDATE ONLY ONE ROW
Load a known ID, change its capacity and show another record stayed unchanged.
Need a hint?
The ID travels through the edit link and hidden field, but must be revalidated.
Reveal guidance
Read the record with GET, submit an update POST with valid token and ID, then inspect both rows. The prepared UPDATE has a targeted WHERE clause. Repeat with unchanged values and confirm it is not misreported as a missing record.
CHALLENGE 10 | REJECT A DELETE
Attempt deletion without confirmation, then without a valid token. Confirm no deletion occurred.
Need a hint?
User intent and CSRF are separate checks.
Reveal guidance
The first attempt fails confirmation and the second receives 403. Both preserve the record. These mechanisms still do not implement user authentication or per-record authorisation.
CHALLENGE 11 | CONFIRMED DELETE
Delete one selected test record with confirmation and a valid token. Refresh the result.
Need a hint?
Use POST for the change and follow with a 303 GET.
Reveal guidance
The chosen ID disappears; other rows remain. Refreshing the redirected page repeats only the read. Inspect the database to verify the result.
CHALLENGE 12 | CAPSTONE: A DATA-DRIVEN DIRECTORY
Run the complete local site, demonstrate all four CRUD operations and add a validated read-only minimum-capacity report. Submit a test record and explain the system’s access limitation.
Need a hint?
Build on the fixed connection and request patterns rather than bypassing them.
Reveal guidance
Expected evidence includes file-read failure handling, driver and account setup, prepared query values, escaped output, targeted writes and CSRF rejection. State that the application is local single-user practice without login. The database account’s grants limit SQL capabilities; they do not identify individual website users.
STRETCH | REQUIRE A TRANSACTION
Propose an operation that needs two dependent writes, such as creating a club and an initial meeting record. Explain the schema relationship and why both should commit together or be rolled back.
Do not implement a fake transaction around unrelated actions merely to use the syntax. Define the business operation and relevant database constraints first.
FINISH | EXPLAIN WHAT IS NOT IMPLEMENTED
Explain prepared statements versus validation, database account grants versus website-user permissions, and private filesystem paths versus public URLs.
The next section develops error configuration, login concepts and larger projects. Keep this demonstration local and use it as evidence of the specific skills you can explain and test.
KEEP LEARNING
Previous page 04.1 Error handling and configuration
Official references: JSON decoding · PDO connections · Prepared statements · MariaDB grants