Transcript 10 Web Servers PHP MySQL

10. Web Servers, SQL & PHP
2
Motto:
Get your facts first, and then you can
distort them as much as you please.
– Mark Twain
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
5
Client-Server Communication
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.
2.
3.
Download InstantRails from instantrails.rubyforge.org/wiki/wiki.pl?Instant_Rails
Download and install 7-Zip from www.7-zip.org/
Unzip InstantRails-2.0-win.zip with 7-Zip
•
don't use Windows Explorer because of problems with long paths
•
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 Administrator
2. OK to regeneration of configuration files
3. 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.
2.
Test the server in your browser using the localhost/ address
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
Table Student
Table StudentCourse
Table Course
ID
Last Name
First Name
StudentID
CourseID
ID
Number
Term
Title
201
Stelovsky
Jan
201
502
501
313
FS 2008
Theory of PLs
202
Wu
John
202
501
502
414
SS 2007
Web Programming
203
Nice
Guy
202
502
203
502
primary
key
primary
key
primary key
foreign key
into Student
foreign key
into Course
ER Diagram
Student
ID
Last Name
First Name
1
∞
StudentCourse
StudentID
CourseID
1
∞
Course
ID
Number
Term
Title
15
SQL Keywords
•
SELECT
–
•
FROM
–
•
inserts rows
UPDATE
–
•
combines rows from several tables
INSERT
–
•
how to group rows
INNER JOIN
–
•
list of columns to determins how to order rows
suffix ASC or DECS means ascending or descending, ASC is default
GROUP BY
–
•
specifies rows
ORDER BY
–
–
•
specifies table(s) (e.g. within SELECT)
WHERE
–
•
retrieves data from one or more tables
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"))) { //
die ("Can't use database. </body></html>");
}
if (! mysql_select_db ("books", $database)) {
//
die ("Can't use books database. </body></html>");
}
if (! ($result = mysql_query ($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
print ("<tr>");
foreach ($row as $key => $value) {print ("<td>$value</td>");}
print ("</tr>");
}
?>
</table>
</body>
connect MySQL
choose database
query database
in table's rows