Homework: From database rows to an HTML page

 

The goal

Starting record Completed record
Daniel DeFrance Danny DeFrance
Hunter Lloyd Deleted
Pia Louise Unchanged
— Your name and favorites

Change the data in MariaDB. Your PHP page is already provided.

Before:

https://csci331vm.cs.montana.edu/~h48c334/hw06/demo.php

After:

https://csci331vm.cs.montana.edu/~h48c334/hw06/done.php

 

 

C.R.U.D. = Create; Read; Update; Delete

Create

We'll start with a table in that's already been created. The database has also already been created. Later on, you'll create a record in the table. 

 

Read

At the school-server shell, from your email:

mysql -u userNN -p dbNN

At the MariaDB prompt:

SELECT DATABASE();
SET autocommit =1;
DESCRIBE fans;
SELECT*FROM fans ORDERBY fanId;
 

Update one record

UPDATE fans SET firstName ='Danny'WHERE fanId =1;
SELECT*FROM fans WHERE fanId =1;

Refresh the demo page: Daniel is now Danny.

What would happen without the WHERE clause?

 

Delete one record

SELECT*FROM fans WHERE fanId =2;
DELETEFROM fans WHERE fanId =2;
SELECT*FROM fans ORDERBY fanId;

Refresh: Danny and Pia remain, with IDs 1 and 3.

Should Pia's ID change to 2?

 

Insert a new fan (record)

INSERTINTO fans
    (firstName, lastName, faveArtist,
     faveAlbum, albumYear, isTouring)
VALUES # Make this your OWN INFO...
    ('Jordan', 'Rivera', 'Daft Punk',
     'Discovery', 2001, FALSE);

Supply all six data fields.  (Jordan Rivera is an example) MariaDB assigns fanId.

 

Check the result

SELECT*FROM fans ORDERBY fanId;
fanId First name Last name
1 Danny DeFrance
3 Pia Louise
4 Jordan Rivera

ID 4 assumes a freshly seeded table. Refresh to see all seven columns.

 

What happens when we refresh?

  1. Browser requests the PHP page.
  2. Server runs PHP, which queries MariaDB.
  3. PHP builds HTML from the returned rows.
  4. Browser receives and displays the HTML.

Did we edit the PHP file to change the names?

 

PHP builds one HTML row per record

<?phpforeach ($fansas$fan): ?>
    <tr>
        <td><?=escapeHtml($fan['fanId']) ?></td>
        <td><?=escapeHtml($fan['firstName']) ?></td>
        <!-- Other columns follow the same pattern. -->
    </tr>
<?phpendforeach; ?>

Excerpt from the provided page. PHP runs on the server.

 

Same information, different representations

Representation Role in this example
Database row Stores the fan's fields
PHP associative array Holds a fetched record on the server
HTML table row Structures the browser's display

JSON is another way to represent data for programs. HTML links connect resources: the basis of hypertext and hypermedia.

 

HW06: Check and submit

  • 3 points: Daniel → Danny; other fields unchanged.
  • 3 points: Hunter deleted; Pia unchanged.
  • 4 points: Your record added; three records total.

Refresh your provided PHP page. Paste that page's URL into the Canvas submission text field.

Reference: W3Schools SQL

 

Trouble?

You changed a name in MariaDB, but the page looks unchanged. What should you check?

Answer: Refresh the page! Hard refresh is usually Shft + Ctrl/Cmd + R (or hold Shift and click refresh)