Introduction to Relational Databases in mySQL

1. validated.php <?php $title="my basic page\n"; include("top.html"); print "<div>hello, world</div>"; include("bottom.html"); ?>

2. Thomas Krichel 2005-04-02 LIS651 lecture 2

3. today • introduction to mySQL • more on PHP • functions • includes

4. Databases • Databases are collection of data with some organization to them. • The classic example is the relational database. • But not all database need to be relational databases.

5. Relational databases • A relational database is a set of tables. There may be relations between the tables. • Each table has a number of record. Each record has a number of fields. • When the database is being set up, we fix • the size of each field • relationships between tables

6. Example: Movie database ID | title | director | date M1 | Gone with the wind | F. Ford Coppola | 1963 M2 | Room with a view | Coppola, F Ford | 1985 M3 | High Noon | Woody Allan | 1974 M4 | Star Wars | Steve Spielberg | 1993 M5 | Alien | Allen, Woody | 1987 M6 | Blowing in the Wind | Spielberg, Steven | 1962 • Single table • No relations between tables, of course

7. Problem with this database • All data wrong, but this is just for illustration. • Name covered inconsistently. There is no way to find films by Woody Allan without having to go through all spelling variations. • Mistakes are difficult to correct. We have to wade through all records, a masochist’s pleasure.

8. Better movie database ID | title | director | year M1 | Gone with the wind | D1 | 1963 M2 | Room with a view | D1 | 1985 M3 | High Noon | D2 | 1974 M4 | Star Wars | D3 | 1993 M5 | Alien | D2 | 1987 M6 | Blowing in the Wind | D3 | 1962 ID | director name | birth year D1 | Ford Coppola, Francis | 1942 D2 | Allan, Woody | 1957 D3 | Spielberg, Steven | 1942

9. Relational database • We have a one to many relationship between directors and film • Each film has one director • Each director has produced many films • Here it becomes possible for the computer • To know which films have been directed by Woody Allen • To find which films have been directed by a director born in 1942

10. Many-to-many relationships • Each film has one director, but many actors star in it. Relationship between actors and films is a many to many relationship. • Here are a few actors ID | sex | actor name | birth year A1 | f | Brigitte Bardot | 1972 A2 | m | George Clooney | 1927 A3 | f | Marilyn Monroe | 1934

11. Actor/Movie table actor id | movie id A1 | M4 A2 | M3 A3 | M2 A1 | M5 A1 | M3 A2 | M6 A3 | M4 … as many lines as required

12. databases in libraries • Relational databases dominate the world of structured data • But not so popular in libraries • Slow on very large databases (such as catalogs) • Library data has nasty ad-hoc relationships, e.g. • Translation of the first edition of a book • CD supplement that comes with the print version Difficult to deal with in a system where all relations and field have to be set up at the start, can not be changed easily later.

13. databases in web sites • Lots of active web sites are driven by relational databases. All large active web sites are. • The design of a active web site first involves looking at databases. • In a shop situation, we have the following tables • customers • products • orders • orders_products for multiple to multiple relationship between orders and products.

14. SQL • Once we have the relational database, we can ask sophisticated questions: • Which director has had the most female actors working for him? • In which years films have been shot that starred actors born between 1926 and 1935? • Such questions can be encoded in a language know as “structured query language” or SQL. All relational database vendors implement a dialect of SQL.

15. mySQL • They are a very successful, open-source vendor of SQL software. • Their product is basically freely available. • We will learn the mySQL dialect of SQL.

16. using mySQL • mySQL is installed on wotan. • Normally this involves logging into wotan and issuing commands to a character interface. • The command would be • mysql -u user -p

17. create database • This is a mySQL command to create a new database. • Example • create database newbase; • creates a database newbase

18. GRANT • This is a command to create users and give them privileges. A simplified general syntax is GRANT privileges [columns] ON item TO user_name [IDENTIFIED BY 'password''] [WITH GRANT OPTION] • If you use WITH GRANT OPTION, you allow the user to grant other users the privileges that you have given to her.

19. user privileges I • SELECT allows users to select (read) records from tables. • INSERT allows users to insert new rows into tables. • UPDATE allows users to change values in existing table rows. • DELETE allows users to delete table rows. • INDEX allows user to index tables

20. user privileges II • ALTER allows users to change the structure of the database. • adding columns • renaming columns or tables • changing the data types of tables • DROP allows users databases or tables.

21. user privileges III • CREATE allows users to create new databases or tables. If a specific table or database is mentioned in the GRANT statement, users can only create that database or table, which will mean that they have to drop it first. • USAGE allows users nothing. This is a useful point to start with if you just want to create a user.

22. REVOKE • This is the opposite of GRANT.

23. current setup • As the super user, I did create database user_name; GRANT * ON user_name TO user_name IDENTIFIED BY 'secret_word'' WITH GRANT OPTION; • Here • user_name is your wotan user name • secret_word is your secret word • * means all rights

24. create a web user • You do not want to give the same access rights to people coming in from the web as you have. • You do not want to do this. You personally have too many privileges. • I have yet to find out how you can create a web user by yourself.

25. creating tables • before you do it, set up some examples on a sheet of paper. • Here is an example CREATE TABLE customers (custumer_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name CHAR(50) NOT NULL, ADDRESS CHAR(100) not NULL, email CHAR(40), STATE char(2) not NULL);

26. column data types • TINYINT can hold a number between -128 and 127 or between 0 to 255. BIT or BOOL are synonyms for the TINYINT. • SMALLINT can hold a number between -32768 and +32767 or 0 and 65535 • INT can hold a number between -2**31 and 2**31-1 or between 0 and 2**32-1. INTEGER is a synonym for INT. • BIGINT can hold a number between -2**63 and 2**61-1 or between 0 and 2**64-1.

27. column data types: float • FLOAT is a floating number on 4 bytes • DOUBLE is a floating number on 8 bytes

28. column data types: dates • DATE is a day from 1000-01-01 to 9999-12-31. • TIME is a time from -838:59:59 to 838:59:59 • DATETIME is a data and time, usually displayed as YYYY-MM-DD HH:MM:SS • TIMESTAMP is the number of seconds since 1970-01-01 at 0 hours. This number may run out in 2037.

29. field options • PRIMARY KEY says that this column is a the primary key. There can be only one such column. Values in the column must be unique. • AUTO_INCREMENT can be used on columns that contain integer values.

30. functions • The PHP function reference is available on its web site http://www.php.net/quickref.php. It shows the impressive array of functions within PHP. • But one of the strengths of PHP is that you can create your own functions as you please. • If you recreate one of the built-in functions, your own function will have no effect.

31. example • Stephanie Rubino was an English teacher and objects to sentences like You have ordered 1 bottles of Grosswald Pils. • Let us define a function rubino_print(). It will take three arguments • a number to check for plural or singular • a word for the singular • a word for the plural

32. function and parameters • use the keyword "function" and declare your parameters, as in: function rubino_print ($number, $singular,$plural) { if($number == 1) { print "one $singular"; } else { print "$number $plural"; } }

33. default arguments • Sometimes you want to allow a function to be called without giving all its arguments. You can do this by declaring a default value. For the previous example function thomas_need($thing='beer') { print "I need $thing\n"; } thomas_need(); // prints: I need beer; thomas_need('sleep'); // prints: I need sleep;

34. rubino_print using common plurals function rubino_print ($num, $sing,$plur=1) { if($num == 1) { print "one $sing"; } elseif($plur ==1) { print "$num $sing"."s"; } else { print "$num $plur"; } }

35. return value • Up until now we have just looked at the effect of a function. • return is a special command that return a value. • When return is used, the function is left.

36. rubino_print with return function rubino_print ($number, $singular,$plural) { if($number == 1) { return "one $singular"; } return "$number $plural"; } $order=rubino_print(2,"beer","beers"); print "you ordered $order\n"; // prints: you ordered 2 beers.

37. visibility of variables • variables used inside a function are not visible from the outside. Example $beer="Karlsberg"; function yankeefy ($name='Sam Adams') { $beer=$name; } yankeefy(); print $beer; // prints: Karlsberg • the variable inside the function is something different than the variables outside.

38. accessing global variables. • There are two ways to change a global variable, i.e. one that is defined in the main script. • One is just to call it as $GLOBAL['name'] where name is the name of the global variable. function yankeefy ($name="Sam Adams") { $GLOBAL['beer']="name"; }

39. brewer_quiz.php: introduction <?php $brewers=array('Großwald Brauerei','Homburger Brauhaus', 'Karlsberg Brauerei','Ponter Hausbrauerei', 'Saarfürst Merziger Brauhaus','Mettlacher Abtei-Bräu','Körpricher Landbräu','Brauerei G.A. Bruch','Neufang Brauerei','Zum Stiefel'); $form_top="<form action=\"$_SERVER[PHP_SELF]\" method=\"get\"><p>\n"; $form_submit='<input type="submit" value="I try!"/>'."\n"; $form_end='<input type="hidden" name="submitted" value="1"/></p></form>';

40. brewer_quiz.php: form building function build_form($answer,$comment) { print "<div>Take the Saarland brewery challenge</div>\n"; print $GLOBALS['form_top']; print "<input type=\"text\" name=\"guess\" value=\"$answer\"/>"; print $GLOBALS['form_submit']; print $GLOBALS['submit_check']; print $GLOBALS['form_end']; print $comment; }

41. brewer_quiz.php: form processing function process_form($answer,$brewers) { $r[]=$answer; foreach($brewers as $brew) { if($answer == "$brew") { $r[]='<div>Congradulation! This is correct!</div>'; return $r; } } $r[]='<div>This is a bad answer, try again!</div>'; return $r; }

42. brewer_quiz.php main part if($_GET['submitted']) { $from_form=process_form($_GET['guess'],$brewers); } build_form($from_form[0],$from_form[1]) ; ?>

43. working with many source files • Many times it is useful to split a PHP script into several files. • PHP has two mechanisms. • require(file) requires the to be included. If the file is not there, PHP exits with an error. • include(file) includes the file.

44. require() and include() • Both assume that you leave PHP. Thus within your included file you can write simple HTML. • If you want to include PHP in your included file, you have to surround it by <?php and ?>, just like in a PHP script. • Here is an example to use include to build the basic web page.

45. bottom.html <p id="validator"> <a href="http://validator.w3.org/check?uri=referer"><img style="border: 0pt" src="http://wotan.liu.edu/valid-xhtml10.png" alt="Valid XHTML 1.0!" height="31" width="88" /></a> </p> </body> </html>

46. validated.php <?php $title="my basic page\n"; include("top.html"); print "<div>hello, world</div>"; include("bottom.html"); ?>

47. trouble • $title in the top.html is not understood as the title. It reads as $title, which means "idiot" for your web user. • Even if you replace $title with <?php $title ?> $title is empty. The definition from the outer file is not seen in the included file. • So you have to split into three files, and print the title in the main file. I leave that to you to figure out.

48. Thank you for your attention! http://openlib.org/home/krichel