10. web servers, sql & php. 2 motto: get your facts first, and then you can distort them as much...
TRANSCRIPT
3
Concepts
Web server functionality
Microsoft IIS and Apache
PHP– data types, operators, control statements– regular expressions– processing forms– assessing MySQL
Relational databases & SQL
MySQL
4
HTTP• Clients communicate with servers using HTTP
– Hypertext Transfer Protocol
• Client sends a request – get specifies URI of a resource, or search query, or …– post sends a "message" with (larger) data to the server
• Server responds with – header(s)
• MIME (Multipurpose Internet Mail Extensions) – data format: text/plain, image/jpeg
• status code:– 200 success– 404 resource not found– see www.w3.org/Protocols/rfc2616/rfc2616-sec10.html
– empty line– content
6
URI & URL
• URI (Uniform Resource Identifier) identify Internet resources
• URL (Uniform Resource Locator)– URI of a documents
• URL consists of– protocol
• http://, file://, ftp://, …
– hostname• translated to unique IP address • using DNS (Domain Name Server)
– e.g. IP address of www.hawaii.ics.edu is 128.171.10.76
– resource path• usually in a virtual directory (security)• resources may be created dynamically
– get method can append data • separated by ?
7
Web Servers: IIS & Apache
• IIS (Internet Information Services) – proprietary, by Microsoft – included with some versions of Windows. – IIS 7.0 is preinstalled with Windows Vista– W3SVC must be enabled
• World Wide Web Publishing Service
• Apache– the most popular web server– open source– runs under UNIX, Linux, Mac OS X, Windows, etc.– download from httpd.apache.org/download.cgi
8
Server-side Installation(Apache, MySQL, PHP, Ruby, Rails, etc.)
1. Download InstantRails from instantrails.rubyforge.org/wiki/wiki.pl?Instant_Rails2. Download and install 7-Zip from www.7-zip.org/3. Unzip InstantRails-2.0-win.zip with 7-Zip
1. don't use Windows Explorer because of problems with long paths2. Extract files… into a directory with a short path, e.g. C:/server/
9
Server-side Installation (cont.)1. On Vista, right-click InstantRails.exe and select Run As Administrator2. OK to regeneration of configuration files3. When Windows Firewall asks whether to unblock Apache.exe, Unblock it
10
Test Installation
• InstantRails with Apache and MySQL servers is now running, you can stop them any time
1. Test the server in your browser using the localhost/ address 2. Then replace the path/www/index.html file, were is the installation directory, e.g.
C:/server/www/index.html
11
Direct Instalation of Servers
• Separate installation Apache server is described in Apache.ppt
• Separate installation MySQL server is described in MySQL.ppt
12
Databases
• Database: collection of data• DBMS (database management system)
– allows to store, organize, retrieve and modify a database
• Relational database – RDBMS (Relational DBMS)– the most popular today– based on interrelated tables– use SQL (Structured Query Language) to
• execute queries • manipulate data
13
Relational Databases
• Table rows– data
• Table columns– data attributes– primary key
• column(s) with unique (combination of) values
– foreign key(s)• primary key(s) in another table• establish relations within tables
• ER (entity-relationship) diagram shows relationships among tables – a table is shown as a box listing its columns
– a relationships between two tables is shown as an arrow • points from a foreign key to the related table
• label ∞ means that there may be many rows for one value of the foreign key
– one-to-many relationship
14
Tables & ER Diagram Example
ID Last Name First Name
201 Stelovsky Jan
202 Wu John
203 Nice Guy
ID Number Term Title
501 313 FS 2008 Theory of PLs
502 414 SS 2007 Web Programming
StudentID CourseID
201 502
202 501
202 502
203 502
Student
ID
Last Name
First Name
StudentCourse
StudentID
CourseID
Course
ID
Number
Term
Title
∞1
∞1
Table Student Table StudentCourse Table Course
primarykey
primarykey
primary key
foreign keyinto Student
foreign keyinto Course
ER Diagram
15
SQL Keywords• SELECT
– retrieves data from one or more tables
• FROM– specifies table(s) (e.g. within SELECT)
• WHERE– specifies rows
• ORDER BY– list of columns to determins how to order rows– suffix ASC or DECS means ascending or descending, ASC is default
• GROUP BY– how to group rows
• INNER JOIN– combines rows from several tables
• INSERT– inserts rows
• UPDATE– updates rows
• DELETE– deletes rows
16
Basic SQL
• SELECT columns FROM table WHERE conditions– columns is a comma-separated list of columns
• * means all columns• the order of columns in the list determines their order in the result
– conditions that the values in columns must satisfy• possible relational operators: <, >, <=, >=, =, <>• operator LIKE 'pattern' allows pattern matching
– _ matches any single character within pattern– % matches any number (incl. 0) of any characters within pattern
• INSERT INTO table (columns) VALUES (values)– values is a comma-separated list of values that match columns
• UPDATE table SET columns-values WHERE conditions– columns-values is a comma-separated list of column and value pairs
• = separates column from value within a pair
• DELETE FROM table WHERE conditions• WHERE conditions clause is optional in all statements
17
SQL Examples
• SELECT Title FROM Courses
• SELECT Title FROM Courses WHERE Term = 'FS 2008'• SELECT Title FROM Courses
WHERE Term = 'FS 2008', Number = '313'
18
SQL Join• SELECT columns FROM table1 INNER JOIN table2 ON column1 = column2
– cartesian product, function composition in relational algebra– ON clause specifies which rows are joined.– If column1 and column2 have the same name they must be fully qualified – i.e., table1.column = table2.column
• Other joins exist– OUTER JOIN, LEFT JOIN, RIGHT JOIN, …
19
MySQL
• MySQL– pronounced “my sequel”– robust and scalable– multiuser– multithreaded
• concurrent tasks, i.e. fast
– multi-platform • Windows, Mac OS X, UNIX, Linux
– uses SQL– free
20
Other RDBMS
• Apache Derby – free
– uses SQL
– accessible as Java DB
– bundled with Java SE 6
• ADO.NET– Microsoft
– SQL
– object model• SqlCommand object represents a SQL command
• SqlAdapter object connects a SQL RDBMS
• Most web server systems simplify access to RDMBS– PHP, Ruby on Rails, Perl
21
PHP
• Popular server-side scripting language• Open source• Numerous platforms
• UNIX, Linux, Mac, Windows• Supports several databases
• Allows to write programs within XHTML pages– PHP code is enclosed within <?php and ?>– server substitutes PHP code by XHTML before it sends the page to
the client
22
PHP Basics
• Variables– declared using my – start with $
• e.g., my $count = 0;
– can be assigned values of different types
• Statements– end with ;
• Comments– line comments start with // or #– multiline comments are enclosed within /* and */
• Strings are enclosed within " or '– . is the concatenation operator– variables within a string are "interpolated", i.e.,
• print ("count"." = $count"); //prints: count = 0
23
Types
• int, integer• float, double, real• string• bool, boolean
– true, false
• array• object• resource
– external, e.g. database
• NULL
• Conversions– casts
– settype()
24
Arrays
• start at index 0• access via [index]• initialized by list of values in array(list)
– e.g., my $courses = array(313, 415, 413);
• count(array) returns the length of array• assigning a value to the array appends it at the end• associative arrays
– like hash tables, mapping from keys to values, set of (key, value) entries
– elements can be created using => • e.g., my $grades = array("313" => "A", "415" => "A+");
25
Iteration through Arrays
• an array has an internal pointer to the current element – reset(array) sets the pointer to 0– key(array) returns the index of the pointer – next(array) increments the pointer
• foreach ($array as $key => $value) statement– iterates through $array, in each step assigns
• the current index to $key • the value of the current element to $value • $key => is optional
26
Form Processing
• PHP has buit-in "superglobal" associative arrays that hold – user input– the environment variables– the web server attributes
• server-side scripts can access form data sent as part of client's request• $_GET and $_POST
– arrays with info sent to the server by HTTP get and post requests• method = "post"
– appends form data to the browser request – it contains the protocol and the requested resource’s URL
27
PHP Access to MySQL
• mysql_connect() connects to MySQL– parameters: the server’s hostname, a username and a password– returns a database handle or false if the connection fails
• mysql_select_db() selects the database to be queried– returns a true if it was successful, false otherwise
• mysql_query() executes a SQL statement string– not only SELECT query, but also INSERT, UPDATE or DELETE
statement – if SELECT query, returns the result of the query, or false if the query
fails
• mysql_fetch_row() returns the current row in the query's result– the row is an array of the values in subsequent columns– advances the current row
• mysql_error() returns any error strings from the database. • mysql_close() closes the database connection
28
Sample PHP MySQL Code<html><head> <!-- ... -->
</head><body>
<?php
extract ($_POST);
$query = "SELECT author, title, isbn FROM books";
if (! ($database = mysql_connect ("localhost", "user", "password"))) { // connect MySQL
die ("Can't use database. </body></html>");
}
if (! mysql_select_db ("books", $database)) { // choose database
die ("Can't use books database. </body></html>");
}
if (! ($result = mysql_query ($query, $database))) { // query database
die (Can't search; Error: " . mysql_error () . "</body></html>");
}
mysql_close ($database);
?><!– end of PHP script -->
<h3>Search Results</h3>
<table>
<?php // insert all records in result set of the query into the table
for ($i = 0; $row = mysql_fetch_row ($result); $i++) { // display record in table's rows
print ("<tr>");
foreach ($row as $key => $value) {print ("<td>$value</td>");}
print ("</tr>");
}
?>
</table>
</body>