PhP DB Writing - Mark Dixon`s web site
Download
Report
Transcript PhP DB Writing - Mark Dixon`s web site
22 – Web applications:
Writing data to Databases
using PhP
Mark Dixon
Page 1
Questions: HTML in PhP
• Are these correct (assume variables and
fields exist)?
$f = $f . $r["Description"]
$h = $h . $r["<br />Name"]
$a = "<p>" . $a "</p>"
$html = $html . <img src=face.gif />
$h = "<table>" . $h . "</table>"
Mark Dixon
Page 2
Questions: Databases
• How many primary keys?
• How many foreign keys?
Plant
PlantID
1
2
3
4
5
6
7
8
EnglishName
Foxglove
Daisy
Hemlock
Marsh Mallow
Lords-and-Ladies
Wild Carrot
Bluebell
Common Poppy
3
2
ScientificName
Price Toxic
FileName
Digitalis purpurea
2.5 TRUE Foxglove.jpg
Bellis perennis
0.45 FALSE Daisy.jpg
Conium maculatum
8.79 TRUE Hemlock.jpg
Althaea officinalis
3.25 FALSE MarshMallow.jpg
Arum maculatum
2.25 TRUE Lords.jpg
Daucus carota
1.25 FALSE WildCarrot.jpg
Hyacinthoides non-scripta
1.8 FALSE Bluebell.jpg
Papaver rhoeas
1.28 FALSE Poppy.jpg
Order
OrderID CustID PlantID Quantity
Date
Current
1
1
7
10 14-Mar-06 TRUE
2
2
5
2 14-Mar-06 TRUE
3
1
3
1 14-Mar-06 FALSE
5
2
4
4 14-Mar-06 FALSE
46
1
2
9 09-Jun-06 FALSE
Customer
CustID Surname Forenames
email
Password
1 Dixon
Mark
[email protected] a
2 Jones
Sally
[email protected]
sally
Mark Dixon
Page 3
Session Aims & Objectives
• Aims
– To introduce the fundamental ideas involved in
using server-side code to write data to
databases
• Objectives,
by end of this week’s sessions, you should be able to:
– create an php web page that allows the user to
store data in database
Mark Dixon
Page 4
Example: Person v1 (Specification)
• User requirement:
– Display people's details from database online
– need 2 pages:
smith
jones
dixon
list of people
Mark Dixon
jones
sally
person's details
Page 5
Example: PeopleList.php v1
<?php
$c = mysql_connect('localhost', 'root', '');
mysql_select_db('People');
$q = mysql_query('SELECT * FROM Person;');
mysql_close($c);
$s = '';
while ($r = mysql_fetch_array($q)){
$s = $s . $r['Surname'] . '<br />';
}
mysql_free_result($q);
?>
<html>
<head><title></title></head>
<body>
<?php
Echo $s;
?>
</body>
</html>
Mark Dixon
Page 6
Example: PeopleList.php v2
<?php
$c = mysql_connect('localhost', 'root', '');
mysql_select_db('People');
$q = mysql_query('SELECT * FROM Person;');
mysql_close($c);
$s = '';
while ($r = mysql_fetch_array($q)){
$s = $s . '<a href="Person.php?id=' . $r["ID"] . '">';
$s = $s . $r['Surname'] . '</a><br />';
}
mysql_free_result($q);
?>
now links
<html>
<head><title></title></head>
<body>
<?php
Echo $s;
?>
</body>
</html>
Mark Dixon
Page 7
Example: Person.php v2
<?php
$c = mysql_connect('localhost', 'root', '');
mysql_select_db('People');
$sql = "SELECT * FROM Person WHERE id=" . $_GET["id"];
$q = mysql_query($sql);
mysql_close($c);
$s = '';
if ($r = mysql_fetch_array($q)){
$s = $r['Surname'];
}
mysql_free_result($q);
?>
reads querystring
(from previous page)
displays data for
selected record only
<html>
<head><title></title></head>
<body>
<a href="PeopleList2.php">Back to People List</a><br />
<form method="post">
Surname: <input name="txtSurname" value="<?php Echo $s; ?>" /><br />
<input name="btnSave" type="submit" value="Save" />
</form>
</body>
</html>
Mark Dixon
Page 8
Example: Person v2 (Specification)
• User requirement:
Display person’s details from database online
– Change surname and save to database
Mark Dixon
Page 9
Changing Data
• use SQL
– INSERT: inserts a new record and makes it
current
– UPDATE: sends changes back to DB
UPDATE Person
SET Surname = 'Jones'
WHERE ID = 1
– DELETE: deletes currently selected record
Mark Dixon
Page 10
Example: Person.php v3
<?php
$c = mysql_connect('localhost', 'root', '');
mysql_select_db('People');
$id = $_GET["id"];
if(isset($_POST["btnSave"])){
$sn = $_POST["txtSurname"];
$sql = "UPDATE Person SET Surname = '" . $sn . "'";
$sql = $sql . " WHERE ID =" . $id;
mysql_query($sql);
}
Save button works now
$sql = "SELECT * FROM Person WHERE id=" . $id;
$q = mysql_query($sql);
mysql_close($c);
$s = '';
if ($r = mysql_fetch_array($q)){
$s = $r['Surname'];
}
mysql_free_result($q);
?>
<html>
<head><title></title></head>
<body>
<a href="PeopleList3.php">Back to People List</a><br />
<form method="post">
Surname: <input name="txtSurname" value="<?php Echo $s; ?>" /><br />
<input name="btnSave" type="submit" value="Save" />
</form>
</body>
</html>
Mark Dixon
Page 11
Tutorial Exercise: Person
• Task 1: Get the Person (v1) example from the lecture
working.
• Task 2: Modify your code, so that forename is displayed as
well as surname (use a table).
• Task 3: Get the Person (v2 and v3) example from the
lecture working.
• Task 3: Modify your code, so that a line of text is displayed
confirming that data has been saved.
• Task 4: Modify your code, so that an add button is
included, which allows a new record to be added.
• Task 5: Modify your code, so that a delete button is
included, which allows the current record to be deleted.
Mark Dixon
Page 12