1 / 34

Introduction to Database Programming on the Web

This article provides an introduction to programming for database systems on the web, with a focus on using PHP and MySQL as a case study. It covers key concepts, web development tools, the LAMP/WAMP stack, and provides resources for setting up a web server and working with databases.

ambera
Download Presentation

Introduction to Database Programming on the Web

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. CS4433Database Systems DB Programming on the Web

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

  3. What is Internet?

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

  5. Key Concepts • HTTP: Hyper-Text Transfer Protocol • 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)

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

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

  8. XAMPP • Free Web Server Solution Package •  Everything you need to set up a web server • – server application (Apache), database (MySQL), and scripting language (PHP) – • included in a simple extractable file.  • Cross-platform •  means it works equally well on(Mac, Windows, Linux, Solaris): http://www.apachefriends.org/en/xampp.html • Apache HTTP Server • MySQL/MariaDB databases, phpMyAdmin • PHP, Perl • FileZilla FTP Server • …… • XAMPP Tutorial: • https://www.udemy.com/blog/xampp-tutorial/

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

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

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

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

  13. MySQL-PHP API - Connection • Mysql_connect opens 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 • mysqli_connect:new versions of PHP (>5.0) • Mysql_select_db selects the desired database • Mysql_closecloses the connection at the end • It is automatically called at the end of the script • mysqli_close

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

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

  16. 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(); • }

  17. 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 • Mysqli_fetch_array, Mysqli_fetch_assoc • mysql_free_result(H) • frees the result set • It is called automatically at the end of the script • Mysqli_free_result

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

  19. 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 • More functions at https://dev.mysql.com/doc/apis-php/en/apis-php-mysqlinfo.html • Example: Create a Simple View Counter • http://www.youtube.com/watch?annotation_id=annotation_257200&feature=iv&src_vid=4rfeWzpszQU&v=jAKcdxKhiJ8

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

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

  22. 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 • ……

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

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

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

  26. 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 from data after a form is submitted

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

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

  29. PHP Variables • Variables are used for storing values, like text strings, numbers or arrays • All variables in PHP start with a $ 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)

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

  31. $_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

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

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

  34. 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 • Installing, configuring, and developing with XAMPP • http://dalibor.dvorski.net/downloads/docs/InstallingConfiguringDevelopingWithXAMPP.pdf • Also for using PHP with MySQL • Hugh Williams and David Lane, ``Web Database Applications with PHP and MySQL'' 2nd edition

More Related