Unit 8 Project: Database-Driven Website with API Integration

After completing the lessons in this unit, it’s time to apply all those concepts in a cohesive capstone project. In Unit 8’s project, you will transform your small business website into a fully database-driven application with an external data feed, significantly enhancing its functionality and professionalism.

Project Overview:
You’ll integrate a MySQL database into your website and refactor existing features to use it. All key site content – for example, the list of services offered, business hours, or other business info that might change – will move into database tables. Rather than being hard-coded in PHP files, these pieces of information will be loaded from the database, making updates easier (through database edits or a simple admin form) and the content more consistent. In addition, you will enhance the contact page: when a visitor submits the contact form, their message will be stored in the database. To manage this data, you’ll create an admin dashboard page where the business owner (logged in via the Unit 6 authentication system) can review all contact messages at any time.

Beyond the internal data, you’ll also incorporate an external API into your site. For example, you might fetch the current weather for your business’s city and display it on the homepage, or show the latest review count or social media posts. This will be done using PHP’s cURL to make an HTTP request to a public API, then parsing the response (likely JSON) and outputting a friendly snippet on the site (e.g. “🌤️ It’s currently 72°F and sunny in Springfield!” or “⭐ Our Yelp rating is 4.5 stars out of 5”). This external data element adds a modern, dynamic touch to your project and demonstrates your ability to integrate third-party services – a key skill for any PHP developer.

Capstone Project Requirements:
Let’s break down the specific requirements and features you need to implement for Unit 8:

• Database Setup & Configuration:
  • Design the database: Create a new MySQL database (if you haven’t already) and at least two tables to support the site’s data:
    • a Content table (or separate tables for different content types) to hold site information such as services (e.g. service name, description, price) and possibly business details (e.g. hours of operation, about page text). This makes the site’s textual content editable via the database.
    • a Messages table to store contact form submissions (fields might include name, email, message, date/time submitted, and maybe a “read” flag for admin use).
  • Connect via PDO: In your PHP code, use PDO to connect to the MySQL database. Store the connection details (host, db name, username, password) in a secure location (like a separate config file). Use proper PDO options (set error mode to exceptions, use prepared statements by default, etc.) to ensure reliable database operations. This PDO connection will be the foundation for all the data interactions in your site.
  • Legacy note (PEAR): While setting up the database connection, be aware that older PHP projects might have used PEAR or other libraries for database abstraction. In this project, we use PDO as the modern standard. (You don’t need to use PEAR, just recognize its name as a historical footnote in PHP development.)
• PDO CRUD Implementation:
  Now that the database is ready, update your site’s functionality to use it:
  • Dynamic content retrieval: Replace hard-coded content on your pages with database-driven content. For example, on your Services page, instead of an HTML list that you manually update, fetch the services from the Content table with a SELECT query and loop through the results to generate the list. Similarly, the About Us section or business hours displayed on the site could come from the database. This ensures the live site always uses the latest info from the DB.
  • Admin content management: Create a simple admin interface to edit content. This could be a form in the admin dashboard where the owner can update things like the business hours, about text, or add/edit services. When the admin submits changes, use INSERT or UPDATE SQL queries (via PDO prepared statements) to update the Content table. For example, you might build an “Add Service” form that inserts a new service record, or an “Edit Business Info” form that updates a row in a settings table. Providing this interface not only makes your project feel like a mini content-management system, but also gives practice with CREATE/UPDATE operations.
  • Storing contact messages: Modify the contact form handler so that each submission is inserted into the Messages table. Use an INSERT query to save the visitor’s name, email, message, and the date/time (you can use SQL NOW() or PHP date() for timestamp). After inserting, still show a confirmation to the user as before, but now you have a permanent record of inquiries.
  • Admin message viewing: On the admin dashboard, implement a page (e.g. messages.php or a section on admin home) that reads from the Messages table (SELECT query) and displays the stored messages in a neat format (perhaps a table listing the most recent inquiries). This way, the business owner can log in and see all contact requests at a glance. (Optional enhancements: allow marking messages as “read” or deleting spam messages, using UPDATE or DELETE queries — these can be nice extras to practice full CRUD.)
• Use of Design Patterns:
  To write more maintainable code, you’ll integrate design patterns into your project:
  • Singleton Pattern: Create a Database helper class (for example, Database.php) that uses the Singleton pattern to manage the PDO connection. The class would have a method like Database::getInstance() which returns the single PDO connection object. Internally, it ensures that if a connection already exists, it reuses it instead of creating a new one. Use this Singleton whenever you need to run a database query (so all parts of your app use the same shared PDO instance). This practice not only avoids having multiple connections open, but also exemplifies a common design pattern used in many frameworks.
  • Factory Pattern: Implement a simple Factory to handle object creation or data retrieval logic. For example, you might have different classes for your content types (maybe a Service class and a Message class that represent records from the database). You could create a ContentFactory that has methods like createService($id) or createMessage($id) which internally fetch the data from the database and return an object of the appropriate class. Another use-case: if you plan to support multiple external APIs (now or in the future), you could use a factory to instantiate the correct API client based on a parameter. In this unit, the Factory pattern usage can be modest – the goal is to practice the concept. Even a function or static method that chooses between creating one of two types of objects based on input would suffice to demonstrate understanding.
  • These patterns might feel abstract, but applying them in your project will show you their value. By having a Singleton database instance, your code to get a DB connection becomes as simple as $db = Database::getInstance(); anywhere in your project, rather than repeating connection code. And by using a Factory, you centralize how certain objects or data are created, making it easier to expand or change later. This is excellent practice for writing clean, enterprise-level code.
• External API Data with cURL:
  Enhance your site with real-time data from an external service using cURL:
  • Choose a public API: Pick an API that provides information relevant or interesting to your site. Common choices include a weather API (to show current weather in your area), a news API (latest headline related to your business field), or even a fun trivia or quote API for a “Quote of the Day” sidebar. Ensure the API is accessible without heavy authentication or use a free API key if required. For learning purposes, something like OpenWeatherMap (for weather) or a simple public JSON API will work great.
  • Fetch data using cURL: In PHP, use cURL functions to make a request to the API’s URL. For example, if using OpenWeatherMap, you’d construct a URL with your city and API key, then use cURL to send a GET request. Remember to set cURL options to return the response (CURLOPT_RETURNTRANSFER) and handle HTTPS if needed.
  • Process the response: Most modern APIs return data in JSON format. After getting the response string from cURL, use json_decode() to convert it to a PHP array or object. Extract the pieces of data you want to display. For instance, from a weather API response, you might pull out the temperature, conditions description, and an icon code.
  • Display on the site: Integrate this external data into an appropriate spot on your site. You could display weather info on the homepage or contact page (e.g., “It’s currently 18°C and cloudy in Springfield.”). If it’s reviews or social media stats, you might show it in a sidebar or footer (“🌟 Check out our Yelp page – 50+ reviews and a 4.5⭐ rating!”). Format it neatly and update any icons or units for clarity. This not only makes your site look dynamic and up-to-date, but also shows off your ability to mash up data from other sources.
  • Error handling: Be sure to handle cases where the API call fails or returns an error. For example, if the API is down or the internet connection drops, your code should not crash. You can detect if cURL didn’t get a valid response or if json_decode returns null, and in those cases, display a friendly message or simply hide the external data section. This way the user experience remains smooth even if the external service is unavailable.
• Final Integration & Testing:
  With all the new features implemented, take time to test everything together:
  • Verify that all pages that should pull from the database are doing so. Try changing a value directly in the database (for example, modify a service description or business hour) and refresh the page to confirm the change appears. This proves your dynamic content is working.
  • Test the admin content editing forms (if you added them). Ensure that submitting an edit actually updates the database and the new content shows up on the public site.
  • Submit a new contact form entry on the live site, then log in to the admin area and confirm that the message shows up in the Messages list. Test the display of multiple messages and any additional functions like marking as read or deleting, if you built those.
  • Check your design pattern implementations: for the Singleton, you can print out or var_dump the connection in two different places to ensure it’s the same object (this is more for your own verification). For the Factory, consider writing a tiny test or using it in practice (e.g., call your factory method and ensure you get back a proper object or data set).
  • Exercise the external API feature by loading the page that uses it. You should see the dynamic data appear. Try deliberately changing the API URL to an incorrect one to see how your error handling works, then change it back. If an API requires an API key, make sure you’ve handled that securely and the key is working.

When you’ve completed Unit 8’s project, take a step back and recognize that you have built a robust, data-driven web application. Your small business site now persists important data in a database, has an owner-facing admin component for managing content and viewing user interactions, and even pulls in external information to enrich the user experience. These are hallmark features of modern web development. You’ve also written code in a more structured way by using design patterns, which is a big step toward professional-grade coding. This comprehensive project is great practice for the PHP Coding Specialist exam objectives and is also a portfolio-worthy piece demonstrating database integration and API usage.

---

🧪 Grading Rubric: (Unit 8 – Database-Driven Website with API Integration)

Your Unit 8 submission will be evaluated on the following criteria. Aim to meet the “Proficient” or “Exemplary” level in each area to demonstrate readiness for real-world PHP development and the PHP Coding Specialist exam.

Criteria	Exemplary (Full Points)	Proficient (Good)	Developing (Needs Improvement)	Not Yet (Incomplete)
Database Connection & Singleton Pattern	PDO connection implemented securely via Singleton class; centralized and reused cleanly across project	PDO used with reasonable organization (e.g., included via config); Singleton partially used or inconsistently	PDO connection works but is copied across pages or insecure; no pattern applied	Database not connected or uses outdated/insecure method
Dynamic Content from DB	Site content (services, business info) dynamically loaded from MySQL using PDO SELECT queries; clean display and fallback handling	At least one major page uses DB content instead of hardcoded HTML; minimal fallback or update handling	Some attempt at dynamic content, but page still uses static PHP or logic is broken	No dynamic content retrieved from DB; site is static
Admin Content Management	Admin interface includes form(s) to edit business content or add services; changes update DB and reflect live on site	Content can be edited via DB manually; basic admin forms may exist with limited effect	No admin UI; content must be edited directly in database or files	No content editing possible via admin or DB
Contact Form → DB (CRUD)	Contact form saves messages to DB via INSERT; admin panel displays messages with read/delete or status tracking (UPDATE/DELETE)	Messages stored and displayed in admin area; may lack status tracking or safe deletion	Messages stored in DB but no way to view/edit/delete; or unsafe implementation	Messages not stored or only emailed; no persistence or admin access
External API Integration (via cURL)	External API (e.g., weather, reviews) fetched with cURL, parsed, and displayed on site; robust fallback for downtime/errors	cURL used to get and display external data; minor formatting or error handling issues	API integration attempted but not functioning consistently or clearly displayed	No external API integration or completely broken implementation
Use of Factory or Additional Pattern	Factory pattern used appropriately to structure content loading or object creation (e.g., page types, services, messages)	Some abstraction or attempt at factory/helper function; partially structured logic	Factory not clearly implemented or confused with another pattern	No attempt at design patterns beyond Singleton
Admin Panel Integration	Admin dashboard is clearly accessible, secure, and allows access to content editing, messages, and data management	Admin panel exists and allows basic tasks; minor UI/UX or access inconsistencies	Admin access is unclear or limited to viewing; lacks integration with site	No admin dashboard or it’s not linked/protected
Security & Data Handling	All DB interactions use prepared statements; inputs validated/sanitized; secure API key handling; no hardcoded credentials	Mostly secure code; some input or output filtering; config separation in progress	Security practices are inconsistent; raw user input used in queries or output	Unsafe practices throughout (e.g., SQL injection risks, exposed keys)
Error Handling (DB + API)	Errors are caught and user-friendly messages shown (e.g., try/catch, missing API response fallback)	Some error handling present, though generic or limited to dev/debug	Missing or fragile error handling; errors shown as raw output	Errors break the page or are not handled at all
Code Structure & Comments	Modular, well-structured code; uses includes, functions, and classes where appropriate; commented for clarity	Reasonably organized; includes used; some comments and logical structure	Code is functional but repetitive, messy, or poorly explained	Spaghetti code or mostly copy/pasted snippets without structure or clarity

Up Next: Unit 9 – Web Services (APIs). With your site’s data now centralized in a database, you’re ready to take the next leap. In Unit 9, we’ll flip perspectives: instead of consuming someone else’s API, you’ll learn how to expose your own data via web services. You’ll create RESTful endpoints (and even a SOAP interface) for your application, allowing external clients to retrieve data from your site in a controlled way. The work you’ve done in Unit 8 sets the foundation for this – your database will serve as the source for the data your APIs provide. Great job getting the site this far, and get ready to turn it into a full-fledged service in the next unit!