Keys, Referential Integrity and PHP - PowerPoint Presentation

Slide1

Keys, Referential Integrity and PHP

One to Many on the WebSlide2

Music Database

Artist

Album

Track

PK:

artist_id

PK: (

artist_id

,

album_id

)FK: artist_id

PK: track_idFK: artist_id,album_id

New OrderNick CaveMiles Davis

3 Retro1 2 Substance3 2 In a Silent Way

0 Do You Love Me 2 10 Elegia 1 1Slide3

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)Slide4

Music Database

Artist

Album

Track

PK:

artist_id

PK:

album_id

FK:

artist_id

PK: track_id FK: artist_id,album_id

New OrderNick CaveMiles Davis

1 Retro1 2 Substance3 3 In a Silent Way

0 Do You Love Me 2 11 Elegia

1 1Slide5

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

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 impactUpdate: foreign key must be valid and not nullSlide7

Review of PHP with One Table

No foreign key (parent table)

Insert, Modify, Delete and Query from Web page.Slide8

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)Slide9

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 keyProhibit change to primary key in parent***

Update all children with updated foreign keySlide10

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

parentSlide11

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 nullSlide12

PHP with Two Tables: Foreign key table (child table)

Insert: Input new data, select

valid

foreign key

Drop down boxCheck boxesDelete: No considerations-just delete child

Update (no foreign key change): No considerations-just query and update childUpdate (foreign key change): select valid foreign key Drop down boxCheck

boxesSlide13

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

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)Slide15

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']

);

}Slide16

load form (without values

)

index.php

include '

form.html.php';Slide17

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>Slide18

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();Slide19

* 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 artistSlide20

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();Slide21

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);Slide22

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';Slide23

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

; ?>Slide24

UPDATE into Child Table

Query: find row to update (done)

Form:

collect new data

Create and populate Drop Down box for foreign keyController (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 Slide25

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>Slide26

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">Slide27

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>Slide28

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();Slide29

*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