PHP introduction University of California Berkeley School of Information IS 257 Database Management IS 257 Fall 2010 Lecture Outline Review Databases for Web Applications Overview ColdFusion ID: 568435
Download Presentation The PPT/PDF document "IS 257 – Fall 2010" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
IS 257 – Fall 2010
PHP introduction
University of California, Berkeley
School of Information
IS 257: Database ManagementSlide2
IS 257 – Fall 2010
Lecture Outline
Review
Databases for Web Applications – Overview
ColdFusion
DiveShop in ColdFusion
PHP
DiveShop in PHP
More on MySQL and SQLSlide3
IS 257 – Fall 2010
Lecture Outline
Review
Databases for Web Applications – Overview
ColdFusion
DiveShop in ColdFusion
PHP
DiveShop in PHP
More on MySQL and SQLSlide4
IS 257 – Fall 2010
Dynamic Web Applications 2
Server
database
CGI
DBMS
Web
Server
Internet
Files
Clients
database
databaseSlide5
IS 257 – Fall 2010
Server Interfaces
Adapted from
John P Ashenfelter,
Choosing a Database for Your Web Site
Database
Web Server
Web Application
Server
Web DB
App
HTML
JavaScript
DHTML
CGI
Web Server
API
’
s
ColdFusion PhP Perl
Java ASP
SQL
ODBC
Native DB
interfaces JDBC
Native DB InterfacesSlide6
IS 257 – Fall 2010
Web Application Server Software
ColdFusion
PHP
ASP
All of the are server-side scripting languages that embed code in HTML pagesSlide7
IS 257 – Fall 2010
Lecture Outline
Review
Databases for Web Applications – Overview
ColdFusion
PHP
DiveShop in PHP
Introduction to ORACLE and SQL-PlusSlide8
IS 257 – Fall 2010
Web Application Server Software
ColdFusion
PHP
ASP
All of the are server-side scripting languages that embed code in HTML pagesSlide9
IS 257 – Fall 2010
ColdFusion
Developing WWW sites typically involved a lot of programming to build dynamic sites
e.g. Pages generated as a result of catalog searches, etc.
ColdFusion was designed to permit the construction of dynamic web sites with only minor extensions to HTML through a DBMS interfaceSlide10
IS 257 – Fall 2010
ColdFusion
Started as CGI
Drawback, as previously discussed, is that the entire system is run for each cgi invocation
Split into cooperating components
Windows service -- runs constantly
Server modules for 4 main Web Server API (glue that binds web server to ColdFusion service) {Apache, ISAPI, NSAPI, WSAPI}
Special CGI scripts for other serversSlide11
IS 257 – Fall 2010
What ColdFusion is Good for
Putting up databases onto the Web
Handling dynamic databases (Frequent updates, etc)
Making databases searchable and updateable by users.Slide12
IS 257 – Fall 2010
Requirements
Unix or Windows systems
Install as SuperUser
Databases must be defined via
“
data source names (DSNs) by administratorSlide13
IS 257 – Fall 2010
Requirements and Set Up
Field names should be devoid of spaces. Use the underscore character, like new_items instead of "new items."
Use key fields. Greatly reduces search time.
Check permissions on the individual tables in your database and make sure that they have read-access for the username your Web server uses to log in.
If your fields include large blocks of text, you'll want to include basic HTML coding within the text itself, including boldface, italics, and paragraph markers. Slide14
IS 257 – Fall 2010
Templates
Assume we have a database named contents_of_my_shopping_cart.mdb -- single table called contents...
Create an HTML page (uses extension .cfm), and before <HEAD>...Slide15
IS 257 – Fall 2010
Templates cont.
<CFQUERY NAME=
”
cart" DATASOURCE=
“
contents_of_my_shopping_cart">
SELECT * FROM contents ; </CFQUERY>
<HEAD>
<TITLE>Contents of My Shopping Cart</TITLE>
</HEAD>
<BODY>
<H1>Contents of My Shopping Cart</H1>
<CFOUTPUT QUERY=
”cart"> <B>#Item#</B> <BR> #Date_of_item#
<BR> $#Price# <P></CFOUTPUT></BODY></HTML>Slide16
IS 257 – Fall 2010
Templates cont.
Contents of My Shopping Cart
Bouncy Ball with Psychedelic Markings
12 December 1998
$0.25
Shiny Blue Widget
14 December 1998
$2.53
Large Orange Widget 14 December 1998 $3.75Slide17
IS 257 – Fall 2010
CFIF and CFELSE
<CFOUTPUT QUERY=
”
cart">
Item:
#Item#
<BR>
<CFIF #Picture# EQ"">
<IMG SRC=
“
generic_picture.jpg"> <BR>
<CFELSE>
<IMG SRC="
#Picture#"> <BR></CFIF></CFOUTPUT>Slide18
IS 257 – Fall 2010
More Templates
<CFQUERY DATASOURCE =
“
AZ2
”
>
INSERT INTO Employees(firstname, lastname,
phoneext) VALUES(
‘
#firstname#
’
,
‘
#lastname#’,‘#phoneext#
’) </CFQUERY><HTML><HEAD><TITLE>Employee Added</TITLE><BODY><H1>Employee Added</H1><CFOUTPUT>Employee <B>#firstname# #lastname#</B> added.
</CFOUTPUT></BODY></HTML>Slide19
IS 257 – Fall 2010
CFML ColdFusion Markup Language
Read data from and update data to databases and tables
Create dynamic data-driven pages
Perform conditional processing
Populate forms with live data
Process form submissions
Generate and retrieve email messages
Perform HTTP and FTP function
Perform credit card verification and authorization
Read and write client-side cookiesSlide20
IS 257 – Fall 2010
Lecture Outline
Review
Databases for Web Applications – Overview
ColdFusion
PHP
DiveShop in PHP
More on ORACLE and SQL-PlusSlide21
IS 257 – Fall 2010
PHP
PHP is an Open Source Software project with many programmers working on the code.
Commonly paired with MySQL, another OSS project
Free
Both Windows and Unix support
Estimated that more than 250,000 web sites use PHP as an Apache Module.Slide22
IS 257 – Fall 2010
PHP Syntax
Similar to
“
C
”
or Java (note lines end with
“
;
”
)
Includes most programming structures (Loops, functions, Arrays, etc.)
Loads HTML form variables so that they are addressable by name
<HTML><BODY>
<?php $myvar = “Hello World”
; echo $myvar ;?></BODY></HTML>Slide23
IS 257 – Fall 2010
Combined with MySQL
DBMS interface appears as a set of functions:
<HTML><BODY>
<?
php
mysql_connect
(
“
localhost
”
,
“
usename
”, “
password”);mysql_select_db(“mydb
”);$result = mysql_query(“
SELECT * FROM employees”); while ($r = mysql_fetch_array($
result,MYSQL_ASSOC
Slide24
IS 257 – Fall 2010
Diveshop PHP
Examples on Harbinger/People…Slide25
IS 257 – Fall 2010
ASP – Active Server Pages
Another server-side scripting language
From Microsoft using Visual Basic as the Language model (VBScript), though Javascript (actually MS Jscript) is also supported
Works with Microsoft IIS and gives access to ODBC databases
Most commonly used for Access or MS SQL ServerSlide26
IS 257 – Fall 2010
ASP Syntax
<%
SQL="SELECT last, first FROM employees
ORDER BY last"
set conn = server.createobject("ADODB.Connection")
conn.open
“
employee"
set people=conn.execute(SQL)
%>
<% do while not people.eof
set resultline=people(0) &
“
, “ & people(1) & “<BR>” Response.Write(resultline) people.movenextloop%><% people.close %> Slide27
IS 257 – Fall 2010
Lecture Outline
Review
Databases for Web Applications – Overview
ColdFusion
DiveShop in ColdFusion
PHP
DiveShop in PHP
More on MySQL and SQLSlide28
IS 257 – Fall 2010
Today
More on SQL for data manipulation and modificationSlide29
IS 257 – Fall 2010
SELECT
Syntax:
SELECT
[DISTINCT] attr1, attr2,…, attr3 as label, function(xxx), calculation, attr5, attr6
FROM
relname1 r1, relname2 r2,… rel3 r3
WHERE
condition1 {AND | OR} condition2
ORDER BY
attr1 [DESC], attr3 [DESC] Slide30
IS 257 – Fall 2010
SELECT Conditions
=
equal to a particular value
>=
greater than or equal to a particular value
>
greater than a particular value
<=
less than or equal to a particular value
<>
or
!=
not equal to a particular valueLIKE ‘%wom_n%’
(Note different wild card from Access)opt1 SOUNDS LIKE opt2IN (‘opt1’, ‘opt2
’,…,’optn’)BETWEEN opt1 AND opt2
IS NULL or IS NOT NULLSlide31
IS 257 – Fall 2010
Aggregate (group by) Functions
COUNT(dataitem)
COUNT(DISTINCT expr)
AVG(numbercolumn)
SUM(numbercolumn)
MAX(numbercolumn)
MIN(numbercolumn)
STDDEV(numbercolumn)
VARIANCE(numbercolumn)
and other variants of these…Slide32
IS 257 – Fall 2010
Numeric Functions
ABS(n)
ACOS(n)
ASIN(n)
ATAN(n)
ATAN2(n, m)
CEIL(n)
COS(n)
COSH(n)
CONV(n, f-base,t-base)
COT(n)
ROUND(n)
SIGN(n)SIN(n) SINH(n)SQRT(n)TAN(n) TANH(n)TRUNCATE(n,m)
DEGREES(n)EXP(n)EXP(n)
FLOOR(n)LN(n)LOG(n,b)MOD(n)
PI()POWER(n,p)Slide33
IS 257 – Fall 2010
Character Functions returning character values
CHAR(n,…)
CONCAT(str1,str2,…)
LOWER(char)
LPAD(char, n,char2), RPAD(char, n,char2)
LTRIM(char, n, cset), RTRIM(char, n, cset)
REPLACE(char, srch, repl)
SOUNDEX(char)
SUBSTR(char, m, n)
UPPER(char)Slide34
IS 257 – Fall 2010
Character Function returning numeric values
ASCII(char)
INSTR(char1, char2)
LENGTH(char)
BIT_LENGTH(str)
CHAR_LENGTH(str)
LOCATE(substr,str)
LOCATE(substr,str,pos)
and many other variants.Slide35
IS 257 – Fall 2010
Date functions
ADDDATE(dt, INTERVAL expr unit) or ADDDATE(dt, days)
ADDTIME(dttm, time)
LAST_DAY(dt)
MONTH(dt) – YEAR(dt) – DAY(dt)
MONTHNAME(dt)
NOW()
NEW_TIME(d, z1, z2) -- PST, AST, etc.
NEXT_DAY(d, dayname)
STR_TO_DATE(str,format)
SYSDATE()Slide36
IS 257 – Fall 2010
Assignment 3
Assignment 3 is some additional (and occasionally more complex) searches to be run on the
Diveshop
database
These should be run via the command line (via login to
ischool.berkeley.edu
)
Assignment 3 is posted on the class web site
Walkthrough online version
Due
Thursday
, Oct. 25th