LIS651 lecture 2 databases, mySQL and PHP mySQL functions

1. LIS651 lecture 2databases,mySQL and PHP mySQL functions Thomas Krichel 2008-11-08

2. today • Today I talk about • arrays and table (again, since this causes problems) • some basic mySQL commands • a PHP interface to mySQL called mysqli • Today you do • create a table with phpmyadmin • write PHP scripts to read/write data to the table using the web

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

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

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

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

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

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

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

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

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

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

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

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

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

16. SQL • SQL, also pronounced sequel, stands for "structured query language". • It is a standard language for querying database. • In database speak a query is anything one can do to a database.

17. casing and colon • Traditionally SQL commands are written with uppercase. • mySQL commands are really case-insensitive. • But variable names in the commands are case-sensitive. I will therefore write them in lowercase. • All SQL statements are ended with a semicolon.

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

19. phpmyadmin • phpmyadmin is a set of PHP scripts that create a general purpose interface to work with a mySQL database. • It is written in PHP. • It lives at http://wotan.liu.edu/phpmyadmin. • You need an account. This is not your wotan account, but a permission to use a database on the mySQL server running at wotan.

20. using mySQL • mySQL server is installed on wotan. • It is a daemon that deals with client requests. • There is also a tty client installed. To use it you log into wotan and type mysql -u user -p and then you type in your password. • We will cover this in the last lecture.

21. CREATE DATABASE • CREATE DATABASE a mySQL command to create a new database. • Example CREATE DATABASE newbase; • creates a database newbase. • You have no privileges to create a database. • But I don’t see the reason you wanting to do that.

22. creating mySQL databases • To create a user account for your, I did CREATE DATABASE user_name; • user_name is your user name. It is the name of your database as well as the name under which you are logging in.

23. USE • USE database;tells mySQL to start working with the database database. • If you have not issued a USE command, you can still address a table table by using database.table, where datebase is the name of your database and table is the name of your table. You are using the dot to link the two together.

24. GRANT • This is a command to create users and give them privileges. A simplified general syntax is GRANT privileges 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.

25. user privileges I • SELECT allows users to select (read) records from tables. Generally select is a word used for read in databases. • 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 (records)‏ • INDEX allows user to index tables

26. 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 to delete databases or tables. In general, the word drop refers to deleting database or tables.

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

28. creating mySQL databases • To allow you access to your databases, I did GRANT ALLON user_name.* TO user_name IDENTIFIED BY 'secret_word' WITH GRANT OPTION; • user_name is your user name. It is the name of your database as well as the name under which you are logging in. • 'secret_word' is the secret word we use for the quizzes.

29. REVOKE • This is the opposite of GRANT.

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

31. creating tables • This is done conveniently in phpmyadmin. • Here is an example for real SQL code CREATE TABLE customers (customer_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);

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

33. column data types: float • FLOAT is a floating number on 4 bytes • DOUBLE is a floating number on 8 bytes • DECIMAL(x,y) where x is the number of digits before the decimal point and y is the number of digits after the decimal point.

34. 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 date and time, usually displayed as YYYY-MM-DD HH:MM:SS • However, the easiest way I find is to store a date as as INT.

35. date as an INT • The UNIX time stamp is the number of seconds from the 1st of January 1970. • The current value is returned by the PHP function time(). • All values can be converted by the PHP funcition date() function into strings that humans can easily understand. Please refer to the help page http://php.net/manual/en/function.date.php to see the formating option.

36. 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. • NOT NULL requires the field not to be empty.

37. addressing database tables columns • Let there by a database database with a table table and some column column. Then it is addressed as database.table.column. • Parts of this notation can be left out if it is clear what is meant, for example if you have issued USE database before, you can leave out the database part.

38. character sets • There is a special configuration option with mysql working on wotan that tries to ensure utf-8 is the default character set. [mysqld] collation_server=utf8_unicode_ci character_set_server=utf8 skip-character-set-client-handshake • These options are set in a file /etc/mysql/conf.d/local.cnf on wotan.

39. collations • Collations are specifications of word order in sorting, and equivalences in searching. They are language specific. • A list of collation algorithms, that are compiled against the default unicode collection, can be found at http://developer.mimer.com/charts/. • If you require sorting of columns in a certain language, look for an available collation.

40. INSERT • INSERT inserts new rows into a table. In its simples form INSERT INTO table VALUES (value1, value2, ..); • Example: INSERT INTO products VALUES ('','Neufang Pils',1.23); • Note that in the example, I insert the null string in the first column because it is an auto_increment. • Mark Sandford says: If you use an auto_increment variable, you may as well have it last.

41. partial INSERT • If you are only giving a part of a record, or if you want to enter them in a different order you will have to give a list of column names. INSERT INTO products (name,id) VALUES ('Neufang Pils','');

42. SELECT • This is the SQL statement to select rows from a table. Here is the full syntax: SELECT [options] columns [INTO file_details] FROM table [WHERE conditions] [GROUP BY group_type] [HAVING where_definitions] [ORDER BY order_type] [LIMIT limit_criteria] [PROCEDURE proc_name(arguments)] [lock_options]

43. columns to SELECT • You can have a comma-separated list of columns SELECT name, price FROM products; • You can use the star to get all columns SELECT * FROM products;

44. WHERE condition to SELECT • = means equality WHERE id = 3 • >, <, >=, <= and != also work as expected • IS NULL tests if the value is null • IS NOT NULL • IN allows you to give a set WHERE state IN ("NY","NJ","CT")‏

45. ORDER • You can order by a field by saying ORDER BY. • You can add ASC or DESC to achieve ascending or descending order. SELECT name, address FROM customers ORDER BY name ASC

46. LIMIT • This can be used to limit the amount of rows. LIMIT 10 19 • This is useful it web sites where you show a selection of the results. • This ends the discussion of the SELECT command.

47. UPDATE • UPDATE [LOW_PRIORITY] [IGNORE] table SET column1=expression1, column2=expression2... [WHERE condition] [ORDER BY order_criteria][LIMIT number] ; • This changes values in a row. • An example is UPDATE students SET email= 'phpguru@gmail.com' WHERE name='Janice Insinga'; • IGNORE instructs to ignore errors. • LOW_PRIORITY instructs to delay if the server is busy.

48. DELETE • DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table [WHERE condition] [ORDER BY order_criteria] [LIMIT number] • Here is a very bad example DELETE FROM customers; • Here is a good example DELETE FROM customers WHERE customer.name='Thomas Krichel‘;

49. PHP mySQL functions • We are using here the new version of PHP mySQL function, starting with mysqli_ • The interface is object-oriented, but can also be accessed in a non-object-oriented way. This is known as the procedural style, in the documentation. • You should use the online documentation at http://php.net/mysqli

50. error suppression • The function mentioned in this library usually report any error that has occurred. • It can be useful to suppress such errors with the PHP error suppression operator @. • @function() will run the function function without reporting mistakes. • You can then create your own customized mistakes by checking for errors every time you run a mysqli function. This is useful.