I’ve received a number of questions about the web application, both during extra instruction (EI) meetings and by Microsoft Teams. I’m consolidating them here in hopes the answers benefit additional students.

These FAQs may be updated over the next few days. Please check to see if your question has been answered before requesting individual assistance.

Why can’t I connect to my database?
The schema design required writing SQL commands to create the relational schema and to populate the database with representative data. Many students completed that assignment using PostgreSQL and pgAdmin, but for the web application, you should use a MySQL database – PostgreSQL and MySQL are two different database management systems (DBMSs) so you’ll need to recreate your database in MySQL.
How do I create a MySQL database?
Expanding on the instructions in the assignment description, you should create a file with the SQL commands used to create the relational schema and to populate the database with representative data. (These SQL commands were part of the schema design deliverable.) Assuming the file is is named database.sql, you can execute the SQL commands using the following command in the terminal: 
mysql -u student -p student < database.sql

You may need to modify the SQL commands that you previously submitted, particularly if you created the database in PostgreSQL. For example, it is often helpful to include DROP TABLE statements so that you can run the prior terminal command multiple times (e.g., if you find a mistake and want to recreate the database from scratch), and you may need to remove PostgreSQL extensions. Such changes should be straightforward – e.g.,

CREATE TABLE IF NOT EXISTS public.Example
(
  id SERIAL,
  name VARCHAR(64) NOT NULL,
  description VARCHAR(1024),
  related_id INTEGER,

  CONSTRAINT "Example_pkey" PRIMARY KEY (id),
  CONSTRAINT "Related_fkey" FOREIGN KEY (related_id)
      REFERENCES public.Related (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
TABLESPACE pg_default;

becomes

DROP TABLE Example;  -- remove table if it already exists
CREATE TABLE IF NOT EXISTS Example  -- `public.` is a PostgreSQL schema
(
  id INTEGER AUTO_INCREMENT,  -- AUTO_INCREMENT creates a counter for the id
  name VARCHAR(64) NOT NULL,
  description VARCHAR(1024),
  relatedId INTEGER,

  PRIMARY KEY (id),
  FOREIGN KEY (related_id) REFERENCES Related (id)
      ON UPDATE RESTRICT ON DELETE RESTRICT  -- NO ACTION => RESTRICT
);

In essence, remove everything that was not discussed when we covered SQL data definition. (Many of the prior changes are not strictly necessary, but PostgreSQL and MySQL support slightly different SQL variants, and listing all the differences is more difficult than simply reverting to what was covered in class – usually removing whatever is unfamiliar.)

What if I want to use PostgreSQL instead of MySQL?
Using a PostgreSQL database is feasible, but you’ll have to install the php-pgsql package: 
 sudo apt install php-pgsql

Note that PHP’s PostgreSQL extension has a different API than the MySQL Improved extension although they share similar concepts (connections, prepared statements, fetching results, etc.). You will be responsible for troubleshooting if you decide to use PostgreSQL and php-pgsql.

How do I connect to a MySQL database in PHP?
Look at the PHP and MySQL practice exercises. You should connect to the “student” MySQL database, which has already been created for you to use.
How do I pass data from an HTML form to PHP?
Remember that the name attribute corresponds to the key in PHP’s superglobal variables (e.g., $_GET or $_POST). Given the following web form, 
<form action='search.php' method='get'>
  <label for='searchInput'>Search</label>
  <input id='searchInput' name='q' type='text' />

  <input type'submit' value='Go' />
</form>

then search.php should look something like follows:

<?php

// connect to database
$connection = new mysqli(...);

 ?>
<!DOCTYPE html>
<html>
  <head>
    ...
  </head>
  <body>
<?php
if (isset($_POST["q"])) {  // search form submitted
  // create an HTML table to display the search results
 ?>
    <h4>Search Results</h4>
    <table>
      <tr>  <!-- header -->
        <th>ID</th>
        <th>Name</th>
        <th>Description</th>
        <th>Related</th>
      </tr>  <!-- end header -->
<?php
  // query database to find entries that match the search string
  $statement = $connection->prepare(
      "SELECT id, name, description, relatedId ".
      "FROM Example ".
      "WHERE name LIKE ?;"
  );  // use a prepared statement to prevent SQL injection attacks
  $statement->bind_param("s", $_POST["q"]);  // bind user input to `?`
  $statement->execute();  // execute query

  // bind values of attributes in the database to PHP variables
  $statement->bind_result($id, $name, $description, $relatedId);
  // iterate over results
  while ($statement->fetch()) {
    // create a table row to display each result
 ?>
      <tr>
        <td><?php echo htmlspecialchars($id); ?></td>
        <td><?php echo htmlspecialchars($name); ?></td>
        <td><?php echo htmlspecialchars($description); ?></td>
        <td><?php echo htmlspecialchars($relatedId); ?></td>
      </tr>
<?php
  }  // end while
 ?>
    </table>
<?php
} else {
  // TODO: Handle request without a search query
}
 ?>
  </body>
</html>
<?php

// close the database connection
$connection->close();

Note that ellipses indicate omitted content (to keep the length of the example reasonable) and that you’ll need to adapt this example to the context of your project.