D&D Character Sheets
A PHP and MySQL based system for storing (and updating) D&D 5e character sheets
About this project:
D&D Character Sheets
This project is a still evolving section of the DM (Dungeon Master) toolset I use while running Dungeons and Dragons campaigns. It is used as a means of storing character data for my players without needing to maintain physical character sheets for each player. Some information is not yet available for upload into the databases, but those sections are not crucial to running most of my campaign activities.
The site uses PHP and a number of SQL queries to create page layouts of each character, their items, spells, gold supplies, and general information. The pages also provide a means to update, delete, and add new information without going through a SQL terminal.
Due to the public and not at all password protected nature of the example project, most (all) features are disabled. None of the submit buttons do anything at all besides reload the page. Despite all of the data being validated and sanitized on input and output, I'd rather be safe than sorry. If you'd like to know more, or see a working example, shoot me a message.
AJAX updating is coming soon.
PHP: PHP forms the backbone of this project. The language allows me to create a dynamic page that responds to the contents of the databases and allows for the information to be displayed in straight forward and relatively simple ways. PHP also interacts well with SQL through MySQLi and prepared statements, allowing easily contained complex queries involving changing value variables and all the loops you could ever hope for.
SQL: SQL is utilized in this project to retrieve, update, store, delete, and manipulate the data stored on the server's database. The straight forward nature of SQL tables allows for normalized forms to be set up so data is dealt with in the least complicated way as possible.
The first half of the PHP is mostly focused on sorting through potentially posted data from the forms and pages below. Each section, and some within the larger sections are made up of inputs and forms. The code at the top of the page checks to see if any of these input section types are being posted, sanitizes and organizes the data, and the using prepared statements, sends it to the database to be placed in the correct table (via that prepared statement query).
The repetitive nature of having multiple characters (in the example, three) with similar page load outs calls for a lot of looped data in constructing the page. Each character is marked by a personal character ID number, which is used across all of the tables in some fashion. Storing these IDs in arrays and using them within the SQL queries that grab the data to fill out the pages lets me save a tremendous amount of time. I don't need to fully build the page for each character, just once, and then I can let the looped queries fill out the pages, regardless of how many characters I might have in my database.
The latter half of the PHP is mainly used to build the HTML layout for the page display. Each section (Info, Items, Gold, Spells) is only built once, and is then looped through and filled out with each character's information, based on their numbered character ID. As mentioned above, this character ID acts as a primary key in most tables and relationship link to every other table in the database, through which elements and data entries can be targeted and placed correctly. All information coming from the database is also run through the htmlspecialchars function before being displayed.
The queries to the database are straight forward mixes of straight SQL and PHP arrays and variables containing targeting information (character/ spell/ whatever IDs) for where the information is to be stored or sent.
All posted data is sanitized with filters and pushed through prepared statements before being sent to the database. In theory this should protect the site from XSS and SQL injection attacks.
The database itself attempts to follow the 3 normal forms by avoiding partial and transient dependencies by splitting tables down so that in some manner they are all reachable and rely on character ID for storage and display purposes.