{ Fernando Horta } → Projects

Overview

This project consists of an object-oriented PHP system designed to manage and render data for Magic: The Gathering cards. The system employs a layered architecture, separating all data handling (queries, retrieval) from the presentation layer (HTML rendering, filtering).

The collection currently holds 1,706 cards: 897 different printings of 691 unique cards, spread over 51 sets from 1994 to 2025, plus three playable decks. At the latest monthly price update, the whole binder is worth around US$ 2,380.

 

The core functions are managed by two classes:

1. MtgRepository: Handles all database operations, querying the mtg_cards table and returning structured results.
2. MtgRenderer: Responsible for rendering HTML for filters, card lists, and card details, combining PHP output buffering with HTML templates for flexibility.

⚙️ Technical Stack

Architecture & Design

The architecture is built around clear separation of concerns using two primary classes:

• Database Access: The MtgRepository class requires and utilizes a Database.php file to establish a connection via PDO (Database::getConnection()). This repository handles complex data retrieval, such as fetching cards with filters, limits, and offsets, or retrieving print variants using the oracle_id.
• Presentation Logic: The MtgRenderer manages the display. It requires an instance of MtgRepository during its construction. It uses PHP output buffering (ob_start() and ob_get_clean()) to build complex HTML structures, such as filter forms and card grids.
• Input Sanitization: A private sanitize() method is implemented within the MtgRenderer to protect against Cross-Site Scripting (XSS).
• Data Model: Each row in mtg_cards is one printing, keyed by its Scryfall id, with the owned quantity, the oracle_id shared by every printing of the same card, and the latest prices stored as JSON. Decks live in mtg_decks, and mtg_cards_decks links cards to decks with a per-deck quantity, so the same printing can sit in more than one deck.
• Collection Management: Cards are added from the admin panel by Scryfall id (or set code and collector number), one at a time or in bulk from a CSV list of quantities and Scryfall ids. Unknown cards are fetched from Scryfall, their images are cached locally in three sizes (large, png and art_crop), and cards already in the collection just have their quantity increased. Decks are built in the admin with a searchable card picker.

Features

The system provides robust features for filtering and displaying card data:

1. Filter Generation: The system dynamically fetches distinct card properties from the database to build HTML filter forms, including types, rarities, and editions (sets).
2. SQL Filtering: It can construct SQL-safe WHERE conditions by processing user input from POST/GET parameters for type, edition, rarity, and color identity. For colors, it uses a defined mapping of single-letter keys (U, B, R, W, G) to their full names (Blue, Black, Red, White, Green).
3. Detailed Card View: The getCard() method retrieves detailed card information, including prices, oracle_text, released_at, and rarity.
4. Symbol Rendering: When displaying the card's oracle_text, the renderer processes placeholders (e.g., {W}, {T}) and replaces them with the contents of the corresponding SVG files stored in a designated folder. It sanitizes the SVG content and adds inline styling to scale it to text height.
5. Variant Display: It supports listing other printings of the same card by querying all cards sharing the same oracle_id.
6. Set Collection Display: It can retrieve a random sample of 12 cards from the same collection/set using ORDER BY RAND().
7. Deck Filter: Picking a deck switches the list to a query joined through mtg_cards_decks, showing the deck's quantities and grouping the cards by type, the way a decklist is usually read.
8. Free Search: A text search across card name, set name, type line and oracle text, combined with any of the other filters.
9. Prices: Each card page shows its current USD price (falling back to the foil price when Scryfall only lists that one), the month it was added to the collection and when its price was last updated. A monthly job refreshes every card's price from Scryfall.
10. Pagination and Sharing: The list is paginated with the site's shared components, and every filtered search can be shared as a link.

Challenges & Solutions

• Challenge: Accurately fetching general card types (e.g., "Creature") when the database field (type_line) might contain subtypes (e.g., "Creature — Goblin").
  • Solution: The getTypes() method uses a CASE WHEN statement and string manipulation functions (LOCATE and SUBSTRING) in SQL to strip the subtype if an '—' is found, returning only the primary type.
• Challenge: Securely passing dynamic variables (like pagination controls) into SQL queries.
  • Solution: The getCards() method utilizes prepared statements ($this->pdo->prepare($sql)) and explicitly binds variables using PDO::PARAM_INT for the $limit and $offset parameters.
• Challenge: Rendering complex text containing custom card symbols.
  • Solution: The displayCard() method uses preg_replace_callback to find symbols enclosed in braces ({...}) and replaces them with sanitized, injected SVG code fetched from local files, ensuring the symbols scale appropriately within the text.
• Challenge: Importing hundreds of cards and refreshing hundreds of prices without hitting Scryfall's rate limit (HTTP 429).
  • Solution: Requests go out one at a time with a short pause between them (usleep(), 0.25 to 0.3 seconds) and an identifying User-Agent, as Scryfall asks. Card lookups only happen for cards not yet in the database, and each batch runs inside a single transaction.
• Challenge: Double-faced cards have no top-level image on Scryfall.
  • Solution: The importer falls back to the card_faces array and takes the image from the card faces, so transform and modal cards still get artwork.

Code Snippets

The core responsibility of the repository is defined in its initialization:

require_once __DIR__ . '/Database.php';

/**
* Class MtgRepository
*
* Handles all database operations for Magic: The Gathering card data.
* Responsible for querying the mtg_cards table and returning structured results.
*/
class MtgRepository {

private PDO $pdo;

public function __construct() {
$this->pdo = Database::getConnection();
}
// ... methods for fetching data ...

The method for fetching distinct card types demonstrates how complex SQL logic is used to clean data:

SELECT DISTINCT
  CASE WHEN LOCATE('—', type_line) > 0 THEN SUBSTRING(type_line, 1, LOCATE('—', type_line) - 1)
       ELSE type_line
  END AS value
FROM mtg_cards
ORDER BY type_line;

Results & Learnings

This project demonstrates proficiency in building a robust, data-driven application using Object-Oriented PHP. Key learning outcomes include managing complex data transformation within SQL, implementing secure and parameterized queries, and employing advanced text processing with preg_replace_callback to integrate visual assets (SVGs) into dynamic text content. It provides a scalable structure for handling large, complex datasets like those found in Magic: The Gathering.

Future roadmap

• ✅ Pagination for card list
• ✅ Monthly card price update
• ✅ Add the remaining cards (took a while, they were not physically with me)
• ✅ Deck builds, filterable on the card list

go to magic the gathering card list