Understanding Referential Integrity in PHP Music Databases: Key Concepts and Implementation

Keys, Referential Integrity and PHP One to Many on the Web

Music Database Artist Album PK: (artist_id, album_id) FK: artist_id 3 Retro 1 2 Substance 3 2 In a Silent Way PK: artist_id New Order Nick Cave Miles Davis Track PK: track_id FK: artist_id, album_id 0 Do You Love Me 2 1 0 Elegia 1 1

Composite Primary Key Implications • AUTOINCREMENT works great with single column primary key. Not so with composite keys. • Query may require two joins (transition table) • Update, Delete may require two joins (transition table)

Music Database Artist Album PK: album_id FK: artist_id 1 Retro 1 2 Substance 3 3 In a Silent Way PK: artist_id New Order Nick Cave Miles Davis Track PK: track_id FK: artist_id, album_id 0 Do You Love Me 2 1 1Elegia 1 1

Referential Integrity Review • Referential Integrity rule: When one table has a foreign key to another table, the concept of referential integrity states that you may not add a record to the table that contains the foreign key unless there is a corresponding record in the linked table. • It also includes the techniques known as cascading update and cascading delete, which ensure that changes made to the linked table are reflected in the primary table.

Referential Integrity and SQL • Parent table (no foreign key) • Insert: No impact • Delete: Must prevent orphan foreign key • Modify (primary key not changed ): No impact • Modify (primary key change): Update all children (cascade update) • Child table (foreign key) • Insert: foreign key must be valid and not null • Delete: no impact • Update: foreign key must be valid and not null

Review of PHP with One Table • No foreign key (parent table) • Insert, Modify, Delete and Query from Web page.

Referential Integrity and SQL • Parent table (no foreign key) • Insert: No impact • Delete: Must prevent orphan foreign key • Modify (primary key not changed ): No impact • Modify (primary key change): Update all children (cascade update)

PHP with Two Tables: No Foreign Key (parent table) • Insert: No considerations • Delete: Cannot leave orphan foreign key • Prohibit deletions • Delete all children that belong to parent*** • Set all children foreign key to NULL (no parent) • Update: Cannot leave orphan foreign key • Prohibit change to primary key in parent*** • Update all children with updated foreign key

Cascade Delete (Child then Parent) First DELETE children if(isset($_GET['deleteartist'])) { $sql = 'DELETE FROM album WHERE artist_id = :artist_id'; $s=$pdo->prepare($sql); $s->bindValue(':artist_id', $_POST['artist_id']); $s->execute(); $sql='DELETE FROM artist WHERE artist_id = :artist_id'; $s=$pdo->prepare($sql); $s->bindValue(':artist_id', $_POST['artist_id']); $s->execute(); header('Location: .'); exit(); } Then DELETE parent

Referential Integrity and SQL • Child table (foreign key) • Add: foreign key must be valid and not null • Delete: no impact • Update: foreign key must be valid and not null

PHP with Two Tables: Foreign key table (child table) • Insert: Input new data, select valid foreign key • Drop down box • Check boxes • Delete: No considerations-just delete child • Update (no foreign key change): No considerations-just query and update child • Update (foreign key change): select valid foreign key • Drop down box • Check boxes

DELETE From a Child Table (index.php) No Impact if (isset($_POST['action']) and $_POST['action'] == 'Delete') { include $_SERVER['DOCUMENT_ROOT'] . '/connect/db.inc.php'; // Delete the joke $sql = 'DELETE FROM album WHERE album_id = :album_id'; $s = $pdo->prepare($sql); $s->bindValue(':album_id', $_POST['album_id']); $s->execute(); header('Location: .'); exit(); }

INSERT into Child Table • Form to collect new data • Create and populate Drop Down box for foreign key • Controller (index.php) 2 parts: • Part 1: -SQL to collect foreign keys for Drop Down Box on form -load form (without values) • Part 2: -SQL to post INSERT with form data * requires foreign key (AUTOINCREMENT handles primary key)

SQL to collect foreign keys for Drop Down (index.php) $result = $pdo->query('SELECT artist_id, artist_name FROM artist'); foreach($result as $row) { $artists[] = array( 'artist_id' => $row['artist_id'], 'artist_name' => $row['artist_name'] ); }

load form (without values) index.php include 'form.html.php';

Create and populate Drop Down box for foreign key (form.html.php) <label for="album_name">Type the album name here:</label> <textarea id="album_name" name="album_name" rows="3" cols="40"> <?phphtmlout($album_name); ?></textarea> <label for="artist">Artist:</label> <select name="artist" id="artist"> <option value="">Select one</option> <?phpforeach ($artists as $artist): ?> <option value="<?phphtmlout($artist['artist_id']); ?>"><?phphtmlout($artist['artist_name']);?> </option> <?phpendforeach; ?> </select>

SQL to post INSERT with form data index.php $sql = 'INSERT INTO album SET album_name = :album_name, artist_id = :artist_id'; $s = $pdo->prepare($sql); $s->bindValue(':album_name', $_POST['album_name']); //form.html.php’salbum_name $s->bindValue(':artist_id', $_POST['artist']); //form.html.php’s artist $s->execute();

* requires foreign key (AUTOINCREMENT handles primary key) • Form.html.php <select name="artist" id="artist"> • Index.php $s->bindValue(':artist_id', $_POST['artist']); //form.html.php’s artist

Query Child Table include $_SERVER['DOCUMENT_ROOT'] . '/connect/db.inc.php'; // The basic SELECT statement $select = 'SELECT album_id, album_name'; $from = ' FROM album'; $where = ' WHERE TRUE'; $placeholders = array();

Allow Users to Choose Criteria if ($_GET['artist'] != '') // Search by author { $where .= " AND artist_id = :artist_id"; $placeholders[':artist_id'] = $_GET['artist']; } if ($_GET['album_name'] != '') // search text { $where .= " AND album_name LIKE :album_name"; $placeholders[':album_name'] = '%' . $_GET['album_name'] . '%'; } $sql = $select . $from . $where; $s = $pdo->prepare($sql); $s->execute( $placeholders);

Store Query Result for Form foreach ($s as $row) { $albums[] = array('album_id' => $row['album_id'], 'album_name' => $row['album_name']); } include 'albums.html.php';

Search Result Form (albums.html.php) <h1>Search Results</h1> <?php if (isset($albums)): ?> <table> <tr><th>Album Name</th><th>Options</th></tr> <?phpforeach($albums as $album): ?> <tr> <td><?phphtmlout($album['album_name']); ?></td> <td> <form action="?" method="post"> <div> <input type="hidden" name="album_id" value="<?phphtmlout($album['album_id']); ?>"> <input type="submit" name="action" value="Edit"> <input type="submit" name="action" value="Delete"> </div> </form> </td> </tr> <?phpendforeach; ?> </table> <?phpendif; ?>

UPDATE into Child Table • Query: find row to update (done) • Form: collect new data • Create and populate Drop Down box for foreign key • Controller (index.php) 2 parts: • Part 1: -SQL to collect foreign keys for Drop Down box on form -load form (with selected record) • Part 2: -SQL to post UPDATE with form data * requires primary key of child and foreign key

Create and Populate Drop Down <label for="artist">Artist:</label> <select name="artist" id="artist"> <option value="">Select one</option> <?phpforeach ($artists as $artist): ?> <option value="<?phphtmlout($artist['artist_id']); ?>"> <?php if ($artist['artist_id'] == $artist_id) { echo ' selected'; } ?> <?phphtmlout($artist['artist_name']);?> </option> <?phpendforeach; ?> </select>

load form (with selected record) • Collect all data for a child to display on form (index.php) $sql = 'SELECT album_id, album_name, artist_id FROM album WHERE album_id = :album_id'; $s = $pdo->prepare($sql); $s->bindValue(':album_id', $_POST['album_id']); $s->execute(); • Where did album_id come from? albums.php.html (used for search results) <input type="hidden" name="album_id" value="<?phphtmlout($album['album_id']); ?>"> <input type="submit" name="action" value="Edit"> <input type="submit" name="action" value="Delete">

Load form (with selected record) • Store album info from query in variables (index.php) $row = $s->fetch(); $album_name = $row['album_name']; $artist_id = $row['artist_id']; $album_id = $row['album_id']; • Then load into form (form.html.php) <label for="album_name">Type the album name here:</label> <textarea id="album_name" name="album_name" rows="3" cols="40"> <?phphtmlout($album_name); ?></textarea> </div>

SQL to post UPDATE with form data index.php $sql = 'UPDATE album SET album_name = :album_name, artist_id = :artist_idWHERE album_id = :album_id'; $s = $pdo->prepare($sql); $s->bindValue(':album_id', $_POST['album_id']); $s->bindValue(':album_name', $_POST['album_name']); //form.html.php’salbum_name $s->bindValue(':artist_id', $_POST['artist']); //form.html.php’s artist $s->execute();

*requires primary key of child and foreign key $s->bindValue(':album_id',$_POST['album_id']); //primary key $s->bindValue(':artist_id', $_POST['artist']); //foreign key

Understanding Referential Integrity in PHP Music Databases: Key Concepts and Implementation