10: Writing a Basic Dynamic Website (Part 2)

Learning Target

• Configure a database, user and table using SQL
• Add a variety of user inputs to an HTML form
• Store database connection information more securely

Instructions

Part 1: Review the Starting Point

Step 1: Confirm your existing site is working

In the previous assignment, you created a PHP site with shared includes, multiple pages, and a contact form that does not yet save data. This new assignment builds directly on that work. Your contact page from the earlier assignment used a form with name and message fields and noted that database work would be added later.

Step 2: Make sure you can reach your website files

You should already have a working site structure similar to this:

/var/www/yoursite/
    index.php
    includes/
    pages/
    static/

Step 3: Understand the goal of this assignment

You will now do four main things: Create a MariaDB database for contact messages Create a config file that stores your database credentials Write PHP that inserts form data into the database and shows a confirmation message Create a protected messages page that requires a username and password before it displays stored messages

Part 2: Create the Database and User Accounts in MariaDB

Step 1: Open MariaDB

Use the terminal on your Raspberry Pi:

sudo mariadb

Step 2: Create the website database

Basic explanation: A database is a container that stores related tables. SQL command example:

CREATE DATABASE mysite_db;

Step 3: Create a database user for your website

Basic explanation: A database user gives your PHP site a secure way to connect to the database. Your website should not connect as the MariaDB root user. WRITE THIS DOWN!

CREATE USER 'mysite_user'@'localhost' IDENTIFIED BY 'StrongPasswordHere';

Step 4: Grant privileges to the website user

Basic explanation: Privileges decide what a database user is allowed to do. Your site user needs permission to work with the website database. 

GRANT ALL PRIVILEGES ON mysite_db.* TO 'mysite_user'@'localhost';
FLUSH PRIVILEGES;

Step 5: Select the database you want to work with

Basic explanation: The USE command tells MariaDB which database your next commands should affect. 

USE mysite_db;

Step 6: Create a table to store contact form messages

Basic explanation: A table stores rows of information. Each column stores one kind of data. Your messages table should include: An ID number The date and time the message was received The sender name The topic The phone number The address The city The state The zip code The message SQL command example:

CREATE TABLE messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    submitted_at DATETIME NOT NULL,
    name VARCHAR(100) NOT NULL,
    topic VARCHAR(50) NOT NULL,
    phone VARCHAR(25),
    address VARCHAR(150),
    city VARCHAR(100),
    state VARCHAR(50),
    zip VARCHAR(15),
    message TEXT NOT NULL
);

Step 7: Create a table for login users

Basic explanation: A users table stores usernames and passwords for the protected messages page. For this assignment, plain SQL is fine for creating the table. In PHP, you will use password hashing so the stored password is not plain text. SQL command example:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL
);

Step 8: View your tables

Basic explanation: SHOW TABLES lets you verify that the tables were created. SQL command example:

SHOW TABLES;

Step 9: Describe a table

Basic explanation: DESCRIBE shows the columns and data types in a table. SQL command example:

DESCRIBE messages;

Step 10: Exit MariaDB when finished

EXIT;

Part 3: Create a Config File for Database Credentials

Step 1: Create a config folder

Your database credentials should not be repeated in many PHP files. Store them in one config file so they can be managed more securely and updated more easily.
Create a new subdirectory in your root folder:

mkdir config

Step 2: Create the database config file

nano config/database.php

Step 3: Add your database settings

This file should return an array of settings that your other PHP files can load.

<?php
  return [
    "host" => "localhost",
    "database" => "mysite_db",
    "username" => "mysite_user",
    "password" => "StrongPasswordHere"
  ];

Part 4: Update the Contact Form

Step 1: Add additional fields to your contact form

In the previous assignment, the contact form collected only a name and message. Now expand it so the form also includes: Topic Phone Address City State Zip

Step 2: Use appropriate HTML input types

Basic explanation: Choosing the correct input type makes forms easier to use and helps the browser understand the kind of data being entered. Summary of useful input types: type="text" for name, city, state type="tel" for phone textarea for message select for topic type="text" or type="number" for zip code Example form section:

<h1>Contact Us</h1>
<form action="index.php?page=submit_contact" method="POST"> 
  <p>Your Name:<br><input name="name" required="" type="text"></p> 
  <p>Topic:<br>
    <select name="topic" required=""> 
      <option value="">Choose a topic</option> 
      <option value="general">General Question</option> 
      <option value="support">Support</option> 
      <option value="feedback">Feedback</option> 
    </select></p> 
  <p>Phone:<br><input name="phone" type="tel"></p> 
  <p>Address:<br><input name="address" type="text"></p> 
  <p>City:<br><input name="city" type="text"></p> 
  <p>State:<br><input name="state" type="text"></p> 
  <p>Zip:<br><input name="zip" type="text"></p> 
  <p>Message:<br><textarea cols="40" name="message" required="" rows="4"></textarea></p> 
  <p><button type="submit">Send</button></p> 
</form>