20 Persistent data storage relational databases and My
20 – Persistent data storage: relational databases and My. SQL Mark Dixon Page 1
Questions: Session variables • Write a line of code to put 59 into a session variable called score. $_SESSION["score"] = 59 • Write code that adds 1 to a variable called g, when a session variable called i is over 25. if($_SESSION["i"] > 25){ $g = $g + 1 } Mark Dixon Page 2
Admin: SQL Book Gennick J (2006) SQL Pocket Guide (2 nd edition). O'Reilly. ISBN: 0 -596 -52688 -1 Mark Dixon Page 3
Session Aims & Objectives • Aims – To introduce the fundamental ideas involved in using relational databases for persistent data storage • Objectives, by end of this week’s sessions, you should be able to: – create a relational database table – create a web page that displays data from a single table in a database – use SQL in your programs to create more complex record-sets, using records and fields Mark Dixon Page 4
Persistent Data Storage • So far – programs (web-pages) lose data when closed • Not realistic – typically data stored to persistent storage device (e. g. hard disk, key drive, floppy disk, CD-RW) • Use either – flat files – database (relational, or object oriented) Mark Dixon Page 5
Example: People (analysis) SPECIFICATION • User Requirements – need to have access to people's details • Software Requirements – Functional: – Display list of people from a database – Non-functional should be viewable anywhere in the world Mark Dixon Page 6
Example: People (Database) • Information organised into – tables (e. g. person) – fields (e. g. phone) – records (e. g. 1 Dixon Mark 01752 232556 …) Person ID 1 2 3 Mark Dixon Surname Dixon Smith Jones Field Record Forenames Mark John Sally Phone 01752 232556 01752 111111 01752 888888 Gender Male Female Page 7
Questions: Music (Database) • How many fields? • How many records? 3 9 Track Mark Dixon Track Title Artist Name Country Paranoid Black Sabbath UK Falling in Love Aerosmith US Pink Aerosmith US Love in an Elevator Aerosmith US Smooth Criminal Alien Ant Farm US Meaning of Life Disturbed US The Game Disturbed US Voices Disturbed US Down with the Sickness Disturbed US Page 8
DBMS • Database Management Systems (DBMS) provide facilities for: – creating and changing databases • add/remove records • add/remove fields • add/remove data – For example: • • • Mark Dixon Microsoft Access d. Base Borland Paradox My. SQL Microsoft SQL Server Oracle home/small business large scale Page 9
Php. My. Admin • used to create tables, fields, etc. add port number : 8080 Mark Dixon Page 10
Using Queries <? php $c = mysql_connect('localhost', 'root', ''); Connect to My. SQL mysql_select_db('People'); Select Database $q = mysql_query('SELECT * FROM Person; '); mysql_close($c); Get Data from Table $s = ''; while ($r = mysql_fetch_array($q)){ $s = $s. $r['Surname']. ' '; } mysql_free_result($q); ? > Read Record / Row Read Field <html> <head><title></head> <body> <? php echo $s; ? > </body> </html> Mark Dixon Page 11
Example: People (Open) People. ph p $q $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']. ' '; } mysql_free_result($q); echo $s; Mark Dixon Page 12
Example: People. ph p $q $c = mysql_connect('localhost', 'root', ''); mysql_select_db('People'); $q = mysql_query('SELECT * FROM Person; '); mysql_close($c); $s $s = ''; while ($r = mysql_fetch_array($q)){ $s = $s. $r['Surname']. ' '; } mysql_free_result($q); echo $s; Mark Dixon Page 13
Example: People (EOF) People. ph p $q $c = mysql_connect('localhost', 'root', ''); mysql_select_db('People'); $q = mysql_query('SELECT * FROM Person; '); mysql_close($c); $s $s = ''; while ($r = mysql_fetch_array($q)){ $s = $s. $r['Surname']. ' '; } mysql_free_result($q); echo $s; Mark Dixon Page 14
Example: People. ph p $q $c = mysql_connect('localhost', 'root', ''); mysql_select_db('People'); $q = mysql_query('SELECT * FROM Person; '); mysql_close($c); $s $s = ''; while ($r = mysql_fetch_array($q)){ $s = $s. $r['Surname']. ' '; } mysql_free_result($q); Dixon echo $s; Mark Dixon Page 15
Example: People (Loop) People. ph p $q $c = mysql_connect('localhost', 'root', ''); mysql_select_db('People'); $q = mysql_query('SELECT * FROM Person; '); mysql_close($c); $s $s = ''; while ($r = mysql_fetch_array($q)){ $s = $s. $r['Surname']. ' '; } mysql_free_result($q); Dixon echo $s; Mark Dixon Page 16
Example: People (EOF) People. ph p $q $c = mysql_connect('localhost', 'root', ''); mysql_select_db('People'); $q = mysql_query('SELECT * FROM Person; '); mysql_close($c); $s $s = ''; while ($r = mysql_fetch_array($q)){ $s = $s. $r['Surname']. ' '; } mysql_free_result($q); Dixon echo $s; Mark Dixon Page 17
Example: People. ph p $q $c = mysql_connect('localhost', 'root', ''); mysql_select_db('People'); $q = mysql_query('SELECT * FROM Person; '); mysql_close($c); $s $s = ''; while ($r = mysql_fetch_array($q)){ $s = $s. $r['Surname']. ' '; } mysql_free_result($q); Dixon />Smith echo $s; Mark Dixon Page 18
Example: People (Loop) People. ph p $q $c = mysql_connect('localhost', 'root', ''); mysql_select_db('People'); $q = mysql_query('SELECT * FROM Person; '); mysql_close($c); $s $s = ''; while ($r = mysql_fetch_array($q)){ $s = $s. $r['Surname']. ' '; } mysql_free_result($q); Dixon Smith echo $s; Mark Dixon Page 19
Example: People (EOF) People. ph p $q $c = mysql_connect('localhost', 'root', ''); mysql_select_db('People'); $q = mysql_query('SELECT * FROM Person; '); mysql_close($c); $s $s = ''; while ($r = mysql_fetch_array($q)){ $s = $s. $r['Surname']. ' '; } mysql_free_result($q); Dixon Smith echo $s; Mark Dixon Page 20
Example: People. ph p $q $c = mysql_connect('localhost', 'root', ''); mysql_select_db('People'); $q = mysql_query('SELECT * FROM Person; '); mysql_close($c); $s $s = ''; while ($r = mysql_fetch_array($q)){ $s = $s. $r['Surname']. ' '; } mysql_free_result($q); Dixon Smith />Jones echo $s; Mark Dixon Page 21
Example: People (Loop) People. ph p $q $c = mysql_connect('localhost', 'root', ''); mysql_select_db('People'); $q = mysql_query('SELECT * FROM Person; '); mysql_close($c); $s $s = ''; while ($r = mysql_fetch_array($q)){ $s = $s. $r['Surname']. ' '; } mysql_free_result($q); Dixon Smith Jones echo $s; Mark Dixon Page 22
Example: People (EOF) People. ph p rs $c = mysql_connect('localhost', 'root', ''); mysql_select_db('People'); $q = mysql_query('SELECT * FROM Person; '); mysql_close($c); $s $s = ''; while ($r = mysql_fetch_array($q)){ $s = $s. $r['Surname']. ' '; } mysql_free_result($q); Dixon Smith Jones echo $s; Mark Dixon Page 23
Example: People. ph p $q $c = mysql_connect('localhost', 'root', ''); mysql_select_db('People'); $q = mysql_query('SELECT * FROM Person; '); mysql_close($c); $s $s = ''; while ($r = mysql_fetch_array($q)){ $s = $s. $r['Surname']. ' '; } mysql_free_result($q); Dixon Smith Jones echo $s; Mark Dixon Page 24
Example: People. ph p $q $c = mysql_connect('localhost', 'root', ''); mysql_select_db('People'); $q = mysql_query('SELECT * FROM Person; '); mysql_close($c); $s $s = ''; while ($r = mysql_fetch_array($q)){ $s = $s. $r['Surname']. ' '; } mysql_free_result($q); Dixon Smith Jones echo $s; Mark Dixon Page 25
Example: Countries. ph p $c = mysql_connect('localhost', 'root', ''); mysql_select_db('Countries'); $q = mysql_query('SELECT * FROM Country; '); html by hand put br tags between data mysql_close($c); $s = ''; while ($r = mysql_fetch_array($q)){ $s = $s. $r['Name']; } mysql_free_result($q); Need data on separate lines Mark Dixon Page 26
Example: Countries (error) Countries. ph p $c = mysql_connect('localhost', 'root', ''); mysql_select_db('Countries'); $q = mysql_query('SELECT * FROM Country; '); Try putting br tag here mysql_close($c); $s = ; while ($r = mysql_fetch_array($q)){ $s = $s. $r['Name']; } mysql_free_result($q); php does not understand html Mark Dixon Page 27
Example: Countries. ph p $c = mysql_connect('localhost', 'root', ''); mysql_select_db('Countries'); $q = mysql_query('SELECT * FROM Country; '); mysql_close($c); $s = " "; while ($r = mysql_fetch_array($q)){ $s = $s. $r['Name']; } mysql_free_result($q); • Need double quotes around tag • (php sees html as literal string) runs, but br in wrong place Mark Dixon Page 28
Example: Countries. ph p $c = mysql_connect('localhost', 'root', ''); mysql_select_db('Countries'); Move br tag inside loop. $q = mysql_query('SELECT * FROM Country; '); Which bit of code pulls data from database? mysql_close($c); $s = " "; while ($r = mysql_fetch_array($q)){ $s = $s. $r['Name']; } mysql_free_result($q); Mark Dixon Page 29
Example: Countries. as px $c = mysql_connect('localhost', 'root', ''); mysql_select_db('Countries'); Move br tag inside loop. $q = mysql_query('SELECT * FROM Country; '); after field data mysql_close($c); $s = ""; while ($r = mysql_fetch_array($q)){ $s = $s. $r['Name']. " "; } mysql_free_result($q); View Source Mark Dixon Page 30
Embedding html in php • html must be string (inside double quotes) • follows normal pattern for expressions: data $s = $s. "<b>". $r['Name']. "</b>"; operator Mark Dixon operator Page 31
![Embedding html in php (errors) $s = $s. "<i>" $r['Gender']. "</i>"; missing operator $s](http://slidetodoc.com/presentation_image_h/74621bb1ce47929bf9adc2e1b7b66537/image-32.jpg "Embedding html in php (errors) $s = $s. \"<i>\" $r['Gender']. \"</i>\"; missing operator $s")
Embedding html in php (errors) $s = $s. "<i>" $r['Gender']. "</i>"; missing operator $s = $s. $r['Height']. </i>"; missing double quote $s = $s. <ul>. $r['Height']; html tag must be inside double quotes $s = $s. "<ul>". $r['<b>Height']; looks for field in database called <b>Height Mark Dixon Page 32
Questions: HTML in php • Are these correct (assume variables and fields exist)? $g = $g. $r['Surname ']; $h = $h. "<ol>" $r['Width']; $a = "<p>". $a. "</p>"; $html = $html. "<img src=face. gif />"; $h = <table>. $h. "</table>"; Mark Dixon Page 33
Example: People v 2 • Display Surname of Male people: <? 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)){ if($r['Gender'] == 1){ $s = $s. $r['Surname']. ' '; } } mysql_free_result($q); ? > Mark Dixon Page 34
Example: People v 3 • Display Surname of Male people: <? php $c = mysql_connect('localhost', 'root', ''); mysql_select_db('People'); $q = mysql_query('SELECT * FROM Person WHERE Ge mysql_close($c); $s = ''; while ($r = mysql_fetch_array($q)){ $s = $s. $r['Surname']. ' '; } mysql_free_result($q); ? > SQL statement Mark Dixon Page 35
SQL: Queries • main purpose of databases: – get information back out: searching • Structured Query Language – dedicated to interacting with databases • 3 rd Generation Language (such as VB, C++) – code describes how to do task • 4 th Generation Language (such as SQL) – code describes what to do (not how to do it) Mark Dixon Page 36
SQL: SELECT statement • SELECT statement – used to get data – can be embedded in php, via mysql_query: $q = mysql_query('SELECT * FROM Person'); all fields Mark Dixon Page 37
SQL: WHERE & ORDER BY • WHERE clause – used to restrict data SELECT * FROM People WHERE age>=18; • ORDER BY clause – used to change order of data SELECT * FROM People ORDER BY Surname; Mark Dixon Page 38
SQL: strings (text data) • Possible confusion: SELECT * FROM Person WHERE Surname = Smith this will look for field called Smith - gives error • need single (SQL) quotes to signify literal text SELECT * FROM Person WHERE Surname = 'Smith' Mark Dixon Page 39
Questions: SQL • Create an SQL statement to extract Track Title of records by Aerosmith Track. Title Artist. Name Country Paranoid Black Sabbath UK Falling in Love Aerosmith US Pink Aerosmith US Love in an Elevator Aerosmith US Smooth Criminal Alien Ant Farm US Meaning of Life Disturbed US The Game Disturbed US Voices Disturbed US Down with the Sickness Disturbed US SELECT Track. Title FROM Track WHERE Artist. Name = 'Aerosmith'; Mark Dixon Page 40
Questions: SQL • Create an SQL statement to extract all fields of songs by Disturbed, ordered by track name Track. Title Artist. Name Country Paranoid Black Sabbath UK Falling in Love Aerosmith US Pink Aerosmith US Love in an Elevator Aerosmith US Smooth Criminal Alien Ant Farm US Meaning of Life Disturbed US The Game Disturbed US Voices Disturbed US Down with the Sickness Disturbed US SELECT * FROM Track WHERE Artist. Name = 'Disturbed' ORDER BY Track. Title; Mark Dixon Page 41
Example: People v 4 • User controls what is displayed: Mark Dixon Page 42
SQL: DISTINCT records Artist. Name SELECT Artist. Name FROM Track; Black Sabbath Aerosmith Alien Ant Farm Disturbed SELECT DISTINCT Artist. Name FROM Track; Artist. Name Black Sabbath Aerosmith Alien Ant Farm Disturbed Mark Dixon Page 43
Tutorial Exercise: People • • • Task 1: Create your own People database: – Start WAMP Server – Open Php. My. Admin – Create a new database – Create a new table – Create fields – Enter data Task 2: Get the People v 1 example (from the lecture) working. Task 3: Modify your page so that it displays phone number as well as the person's name. Task 4: Modify your page so that it displays the data in an html table. Task 5: Modify your page so that records for males are displayed in blue, and records for females are displayed in red. Task 6: Modify your page as per version 4. You will need to: – Add a form to the page, and three submit buttons – In your code, detect when a button has been pressed (have a look at previous weeks) Task 7: Modify your page so that the user can type a letter, and only names starting with that letter are displayed. Task 8: Modify your page so that the user can type a series of numerical digits and only phone numbers containing those digits are displayed. Task 9: Modify your code so that the user can order the data by surname, or email address (You may want to use a Query String) Mark Dixon Page 44
- Slides: 44
