keys, referential integrity and php
DESCRIPTION
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 , - PowerPoint PPT PresentationTRANSCRIPT
Keys, Referential Integrity and PHP
One to Many on the Web
Music DatabaseArtist Album
Track
PK: artist_id PK: (artist_id, album_id)
FK: artist_id
PK: track_id
FK: artist_id,album_id
1 New Order2 Nick Cave3 Miles Davis
1 3 Retro1 2 Substance3 2 In a Silent Way
0 Do You Love Me 2 10 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 DatabaseArtist Album
Track
PK: artist_id PK: album_id
FK: artist_id
PK: track_id
FK: artist_id,album_id
1 New Order2 Nick Cave3 Miles Davis
1 1 Retro1 2 Substance3 3 In a Silent Way
0 Do You Love Me 2 11 Elegia 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) 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();}
First DELETE children
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"><?php htmlout($album_name); ?></textarea>
<label for="artist">Artist:</label> <select name="artist" id="artist">
<option value="">Select one</option>
<?php foreach ($artists as $artist): ?> <option value="<?php htmlout($artist['artist_id']); ?>"><?php htmlout($artist['artist_name']);?></option>
<?php endforeach; ?> </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’s album_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 Criteriaif ($_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> <?php foreach ($albums as $album): ?> <tr> <td><?php htmlout($album['album_name']); ?></td> <td> <form action="?" method="post"> <div> <input type="hidden" name="album_id" value="<?php htmlout($album['album_id']); ?>"> <input type="submit" name="action" value="Edit"> <input type="submit" name="action" value="Delete"> </div> </form> </td> </tr> <?php endforeach; ?> </table> <?php endif; ?>
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> <?php foreach ($artists as $artist): ?>
<option value="<?php htmlout($artist['artist_id']); ?>"><?php if ($artist['artist_id'] == $artist_id) { echo ' selected'; } ?><?php htmlout($artist['artist_name']);?>
</option> <?php endforeach; ?> </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="<?php htmlout($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"><?php htmlout($album_name); ?></textarea> </div>
SQL to post UPDATE with form data index.php
$sql = 'UPDATE album SET album_name = :album_name, artist_id = :artist_id WHERE 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’s album_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