CSC Web Programming

1. CSC Web Programming In the next lectures you will learn What is SQL How to access mySQL database How to create a basic mySQL database How to use some basic queries How to use PHP and mySQL

2. Introduction to SQL SQL is an ANSI (American National Standards Institute) standard computer language for accessing and manipulating databases. • SQL stands for Structured Query Language • using SQL can you can • access a database • execute queries, and retrieve data • insert, delete and update records • SQL works with database programs like MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase, mySQL, etc. Unfortunately, there are many different versions. But, they must support the same major keywords in a similar manner such as SELECT, UPDATE, DELETE, INSERT, WHERE, etc. Most of the SQL database programs also have their own proprietary extensions!

3. Application Structure Application Software Database Data Model End User SQL SQL Developer Database Tools DBA

4. Database Administrator (dba) A database administrator (DBA) is a person responsible for the design, implementation, maintenance and repair of an organization's database. The role includes the development and design of database strategies, monitoring and improving database performance and capacity, and planning for future expansion requirements. They may also plan, co-ordinate and implement security measures to safeguard the database.

5. Database Model A database model or database schema is the structure or format of a database, described in a formal language supported by the database management system, In other words, a "database model" is the application of a data model when used in conjunction with a database management system.

6. SQL • Structured Query Language is the language we use to issue commands to the database • Create a table • Retrieve some data • Insert data • Delete data

7. Common Database Systems • Three Major Database Management Systems in wide use • MySQL - Simple fast and scalable - commercial open source • Oracle - Large, commercial, enterprise-scale, very verytweakable • SqlServer - Very nice - from Microsoft (also Access) • Many other smaller projects, free and open source • HSQL, SQLite, Postgress, ...

8. Basic SQL Syntax

9. MySQL – Key Commands MySQL Resource: http://dev.mysql.com/doc/refman/5.5/en/index.html

10. Here at GSU... The University of GSU CS department has a version of mySQL installed on the servers, and it is this system that we use in this course. Most all of the commands discussed here should work with little (or no) change to them on other database systems. A mySQL database has been created for your (Computer Science) account here (if you didn’t have one already for another course), and it is this database that you should use for assignment 4 (the PHP/mySQL assignment).

11. SQL Database Tables A database most often contains one or more tables. Each table is identified by a name (e.g. "Customers" or "Orders"). Tables contain records (rows) with data. For example, a table called "Persons": The table above contains three records (one for each person) and four columns (LastName, FirstName, Address, and City).

12. SQL Queries With SQL, you can query a database and have a result set returned. A query like this: SELECTLastNameFROMPersons; gives a result set like this: The mySQL database system requires a semicolon at the end of the SQL statement!

13. SQL Data Languages • The query and update commands together form the Data Manipulation Language (DML) part of SQL: • SELECT - extracts data from a database table • UPDATE - updates data in a database table • DELETE - deletes data from a database table • INSERT INTO - inserts new data into a database table • The Data Definition Language (DDL) part of SQL permits database tables to be created or deleted: • CREATE TABLE - creates a new database table • ALTER TABLE - alters (changes) a database table • DROP TABLE - deletes a database table • CREATE INDEX - creates an index (search key) • DROP INDEX - deletes an index *Here we will use some of them in mySQL

14. bash-2.05b$-u myname -p 1db23 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 209201 to server version: 5.0.22 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> mysql>usehenry; Database changed Logging into mySQL Server You can log into our mySQL server from Linux by typing in the prompt From here you can create, modify, and drop tables, and modify the data in your tables. But first, you must specify which database on the server you want to use (you have only one, however).

15. Technical note • You probably don’t need to worry about this, but thought I would mention it here… Most books and on-line tutorials assume the database server is running on the same machine as everything else, and that the user is "root". Neither of these are true here. Wherever you see "localhost", replace it by "mysql" Wherever you see "root", replace it with your username. (Ignore this if you don’t understand it for now, or are not consulting other references.)

16. MySQL & PHP: Process Steps • The process of using MySQL with PHP: • Connect to MySQLmysql_connect • Select the database to use mysql_select_db • Build a query string $query = "SELECT*FROMtableName"; • Perform the query $result = mysql_query($query); • Retrieve the results and output it to a web page • $rows = mysql_num_rows($result); • $row = mysql_fetch_row($result); • Repeat Steps 3 to 5 until all desired data retrieved. • Disconnect from MySQL(usually done automatically)

17. MySQL & PHP: login.php for your database – Step 0 • Create login_lastname.php file • <?php// login_lastname.phpmake sure to place in personal folder • $db_hostname = 'localhost'; • $db_database = 'yourMySQLusername'; • $db_username = 'yourMySQLusername'; • $db_password = 'yourMySQLpassword'; • ?>

18. MySQL & PHP: Connect to Database – Steps 1-4 • <?php // query.php • require_once 'login_lastname.php'; • $db_server = mysql_connect($db_hostname, $db_username, $db_password); • if (!$db_server) die("Unable to connect to MySQL: " . mysql_error());  • mysql_select_db($db_database) or die("Unable to select database: " . mysql_error()); • $query = "SELECT * FROM classics"; • $result = mysql_query($query); • if (!$result) die ("Database access failed: " . mysql_error()); • $rows = mysql_num_rows($result); • for ($j = 0 ; $j < $rows ; ++$j){ • $row = mysql_fetch_row($result); • echo 'Author: ' . $row[0] . '<br />'; • echo 'Title: ' . $row[1] . '<br />'; • echo 'Year: ' . $row[3] . '<br />'; • echo 'ISBN: ' . $row[5] . '<br /><br />'; • ?>

19. Your first MySQL Command • Kind of like "print 'hello world'" • show databases;

20. If this does not work, stop and figure out why. Some of these are part of MySQL and store internal data - don't mess with them.

21. Query OK, 0 rows affected (0.02 sec) *If the server gives you a big ERROR, just try again from the top! Creating a Table You can create a table you might use for the upcoming project. For example, mysql>CREATE TABLEstudents( -> numINT NOT NULL AUTO_INCREMENT, -> f_nameVARCHAR(48), -> l_nameVARCHAR(48), -> student_idINT, -> emailVARCHAR(48), -> PRIMARY KEY(num)); Hit Enter after each line (if you want). MySQL doesn’t try to interpret the command itself until it sees a semicolon (;) (The “->” characters you see are not typed by you.)

22. Creating a Table CREATE TABLE classics ( author VARCHAR(128), title VARCHAR(128), type VARCHAR(16), year CHAR(4)) ENGINE MyISAM; DESCRIBE classics;

23. +------------+-------------+------+-----+---------+----------------++------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | num | int(11) | NO | PRI | NULL | auto_increment | | f_name | varchar(48) | YES | | NULL | | | l_name | varchar(48) | YES | | NULL | | | student_id | int(11) | YES | | NULL | | | email | varchar(48) | YES | | NULL | | +------------+-------------+------+-----+---------+----------------+ Viewing The Table Structure Use DESCRIBEto see the structure of a table mysql>DESCRIBE students;

24. Text Fields • Understand character sets and indexable for searching • CHAR allocates entire space (faster for small strings where length is known) • VARCHAR allocates variable amount of space depending on the data length (less space)

25. Binary Types (rarely used) • Character = 8 - 32 bits of information depending on character set • Byte = 8 bits of information • Small Images - data

26. Text Fields • Have a character set

27. Binary Large Object (BLOB) • Large raw data, files, images, word documents, PDF, Movies, etc etc.. • No translation, indexing or character set

28. Numbers • Numbers are very efficient, take little storage and are easy to process because CPU's can compare them often with a single instruction 32 bit 64 bit

29. Dates • TIMESTAMP is very efficient but only can handle dates from 1970 through 2037 32 bit

30. AUTO_INCREMENT • Often as we make multiple tables and need to JOIN them together we need an integer, primary key for each row so we can efficiently add a reference to a row in a table, in some other table as a foreign key DROP TABLE classics; CREATE TABLE classics ( id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY, author VARCHAR(128), title VARCHAR(128), type VARCHAR(16), year CHAR(4)) ENGINE MyISAM; DESCRIBE classics;

31. Data Types • Text fields (small and large) • Binary fields (small and large) • Numeric fields • AUTO_INCREMENT fields

32. Query OK, 1 row affected (0.00 sec) Using SELECT FROMyou select some data from a table. mysql>SELECT*FROMstudents; +-----+---------+--------+------------+----------------------+ | num | f_name | l_name | student_id | email | +-----+---------+--------+------------+----------------------+ | 1 | Russell | Martin | 396640 | martin@csc.liv.ac.uk | +-----+---------+--------+------------+----------------------+ 1 row in set (0.00 sec) Inserting Data UsingINSERT INTOyou can insert a new row into your table. For example, mysql>INSERT INTOstudents -> VALUES(NULL,’Russell’,’Martin’,396640,‘martin@cs.gsu.edu');

33. Inserting Some More Data You can repeat inserting until all data is entered into the table. mysql>INSERTINTOstudents -> VALUES(‘James',‘Bond',007,'bond@cs.gsu.edu'); Query OK, 1 row affected (0.01 sec) mysql>SELECT*FROMstudents; +-----+---------+--------+------------+----------------------+ | num | f_name | l_name | student_id | email | +-----+---------+--------+------------+----------------------+ | 1 | Russell | Martin | 396640 | martin@cs.gsu.edu | | 2 | James | Bond | 7 | bond@gmail.com | +-----+---------+--------+------------+----------------------+ 2 rows in set (0.00 sec) Note: The value “NULL” in the “num” field is automatically replaced by the SQL interpreter as the “auto_increment” option was selected when the table was defined.

34. Inserting... INSERT INTO classics(author, title, type, year) VALUES('Mark Twain','The Adventures of Tom Sawyer','Fiction','1876');INSERT INTO classics(author, title, type, year) VALUES('Jane Austen','Pride and Prejudice','Fiction','1811');INSERT INTO classics(author, title, type, year) VALUES('Charles Darwin','The Origin of Species','Non-Fiction','1856');INSERT INTO classics(author, title, type, year) VALUES('Charles Dickens','The Old Curiosity Shop','Fiction','1841');INSERT INTO classics(author, title, type, year) VALUES('William Shakespeare','Romeo and Juliet','Play','1594');

35. Inserted... mysql> select * from classics;+----+---------------------+------------------------------+-------------+------+| id | author | title | type | year |+----+---------------------+------------------------------+-------------+------+| 1 | Mark Twain | The Adventures of Tom Sawyer | Fiction | 1876 || 2 | Jane Austen | Pride and Prejudice | Fiction | 1811 || 3 | Charles Darwin | The Origin of Species | Non-Fiction | 1856 || 4 | Charles Dickens | The Old Curiosity Shop | Fiction | 1841 || 5 | William Shakespeare | Romeo and Juliet | Play | 1594 |+----+---------------------+------------------------------+-------------+------+5 rows in set (0.00 sec)mysql>

36. mysql> describe classics;+--------+------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+------------------+------+-----+---------+----------------+| id | int(10) unsigned | NO | PRI | NULL | auto_increment || author | varchar(128) | YES | | NULL | || title | varchar(128) | YES | | NULL | || type | varchar(16) | YES | | NULL | || year | char(4) | YES | | NULL | |+--------+------------------+------+-----+---------+----------------+5 rows in set (0.00 sec)mysql> ALTER TABLE classics MODIFY year SMALLINT;QueryOK, 5 rows affected (0.08 sec)Records: 5 Duplicates: 0 Warnings: 0mysql> describe classics;+--------+------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+------------------+------+-----+---------+----------------+| id | int(10) unsigned | NO | PRI | NULL | auto_increment || author | varchar(128) | YES | | NULL | || title | varchar(128) | YES | | NULL | || type | varchar(16) | YES | | NULL | || year | smallint(6) | YES | | NULL | |+--------+------------------+------+-----+---------+----------------+5 rows in set (0.00 sec)

37. mysql> ALTER TABLE classics ADD pages SMALLINT;Query OK, 5 rows affected (0.12 sec)Records: 5 Duplicates: 0 Warnings: 0mysql> describeclassics;+--------+------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+------------------+------+-----+---------+----------------+| id | int(10) unsigned | NO | PRI | NULL | auto_increment || author | varchar(128) | YES | | NULL | || title | varchar(128) | YES | | NULL | || type | varchar(16) | YES | | NULL | || year | smallint(6) | YES | | NULL | || pages | smallint(6) | YES | | NULL | |+--------+------------------+------+-----+---------+----------------+6 rows in set (0.08 sec)mysql>

38. Getting Data Out of the Table • The SELECT command is the main way of getting data out of a table, or set of tables. SELECT*FROMstudents; Here the asterisk means to select (i.e. return the information in) all columns. You can specify one or more columns of data that you want, such as SELECT f_name,l_name FROMstudents; +---------+--------+ | f_name | l_name | +---------+--------+ | Russell | Martin | | James | Bond | +---------+--------+ 2 rows in set (0.00 sec)

39. Getting Data Out of the Table (cont.) • You can specify other information that you want in the query using the WHERE clause. SELECT*FROMstudents WHERE l_name=‘Bond’; +-----+---------+--------+------------+----------------------+ | num | f_name | l_name | student_id | email | +-----+---------+--------+------------+----------------------+ | 2 | James | Bond | 7 | bond@csc.gmail.com | +-----+---------+--------+------------+----------------------+ 1 row in set (0.00 sec) SELECTstudent_id, emailFROMstudents WHERE l_name=‘Bond’; +------------+----------------------+ | student_id | email | +------------+----------------------+ | 7 | bond@gmail.com | +------------+----------------------+ 1 row in set (0.00 sec)

40. Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 Altering the Table TheALTER TABLEstatement is used to add or drop columns in an existing table. mysql>ALTER TABLEstudentsADDdateDATE; mysql>SELECT*FROMstudents; +-----+---------+--------+------------+----------------------+------+ | num | f_name | l_name | student_id | email | date | +-----+---------+--------+------------+----------------------+------+ | 1 | Russell | Martin | 396640 | martin@csc.gmail.com | NULL | | 2 | James | Bond | 7 | bond@gmail.com | NULL | +-----+---------+--------+------------+----------------------+------+ 2 rows in set (0.00 sec)

41. Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 Updating the Table TheUPDATEstatement is used to modify data in a table. mysql>UPDATEstudentsSETdate='2007-11-15'WHEREnum=1; mysql>SELECT*FROMstudents; +-----+---------+--------+------------+----------------------+------------+ | num | f_name | l_name | student_id | email | date | +-----+---------+--------+------------+----------------------+------------+ | 1 | Russell | Martin | 396310 | lhenry@cs.gsu.edu | 01-08-2012 | | 2 | James | Bond | 7 | lhenry@cs.gsu.edu | NULL | +-----+---------+--------+------------+----------------------+------------+ 2 rows in set (0.00 sec) Note that the default date format is “YYYY-MM-DD” and I don’t believe this default setting can be changed.

42. Query OK, 1 row affected (0.00 sec) Deleting Some Data The DELETEstatement is used to delete rows in a table. mysql>DELETE FROMstudentsWHEREl_name='Bond'; mysql>SELECT*FROMstudents; +-----+---------+--------+------------+----------------------+------------+ | num | f_name | l_name | student_id | email | date | +-----+---------+--------+------------+----------------------+------------+ | 1 | Russell | Martin | 396310 | lhenry@cs.gsu.edu | 2006-11-15 | +-----+---------+--------+------------+----------------------+------------+ 1 row in set (0.00 sec)

43. The Final Table We’ll first add another column, update the (only) record, then insert more data. mysql>ALTER TABLEstudentsADDgrINT; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql>SELECT* FROMstudents; +-----+---------+--------+------------+----------------------+------------+------+ | num | f_name | l_name | student_id | email | date | gr | +-----+---------+--------+------------+----------------------+------------+------+ | 1 | Russell | Martin | 396310 | martin@gmail.com | 2007-11-15 | NULL | +-----+---------+--------+------------+----------------------+------------+------+ 1 row in set (0.00 sec) mysql>UPDATEstudentsSETgr=3WHEREnum=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql>SELECT*FROMstudents; +-----+---------+--------+------------+----------------------+------------+------+ | num | f_name | l_name | student_id | email | date | gr | +-----+---------+--------+------------+----------------------+------------+------+ | 1 | Russell | Martin | 396310 | martin@gmail.com | 2007-11-15 | 3 | +-----+---------+--------+------------+----------------------+------------+------+ 1 row in set (0.00 sec) mysql>INSERTINTOstudents VALUES(NULL,‘James',‘Bond',007,'bond@csc.liv.ac.uk‘,‘2007-11-15’, 1); . . . . . .

44. The Final Table (cont.) . . . . . . mysql>INSERTINTOstudents VALUES(NULL,‘Hugh,‘Milner',75849789,‘hugh@poughkeepsie.ny‘, CURRENT_DATE, 2); Note: CURRENT_DATE is a built-in SQL command which (as expected) gives the current (local) date. mysql> SELECT * FROMstudents; +-----+---------+----------+------------+----------------------------+------------+------+ | num | f_name | l_name | student_id | email | date | gr | +-----+---------+----------+------------+----------------------------+------------+------+ | 1 | Russell | Martin | 396310 | martin@gmail.com | 2007-11-15 | 3 | | 5 | Kate | Ash | 124309 | kate@gmail.com | 2007-11-16 | 3 | | 3 | James | Bond | 7 | bond@gmail.com | 2007-11-15 | 1| | 4 | Bob | Jones | 12190 | bob@nowhere.com | 2007-11-16 | 3 | | 6 | Pete | Lofton | 76 | lofton@iwannabesedated.com | 2007-11-17 | 2 | | 7 | Polly | Crackers | 1717 | crackers@polly.org | 2007-11-17 | 1| | 8 | Hugh | Milner | 75849789 | hugh@poughkeepsie.ny | 2007-11-17 | 2 | +-----+---------+----------+------------+----------------------------+------------+------+ 7 rows in set (0.00 sec) mysql> exit Bye

45. Other SQL Commands • SHOW tables; gives a list of tables that have been defined in the database • ALTER TABLE studentsDROPemail; would drop the “email” column from all records • DROP TABLE students; deletes the entire “students” table, and its definition (use the DROP command with extreme care!!) • DELETE FROM students; removes all rows from the “students” table (so once again, use the DELETEcommand with great caution),the table definition remains to be used again • A more useful command is something like DELETE FROM students WHERE(num > 5) AND (num <= 10); which selectively deletes students based on their “num” values (for example). • HELP; gives the SQL help • HELP DROP; gives help on the DROP command, etc.

46. Backing up/restoring a mySQL database • You can back up an entire database with a command such as mysqldump –h mysql –u martin martin > backup.sql (Run from the Unix command line.) • This gives a script containing SQL commands to reconstruct the table structure (of all tables) and all of the data in the table(s). • To restore the database (from scratch) you can use this type of Unix command: mysql –h mysql –u martin martin < backup.sql (Use with caution, as this can overwrite your database.) • Other commands are possible to backup/restore only certain tables or items in tables, etc. if that is what you desire. For example mysqldump –h mysql –u martin martin books clients> backup.sql stores information about the “books” and “clients” tables in the “martin” database.

47. Putting Content into Your Database with PHP We can simply use PHP functions and mySQL queries together: • Connect to the database server and login (this is the PHP command to do so) • mysql_connect("host","username","password"); • Choose the database • mysql_select_db("database"); • Send SQL queries to the server to add, delete, and modify data • mysql_query("query"); (use the exact same query string as you would • normally use in SQL, without the trailing semi-colon) • Close the connection to the database server (to ensure the information is stored properly) • mysql_close(); • Note: For this to work properly on the UoL server, you must access the PHP script through the cgi server (http://cgi.csc.liv.ac.uk/~martin/getstuff.php for example).

48. Student Database: data_in.php <html> <head> <title>Putting Data in the DB</title> </head> <body> <?php /*insert students into DB*/ if(isset($_POST["submit"])) { $db = mysql_connect("mysql”, “henry"); mysql_select_db(“henry"); $date=date("Y-m-d"); /* Get the current date in the right SQL format */ $sql="INSERT INTOstudents VALUES(NULL,'“ . $_POST[“f_name"] . "','“ . $_POST["l_name"] . "',“ . $_POST["student_id"] . ",'“ . $_POST["email"] . "','“ . $date . "',“ . $_POST["gr"] . ")"; /* construct the query */ mysql_query($sql); /* execute the query */ mysql_close(); echo"<h3>Thank you. The data has been entered.</h3> \n"; echo'<p><a href="data_in.php">Back to registration</a></p>‘ . “\n”; echo'<p><a href="data_out.php">View the student lists</a></p>‘ .”\n”; }

49. Student Database: data_in.php else { ?> <h3>Enter your items into the database</h3> <form action="data_in.php" method="POST"> First Name: <input type="text" name=“f_name“ /> <br/> Last Name: <input type="text" name=“l_name“ /> <br/> ID: <input type="text" name=“student_id“ /> <br/> email: <input type="text" name=“email“ /> <br/> Group: <select name="gr"> <option value ="1">1</option> <option value ="2">2</option> <option value ="3">3</option> </select><br/><br/> <input type="submit" name="submit“ /> <input type="reset“ /> </form> <?php } ?> </body> </html>

50. Getting Content out of Your Database with PHP Similarly, we can get some information from a database: • Connect to the server and login, choose a database • mysql_connect("host","username","password"); • mysql_select_db("database"); • Send an SQL query to the server to select data from the database into an array • $result=mysql_query("query"); • Either, look into a row and a fieldname • $num=mysql_numrows($result); • $variable=mysql_result($result,$i,"fieldname"); • Or, fetch rows one by one • $row=mysql_fetch_array($result); • Close the connection to the database server • mysql_close();