Stardew-Valley-Database

A full-stack relational database web application built with PHP and MySQL that models items, villagers, crops, recipes, artisan goods, and relationships from the game Stardew Valley.

The application allows users to explore predefined queries, view relations, and execute ad-hoc SQL queries against the database.

Note: This project was originally deployed on a university server for my CPSC 3300 Fundamentals of Databases Final Project. The database connection is no longer active. This repository contains the source code only. To run the project, a local MySQL database must be configured (see Running the Project section).

---

Technologies

• PHP
• MySQL
• HTML
• CSS
• phpMyAdmin (for database management)
• Apache (via XAMPP for local development)

---

Features

Here’s what you can do with Stardew Valley Database:

• View relational tables including:

  • Artisan Goods
  • Cooking
  • Crops
  • Fish
  • Items
  • Loved Gifts
  • Recipes
  • Villagers

• Execute predefined SQL queries demonstrating:

  • INNER JOIN operations
  • Aggregate functions (SUM, COUNT, AVG)
  • Subqueries
  • GROUP BY and HAVING clauses
  • LEFT/RIGHT OUTER JOIN

• Submit ad-hoc SQL queries through a form interface

• Explore relationships between game entities (e.g., villagers and their loved gifts, recipes and required ingredients)

---

The Process

This project was developed as a collaborative team effort. We began by designing the relational schema, identifying key entities in Stardew Valley and defining primary and foreign key relationships to maintain referential integrity.

We then implemented the schema in MySQL and populated the tables with structured data. Once the database was established, we built PHP scripts to connect the backend to the frontend, dynamically rendering relational data in the browser.

To demonstrate advanced database concepts, we implemented predefined queries featuring joins, aggregate functions, subqueries, and GROUP BY/HAVING clauses. We also developed an ad-hoc query interface to allow controlled execution of SELECT, INSERT, UPDATE, and DELETE operations.

Throughout development, we conducted end-to-end testing to validate query accuracy and database behavior.

My contributions focused on backend PHP integration, writing complex SQL queries, and ensuring database connectivity between the application and MySQL.

---

What I Learned

During this project, I’ve gained hands-on experience with:

• Relational Database Design

  • Designing normalized schemas
  • Defining primary and foreign keys
  • Maintaining referential integrity

• Advanced SQL

  • Multi-table joins
  • Aggregate functions
  • Subqueries
  • GROUP BY and HAVING clauses
  • Outer joins

• Backend Development with PHP

  • Connecting PHP to MySQL
  • Executing dynamic queries
  • Handling user input
  • Rendering query results in HTML

• Full-Stack Integration

  • Connecting front-end forms to backend logic
  • Debugging database-driven applications
  • Structuring multi-page PHP applications

Overall Growth

This project helped me understand how relational database theory translates into real-world web applications. It strengthened my ability to design schemas, write complex SQL queries, and integrate backend systems with frontend interfaces.

---

How Can It Be Improved?

• Refactor credentials to use environment variables (currently in progress)
• Add input validation and stronger SQL injection protection
• Improve UI/UX with modern CSS or a frontend framework
• Add search and filtering functionality
• Containerize with Docker for easier deployment
• Deploy with a managed MySQL database (e.g., cloud hosting)
• Add automated testing

---

Running the Project

To run the project in your local environment, follow these steps:

1. Install XAMPP (or another Apache + MySQL stack).

2. Clone the repository to your local machine:

   git clone https://github.com/yourusername/stardew-database.git
   

3. Move the project folder into your htdocs directory.

4. Start Apache and MySQL from XAMPP.

5. Create a new MySQL database (e.g., stardew_db) in phpMyAdmin.

6. Import the project’s SQL schema (if available).

7. Update the database connection in PHP files:

   $servername = "localhost";
   $username = "root";
   $password = "";
   $database = "stardew_db";

8. Open your browser and navigate to:

   http://localhost/stardew-database/project.html
   

---

Video

---