1 / 29

Keys, Referential Integrity and PHP

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 ,

jatin
Download Presentation

Keys, Referential Integrity and PHP

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


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

  2. 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

  3. 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)

  4. 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

  5. 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.

  6. 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

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

  8. 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)

  9. 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

  10. 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

  11. 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

  12. 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

  13. 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(); }

  14. 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)

  15. 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'] ); }

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

  17. 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>

  18. 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();

  19. * 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

  20. 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();

  21. 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);

  22. 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';

  23. 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; ?>

  24. 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

  25. 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>

  26. 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">

  27. 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>

  28. 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();

  29. *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

More Related