C20.0046: Database Management Systems Lecture #15

1. C20.0046: Database Management SystemsLecture #15 M.P. Johnson Stern School of Business, NYU Spring, 2008 M.P. Johnson, DBMS, Stern/NYU, Spring 2006

2. Homework • Project part 3 due • Project part 4 posted soon… • Topic: populating your tables with data • Later: Project part 5 • Front-end M.P. Johnson, DBMS, Stern/NYU, Spring 2006

3. Agenda: Programming for SQL • Have now been exposed to: • Embedded SQL: Pro*C • Java JDBC • All used; good to know about • Most important for this course: • DB-conn from web scripting languages • PHP M.P. Johnson, DBMS, Stern/NYU, Spring 2006

4. Goals: by next week • Today: be able to post a hello-web PHP script in your sales account • Next week: Be able to write simple dynamic webpages in In PHP that • Take input from user • Execute SQL query • Display formatted results • Based on examples from class… M.P. Johnson, DBMS, Stern/NYU, Spring 2006

5. New topic: web apps • Goal: web front-end to database • Present dynamic content, on demand • Not canned (static) pages/not canned queries • (perhaps) modify DB on demand • Naïve soln: static webpage & HTTP • index.html written, stored, put on server, displayed when it’s url is requested • HTTP is stateless (so?) • This doesn’t solve our problem M.P. Johnson, DBMS, Stern/NYU, Spring 2006

6. Dynamic webpages • Soln 1: upon url request • somehow decide to dynamically generate an html page (from scratch) • send back new html page to user • No html file exists on server, just created on demand • CGI/Perl, Java servlets, etc. M.P. Johnson, DBMS, Stern/NYU, Spring 2006

7. New topic: CGI • First, and still very popular method • CGI: Common Gateway Interface • Not a programming language! • Just an interface (connection) between the webserver and an outside program • “Webserver” = webserver software, e.g., Apache • Very simple basic idea: • user chooses an url •  webserver runs that url’s program, • sends back the program’s output M.P. Johnson, DBMS, Stern/NYU, Spring 2006

8. Client Program HTTP Request Data for program HTML Generated HTML Server On-the-fly content with CGI • Image from http://www.scit.wlv.ac.uk/~jphb/cp3024/ M.P. Johnson, DBMS, Stern/NYU, Spring 2006

9. Using CGI • CGI works with any prog./scripting lang. • Really? • Well, no, not really… M.P. Johnson, DBMS, Stern/NYU, Spring 2006

10. CGI works… • if the webserver machine can run program • pages/soho, not sales • and if the user the webserver is running as (e.g. nobody) can can run your program • and if the necessary jars/libraries are available • and ifnobody has permission to use them • and if the necessary DB software is installed • Plausible choices: Perl, Python, C, sh M.P. Johnson, DBMS, Stern/NYU, Spring 2006

11. CGI admin • Most webservers: CGI program/script must either • End in .cgi and/or • Reside in cgi-bin • If an actual program, the cgi file is just the name of the executable: • In a script, first (“shebang”) line says which interpreter to use: gcc -o myprog.cgi myproc.gcc #!/usr/local/bin/perl M.P. Johnson, DBMS, Stern/NYU, Spring 2006

12. CGI input • CGI programs must respond to input, two ways • GET: string is part of the URL, following a ?: • POST: string can be read by program from an environmental variable • Vars not visible to the browser user • Not automatically put in server log, etc. http://google.com M.P. Johnson, DBMS, Stern/NYU, Spring 2006

13. CGI summary • One big advantage of CGI: not a language • Existing command-line programs can be called from the web • Web user can enter info • Program output sent back as webpage • Don’t want to start a new process for every user/pageview/roundtrip of your site? M.P. Johnson, DBMS, Stern/NYU, Spring 2006

14. Client Program HTTP Request Data for program HTML Generated HTML Server PHP-like scripting • Image from http://www.scit.wlv.ac.uk/~jphb/cp3024/ M.P. Johnson, DBMS, Stern/NYU, Spring 2006

15. Dynamic webpages • Original prob: need webpages to respond to user inputs • Soln 2: • create a an html file embedded with special non-html code • upon url request, execute embedded code to generate more html/fill in the file • Send back the modified html page to user • An incomplete html page exists on server • Examples: PHP, JSPs, ASPs, etc. M.P. Johnson, DBMS, Stern/NYU, Spring 2006

16. Review: dynamic webpages • First option: for each request: run program, produce whole page, send back • CGI & some host language, Java Servlets, etc. • Second option: create html page with missing parts; for each response, fill in the wholes and send back • Embedded scripting • PHP and others • PHP = Personal Home Page or = PHP Hypertext Processor M.P. Johnson, DBMS, Stern/NYU, Spring 2006

17. hello.php • http://pages.stern.nyu.edu/~mjohnson/dbms/php/hello.php • Q: What the difference between <br> and \n? <html> <head><title>Hello from PHP</title> </head> <body> Here comes the PHP part:<BR><BR> <?php print "Hello, World!<br>\n"; ?> <br>That's it! </body></html> M.P. Johnson, DBMS, Stern/NYU, Spring 2006

18. Script errors • Script errors, w/ and w/o display_errors on: • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/hello.php • Local dir must contain .htaccess: • Automatically load GET/POST params as vars • http://pages.stern.nyu.edu/~mjohnson/dbms/php/.htaccess php_flag display_errors on php_flag register_globals on M.P. Johnson, DBMS, Stern/NYU, Spring 2006

19. PHP vars • Names always start with $ • http://pages.stern.nyu.edu/~mjohnson/dbms/php/math.php <? $num1 = 58; $num2 = 67; print "First number " . $num1 . "<br>"; print "Second number " . $num2 . "<br>"; $total = $num1 + $num2; print "The sum is " . $total . "<br>"; ?> M.P. Johnson, DBMS, Stern/NYU, Spring 2006

20. Combining PHP and HTML • http://pages.stern.nyu.edu/~mjohnson/dbms/php/combine.php <?php for($z=0;$z<=5;$z++) { ?> Iteration number <? = $z ?><br> <? } ?> M.P. Johnson, DBMS, Stern/NYU, Spring 2006

21. More PHP syntax • Somewhat C-like, somewhat Perl-like • Case-sensitive • Strings: • Concatenation op: . • Single, double quotes similar to Perl • Comments: • # Unix shell-style • /* */ C-style • // C++-style • Output: • echo(“hi there”); • print(“hi there”); • C’s printf M.P. Johnson, DBMS, Stern/NYU, Spring 2006

22. PHP info • PHP does not have both string and number ops like Perl • Number ops treat (number) strings as numbers, regular strings as strings • http://pages.stern.nyu.edu/~mjohnson/dbms/php/test.php • Info function displays lots of server info: • http://pages.stern.nyu.edu/~mjohnson/dbms/php/info.php <? phpinfo(); ?> M.P. Johnson, DBMS, Stern/NYU, Spring 2006

23. New topic: HTML forms • Interactive parts of HTML: forms • Intuition for name: paper form • Fill in textboxes, check boxes or not, etc. • Turn it in (press button) • HTML form • contains arb. # of INPUTs • Submits to somewhere (ACTION) • By GET or POST M.P. Johnson, DBMS, Stern/NYU, Spring 2006

24. Form example <form method="get" action=""> Enter a number: <input type="Text“ name="number"><br> <input type="Submit" name="submit" value="OK"> </form> On clicking Send, we go to the same page, but with “name=99&sumbit=OK” • http://pages.stern.nyu.edu/~mjohnson/dbms/php/input.php M.P. Johnson, DBMS, Stern/NYU, Spring 2006

25. PHP and forms • Obtain param a param, just prefix with $ (for now..) • Goal: display text and button; • On submit, tell user what was entered • http://pages.stern.nyu.edu/~mjohnson/dbms/php/input.php • http://pages.stern.nyu.edu/~mjohnson/dbms/php/inputphp.txt • Improve: also print, say, triple the input… if (isset($val)) print "You entered $val!<br><br>"; M.P. Johnson, DBMS, Stern/NYU, Spring 2006

26. PHP error-handling • Many PHP scripts have lines of the form • some-statement OR die(“something happened”); • What this means: • die exits with error message • PHP uses OR as and AND for bool operators • PHP supports boolean “short-circuiting” • Boolean eval stops as fast as possible • Ftns often return 0/null/false for errors  if some-statement fails then we die M.P. Johnson, DBMS, Stern/NYU, Spring 2006

27. New topic: PHP and databases • PHP 5 has a JDBC-style DB interface • But we’re using PHP 4.3.4… • Special-purpose methods/libraries for MySQL, etc. • Use these to obtain a connection, prepare and execute queries, etc. M.P. Johnson, DBMS, Stern/NYU, Spring 2006

28. PHP & MySQL • Open a connection and open our DB: • Run query: $db = mysql_connect("mysql2.stern.nyu.edu:3306", user, pass); mysql_select_db("test", $db); $result = mysql_query($query,$db); M.P. Johnson, DBMS, Stern/NYU, Spring 2006

29. PHP & MySQL • Extract next row of data from the results: • What this means: myrow is an array that can then be accessed • Other options, see code • In general, to scroll through results, do: $myrow = mysql_fetch_row($result) while ($myrow = mysql_fetch_row($result)) # print row’s data M.P. Johnson, DBMS, Stern/NYU, Spring 2006

30. Limit: PHP webpages that do something • Semi-interesting PHP scripts: • http://pages.stern.nyu.edu/~mjohnson/dbms/php/lookupphp.txt • http://pages.stern.nyu.edu/~mjohnson/dbms/php/lookup.php • Non-trivial but not huge: ~40 lines • Works with two-column (a,b) table • Takes input from user • Returns rows whose a field contains value • If no/empty input, returns all rows • Bad idea in general! M.P. Johnson, DBMS, Stern/NYU, Spring 2006

31. lookup.php • Two possible situations for running script: • Page opened for the first time • User entered parameter and pressed button • Structure of file: • Print input box and button for next search • On button click, parameter is sent to this page’s url • (Try to) read input parameter • Open MySQL connection • Run query • Print results in a table • Disconnect from MySQL M.P. Johnson, DBMS, Stern/NYU, Spring 2006

32. Higher-level structure • As one page: • If we have params, display data based on them • Otherwise, prompt user for params, call self • Could be: • Page 1: prompt for params, call page 2 • Page 2: display data based on params • In e.g.: always display data for convenience M.P. Johnson, DBMS, Stern/NYU, Spring 2006

33. Insert/delete PHP example • Similar to search example • NB: form has two buttons • http://pages.stern.nyu.edu/~mjohnson/dbms/php/update.php • http://pages.stern.nyu.edu/~mjohnson/dbms/php/updatephp.txt M.P. Johnson, DBMS, Stern/NYU, Spring 2006

34. Master-detail Perl/PHP example • Idea: display list of regions; • When region clicked on, display its countries • Mechanism: pass GET param in link, not with a FORM • http://pages.stern.nyu.edu/~mjohnson/dbms/php/cia.php • http://pages.stern.nyu.edu/~mjohnson/dbms/php/ciaphp.txt M.P. Johnson, DBMS, Stern/NYU, Spring 2006

35. That’s all, folks! • Q: Is this enough to get a job coding PHP? • A: Probably not! • But: a couple modified copies of lookup.php and/or cia.php + some HTML glue  fairly interesting site M.P. Johnson, DBMS, Stern/NYU, Spring 2006

36. For next time (in lab)… • Run/read these PHP scripts: • http://pages.stern.nyu.edu/~mjohnson/dbms/php/lookup.php • http://pages.stern.nyu.edu/~mjohnson/dbms/php/lookupphp.txt • http://pages.stern.nyu.edu/~mjohnson/dbms/php/update.php • http://pages.stern.nyu.edu/~mjohnson/dbms/php/updatephp.txt • http://pages.stern.nyu.edu/~mjohnson/dbms/php/cia.php • http://pages.stern.nyu.edu/~mjohnson/dbms/php/ciaphp.txt 2. Go through at least one tutorial on PHP (on web/below) 3. Try posting a hello-web PHP script in your sales account • Various others in dbms/php… M.P. Johnson, DBMS, Stern/NYU, Spring 2006

37. Tutorials on PHP • Some material drawn from the following good tutorials: • http://php.net • PHP introduction and examples: • http://www.scit.wlv.ac.uk/~jphb/sst/php/ • Interactive PHP with database access: • http://www.scit.wlv.ac.uk/~jphb/sst/php/gazdb.html • Longer PHP/MySQL Tutorial from webmonkey: • http://hotwired.lycos.com/webmonkey/99/21/index2a.html • Nice insert/update/delete example from webmonkey: • http://hotwired.lycos.com/webmonkey/99/21/index3a.html • MySQL/Perl/PHP page from U-Wash: • http://www.washington.edu/computing/web/publishing/mysql-script.html M.P. Johnson, DBMS, Stern/NYU, Spring 2006