COP4710 Database Systems

1. COP4710Database Systems DB Programming on the Web Fall 2014

2. Introduction • Programming Environment • Database Programming in the Web Era • Web Programming PHP + MySQL as a case study

3. Motivation • Here Comes Another Bubble: http://www.youtube.com/watch?v=I6IQ_FOCE6I • Are you feeling “so behind the times”? • Closely relevant to your programming project!

4. What is Internet?

5. How does the web work? • Information is stored in documents called Web pages • index.html, index.php, etc. • Web pages are files on computers called Web servers • Apache, Nginx, IIS, etc. • Computers reading the Web pages are called Web clients • PCs, Laptops, Phones, Tablets, etc. • Web clients view the pages with a program called Web browsers • IE, Chrome, and Firefox, Opera, etc.

6. Key Concepts • HTTP: Hyper TextTransferProtocol • The underlying protocol used by the World Wide Web • Defines how messages are formatted and transmitted • Defines actions Web servers and browsers should take in response to various commands • HTML: Hyper Text Markup Language • Specific standard used for the World Wide Web • Can embed scripting languages (e.g. PHP, JavaScript) • Interpreted by web browser (client side)

7. Web Development Tools • Client-side: • HTML: Primary language for creating web pages (page structure) • CSS: How HTML elements are to be displayed (page style) • JavaScript: Client-side scripting language (browser behavior) • …… • Server-side: • PHP: Server-side scripting language (server behavior) • SQL: Standard language for accessing and manipulating databases • ……

8. LAMP (WAMP) • Linux, Apache, MySQL and PHP • Solution stack of free, open source software • Linux • Unix-like open source computer operating system • Apache (www.apache.org) • Open source web server, most popular in use • MySQL (www.mysql.com) • SQL relational database management system (RDBMS) • PHP (www.php.net) • Server side scripting language

9. XAMPP • Free Web Server Solution Package • Cross-platform (Mac, Windows, Linux, Solaris): http://www.apachefriends.org/en/xampp.html • Apache HTTP Server • MySQL database, phpMyAdmin • PHP, Perl • FileZilla FTP Server • …… • XAMPP Tutorial: https://www.udemy.com/blog/xampp-tutorial/

10. XAMPP Install Run Test: http://localhost

11. How to work? • Put your helloworld.php file under xampp/htdocs folder • To test your program go to: http://localhost/helloworld.php • To check your MySQL database management system go to: http://localhost/phpMyAdmin • Create a Webserver on your computer with XAMPP • http://www.youtube.com/watch?v=nOe1K3f4gEg&feature=related • Create a Database and Table in phpMyAdmin • http://www.youtube.com/watch?v=nBz2lG_jm-A

12. DB Programming • SQL is a (very) high-level language • Not intended for general purpose computations • Solutions • Outside DBMS: use SQL together with general-purpose programming languages, e.g. PHP, etc. • Inside DBMS: augment SQL with constructs from general-purpose programming languages

13. Typical DB Programming Procedure • Connect to a DB Server • Specify what database you want to use • Create a string containing an SQL statement • Execute the statement • Extract the results into variables in the local programming language • Database Specific API • Designed and implemented by the DBMS vendor for a specific programming language

14. MySQL-PHP API - Connection • Mysql_connectopens a connection to the DBMS • It gets the DBMS and login information and returns a connection resource • The connection resource is used in future calls • Mysql_select_db selects the desired database • Mysql_closecloses the connection at the end • It is automatically called at the end of the script

15. Connection - Example • MySQL-PHP API:: Connection example in PHP • $username = “USERNAME”; • $password = “PASSWORD”; • $host = “localhost”; • $dbname= “DatabaseName”; • $connect = mysql_connect($host, $username, $password); • mysql_select_db($dbname, $connect); • //Do Stuff with the DB Connection • mysql_close($connect); • PHP: Connect to a MySQL database • http://www.youtube.com/watch?v=4rfeWzpszQU

16. Executing SQL Statement • mysql_query(S, C) • Causes the SQL statement represented by Sto be executed on connection C • mysql_queryreturns • a handle to the query result set • TRUE/FALSE for DELETE, UPDATE, and INSERT • We can detect DBMS initiated errors using mysql_error()

17. Executing SQL Statement - Example • $query = “SELECT * FROM Beer”; • $result = mysql_query($query, $connect); • $query = “INSERT INTO Beer (manf, beer) VALUES (“Miller”, “High Life”); ” • if (mysql_query($query, $connect)) { • echo “Insert Successful”; • } else { • echo “Insert Failed:”. mysql_error(); • }

18. Fetching Tuples • When the SQL statement executed is a query, we need to fetch the tuples of the result • mysql_fetch_array(H) • gets the tuples from the result set H and • stores them in an associative array • mysql_free_result(H) • frees the result set • It is called automatically at the end of the script

19. Fetching Tuples: Example • $query = “SELECT * FROM Beer”; • $result = mysql_query($query, $connect); • While ($row = mysql_fetch_array($result)) { • $manf= $row[‘manf’]; • $beer = $row[‘beer’]; • echo “The beer”. $beer . “ is made by”. $manf; • } • mysql_free_result($result);

20. Other PHP-MySQL Commands • mysql_num_rows(r) • Checks the Result handler to see how many rows were returned • mysql_affected_rows() • Useful on DELETE or UPDATE commands to see how many rows were changed • mysql_insert_id() • Useful for AUTO_INCREMENT fields • Get the last ID (PRIMARY KEY) you just inserted • More functions athttp://us.php.net/mysql • Example:Create a Simple View Counter • http://www.youtube.com/watch?annotation_id=annotation_257200&feature=iv&src_vid=4rfeWzpszQU&v=jAKcdxKhiJ8

21. Web Programming • Client Side Programming: Code that runs on the client side’s browser • HTML • Predominant markup language for web pages • Written in the form of HTML elements consisting of "tags" surrounded by angle brackets within the web page content • Javascript • often implemented as part of a web browser in order to provide enhanced user interfaces and dynamic websites

22. Web Programming • Client Side Programming: Code that runs on the client side’s browser • Applet and ActiveX • small application that performs one specific task and runs within a larger program • E.g. Java Applet running on web pages • AJAX (Asynchronous Javascript And XML) • To create interactive web applications • Using AJAX, web applications can send data and retrieve data from a server asynchronously (in the background)

23. Web Programming • Server Side Programming: Code that runs on the server side • CGI: standard protocol for server-client communication • PHP: open source • ASP.NET: Microsoft proprietary • JSP: JavaServer Pages • Python, e.g. Django web framework, open source • Ruby, e.g. Ruby on Rails, open source • ……

24. HTML • Hyper Text Markup Language • .html & .htm format – both are correct • Interpreted by browser to show elements on the webpage • Can containscriptinglanguages(PHP, javascript, etc.) • Tags • Keywords surrounded by angle brackets <tag> • Tags come in pairs: opening and closing: <tag> </tag> • Elements come between these tag: <tag>element</tag> • <a href=“http://example.com”>Click here</a> = Click here

25. HTML Basic Tags • <html> </html> • Contain all the elements in the page • <head> </head> • Contain header information such as title, meta data, stylesheets • <body> </body> • Contains elements that are to be shown on the webpage • <h1> … <h6> for headings • <img> image

26. HTML Basic Tags • <table> table • <tr> table row • <td> table data • <form> : HTML forms are used to pass data to a server • text fields, checkboxes, radio-buttons, submit buttons and etc. • $_GET and $_POST methods

27. HTML Tag Attributes • Each tag has attributes • Attributes provide additional information about the element • Should be specified in the opening tag • Attribute name = “attribute value” • Example • <input type=“text” id=“firstname” name=“firstname”> • type: specifies the type of <input> element to display • id: specifies a unique id for an HTML element can be used by CSS or JavaScript • name: specifies the name of an <input> element used to reference form data after a form is submitted

28. PHP Overview • Name comes from Hypertext Preprocessor • Originally Personal Home Page Tools (PHP Tools) • Open source, server-side, HTML embedded scripting language used to create dynamic Web pages • PHP runs on different platforms • PHP is compatible with almost all servers used today, such as Apache, IIS • PHP files have a file extension of ".php", ".php3", or ".phtml"

29. PHP Syntax • PHP scripting block • <?php Script Here ?> • Can be place anywhere in the HTML code • Can contain HTML tags • Script consists of statements and variables • Script code should end with semicolon (;) • // for single line comment, /* comment */ for multiline comment

30. PHP Variables • Variables are used for storing values, like text strings, numbers or arrays • All variables in PHP start with a $ sign symbol • In PHP, a variable does not need to be declared before adding a value to it • Variable type is not necessary to be declared • $x=16; $txt="Hello World!"; • A variable name should not contain spaces. If a variable name is more than one word, it can be separated with an underscore ($my_string)

31. PHP Variables • String concatenation: $a . $b • Single quote and double quote are different • Variable in double quote is replaced by its value • Variable in single quote is shown as it is • Example: • $email_address = me@domain.com; • <?php echo “My email: $email_address”; ?>: displays My email: me@domain.com • <?php echo ‘My email: $email_address’; ?>: displays My email: $email_address

32. $_GET Vs. $_POST • $_GET variable is used to collect values in a form with method="get" • Information sent from a form with the GET method is visible to everyone and has limits on the amount of information to send • $_POST variable is used to collect values from a form sent with method="post" • Information sent from a form with the POST method is invisible to others and has no limits on the amount of information to send

33. $_POST Usage <form action="welcome.php" method="post"> Name: <input type="text" name="fname" /> Age: <input type="text" name="age" /> <input type="submit" /> </form> • How we use it in PHP: • Welcome <?php echo $_POST["fname"]; ?>!<br /> • You are <?php echo $_POST["age"]; ?> years old

34. Take-home Message • Web-based DB Programming is not hard • but it takes time to learn basics and fix bugs • Choose your preferred tools early, and start to get familiar with them • Many tutorials (with examples) available on the web • W3Schools is a great place to start: http://www.w3schools.com

35. References • Getting starting with CGI Programming in C • http://www.cs.tut.fi/~jkorpela/forms/cgic.html • PHP related examples, including PHP + MySQL http://www.w3schools.com/php/ • W3Schools tutorials are generally very helpful, on almost all the tools we covered • Also for using PHP with MySQL • Hugh Williams and David Lane, ``Web Database Applications with PHP and MySQL'' 2nd edition