Title: Coldfusion and PHP introduction
1Coldfusion and PHP introduction
- University of California, Berkeley
- School of Information
- IS 257 Database Management
2Lecture Outline
- Review
- Databases for Web Applications Overview
- ColdFusion
- DiveShop in ColdFusion
- PHP
- DiveShop in PHP
- More on ORACLE and SQL-Plus
3Lecture Outline
- Review
- Databases for Web Applications Overview
- ColdFusion
- DiveShop in ColdFusion
- PHP
- DiveShop in PHP
- More on ORACLE and SQL-Plus
4Dynamic Web Applications 2
5Server Interfaces
Adapted from John P Ashenfelter, Choosing a
Database for Your Web Site
6Web Application Server Software
- ColdFusion
- PHP
- ASP
- All of the are server-side scripting languages
that embed code in HTML pages
7Lecture Outline
- Review
- Databases for Web Applications Overview
- ColdFusion
- DiveShop in ColdFusion
- PHP
- DiveShop in PHP
- Introduction to ORACLE and SQL-Plus
8Web Application Server Software
- ColdFusion
- PHP
- ASP
- All of the are server-side scripting languages
that embed code in HTML pages
9ColdFusion
- 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 interface
10ColdFusion
- Started as CGI
- Drawback, as noted above, is that the entire
system is run for each cgi invocation - Split into cooperating components
- NT 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 servers
11What ColdFusion is Good for
- Putting up databases onto the Web
- Handling dynamic databases (Frequent updates,
etc) - Making databases searchable and updateable by
users.
12Requirements
- Unix or NT systems
- Install as SuperUser
- Databases must be defined via data source names
(DSNs) by administrator
13Requirements 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.
14Templates
- Assume we have a database named
contents_of_my_shopping_cart.mdb -- single table
called contents... - Create an HTML page (uses extension .cfm), before
ltHEADgt...
15Templates cont.
- ltCFQUERY NAME cart" DATASOURCEcontents_of_my_s
hopping_cart"gt - SELECT FROM contents lt/CFQUERYgt
- ltHEADgt
- ltTITLEgtContents of My Shopping Cartlt/TITLEgt
- lt/HEADgt
- ltBODYgt
- ltH1gtContents of My Shopping Cartlt/H1gt
- ltCFOUTPUT QUERY cart"gt
- ltBgtItemlt/Bgt ltBRgt
- Date_of_item ltBRgt
- Price ltPgt
- lt/CFOUTPUTgt
- lt/BODYgt
- lt/HTMLgt
16Templates 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.75
17CFIF and CFELSE
ltCFOUTPUT QUERY cart"gt Item Item
ltBRgt ltCFIF Picture EQ""gt ltIMG
SRCgeneric_picture.jpg"gt ltBRgt ltCFELSEgt
ltIMG SRC"Picture"gt ltBRgt lt/CFIFgt lt/CFOUTPUTgt
18More Templates
ltCFQUERY DATASOURCE AZ2gt INSERT INTO
Employees(firstname, lastname, phoneext)
VALUES(firstname, lastname, phoneext)
lt/CFQUERYgt ltHTMLgtltHEADgtltTITLEgtEmployee
Addedlt/TITLEgt ltBODYgtltH1gtEmployee
Addedlt/H1gt ltCFOUTPUTgt Employee ltBgtfirstname
lastnamelt/Bgt added. lt/CFOUTPUTgtlt/BODYgt lt/HTMLgt
19CFML 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 cookies
20ColdFusion Diveshop
21Lecture Outline
- Review
- Databases for Web Applications Overview
- ColdFusion
- DiveShop in ColdFusion
- PHP
- DiveShop in PHP
- More on ORACLE and SQL-Plus
22PHP
- 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.
23PHP 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
ltHTMLgtltBODYgt lt?php myvar Hello World
echo myvar ?gt lt/BODYgtlt/HTMLgt
24Combined with MySQL
- DBMS interface appears as a set of functions
ltHTMLgtltBODYgt lt?php db mysql_connect(localhost
, root) mysql_select_db(mydb,db) result
mysql_query(SELECT FROM employees,
db) Printf(First Name s ltbrgt\n,
mysql_result(result, 0 first) Printf(Last
Name s ltbrgt\n, mysql_result(result, 0
last) ?gtlt/BODYgtlt/HTMLgt
25Diveshop PHP
26ASP 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 Server
27ASP Syntax
lt SQL"SELECT last, first FROM employees
ORDER BY last" set conn
server.createobject("ADODB.Connection")
conn.open employee" set peopleconn.execute(
SQL) gt lt do while not people.eof set
resultlinepeople(0) , people(1) ltBRgt
Response.Write(resultline)
people.movenext loopgt lt people.close gt
28Lecture Outline
- Review
- Databases for Web Applications Overview
- ColdFusion
- DiveShop in ColdFusion
- PHP
- DiveShop in PHP
- More on ORACLE and SQL-Plus
29Today
- More on SQL and SQLPlus for data manipulation and
modification
30SELECT
- 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
31CREATE SYNONYM
- CREATE SYNONYM newname FOR oldname
- CREATE SYNONYM BIOLIFE for ray.BIOLIFE
32SELECT Conditions
- equal to a particular value
- gt greater than or equal to a particular value
- gt greater than a particular value
- lt less than or equal to a particular value
- ltgt not equal to a particular value
- LIKE wom_n (Note different wild card)
- IN (opt1, opt2,,optn)
33Aggregate Functions
- COUNT(dataitem)
- AVG(numbercolumn)
- SUM(numbercolumn)
- MAX(numbercolumn)
- MIN(numbercolumn)
- STDDEV(numbercolumn)
- VARIANCE(numbercolumn)
34Numeric Functions
- ABS(n)
- ACOS(n)
- ASIN(n)
- ATAN(n)
- ATAN2(n, m)
- CEIL(n)
- COS(n)
- COSH(n)
- ROUND(n)
- SIGN(n)
- SIN(n)
- SINH(n)
- SQRT(n)
- TAN(n)
- TANH(n)
- TRUNC(n,m)
- EXP(n)
- EXP(n)
- FLOOR(n)
- LN(n)
- LOG(m,n)
- MOD(n)
- POWER(m,n)
35Character Functions returning character values
- CHR(n)
- CONCAT(char1,char2)
- INITCAP(char)
- 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)
- SUBSTRB(char, m, n)
- TRANSLATE(char, from, to)
- UPPER(char)
36Character Function returning numeric values
- ASCII(char)
- INSTR(char1, char2,m, n)
- INSTRB(char1, char2,m, n)
- LENGTH(char)
- LENGTHB(char)
37Date functions
- ADD_MONTHS(dt, n)
- LAST_DAY(d)
- MONTHS_BETWEEN(d1, d2)
- NEW_TIME(d, z1, z2) -- PST, AST, etc.
- NEXT_DAY(d, dayname)
- ROUND(d, fmt) -- century, year etc.
- SYSDATE
- TRUNC(d, fmt) -- century, year, etc.
38Conversion Functions
- CHARTOROWID(char)
- CONVERT(char, dchar, schar)
- HEXTORAW(char)
- RAWTOHEX(raw)
- ROWIDTOCHAR(rowid)
- TO_CHAR (date, fmt)
- TO_DATE(char, fmt)
- TO_NUMBER(char,fmt)
- TO_MULTIBYTE(char)
- TO_SINGLE_BYTE(char)
39Create Table
- CREATE TABLE table-name (attr1 attr-type
CONSTRAINT constr1 PRIMARY KEY, attr2 attr-type
CONSTRAINT constr2 NOT NULL,, attrM attr-type
CONSTRAINT constr3 REFERENCES owner.tablename(attr
name) ON DELETE CASCADE, attrN attr-type
CONSTRAINT constrN CHECK (attrN UPPER(attrN)),
attrO attr-type DEFAULT default_value) - Adds a new table with the specified attributes
(and types) to the database. - NOTE that the CONSTRAINT and name parts are
optional)
40Create Table
- CREATE TABLE table-name (
- attr1 attr-type PRIMARY KEY,
- attr2 attr-type NOT NULL,
- , attrM attr-type REFERENCES
owner.tablename(attrname) ON DELETE CASCADE, - attrN attr-type CHECK (attrN UPPER(attrN)
- attrO attr-type DEFAULT default_value)
- Without CONSTRAINT and name parts
41Types
- VARCHAR2(size)
- NUMBER(p, s)
- LONG -- long char data
- DATE -- from 4712BC to 4714 AD
- RAW(size) -- binary
- LONG RAW -- large binary
- ROWID -- row reference
- CHAR(size) -- fixed length characters
42Alter Table
- ALTER TABLE table-name ADD attr1 attr-type
- ALTER TABLE table-name ADD attr1 CONSTRAINT xxx
constrainvalue - ALTER TABLE table-name MODIFY attr1
optiontochange - ALTER TABLE table-name DROP COLUMN attr1
- Adds, drops or modifies a column in an existing
database table. - Note constrainvalue is any column constraint
like PRIMARY KEY, REFERENCES, etc.
43INSERT
- INSERT INTO table-name (attr1, attr4, attr5,,
attrK) VALUES (val1, val4, val5,, valK) - OR
- INSERT INTO table-name SELECT col1, col2, col3 as
newcol2, col4 FROM xx, yy WHERE where-clause - Adds a new row(s) to a table.
44DELETE
- DELETE FROM table-name WHERE ltwhere clausegt
- Removes rows from a table.
45UPDATE
- UPDATE tablename SET attr1newval, attr2
newval2 WHERE ltwhere clausegt - changes values in existing rows in a table (those
that match the WHERE clause).
46DROP Table
- DROP TABLE tablename
- Removes a table from the database.
47CREATE INDEX
- CREATE UNIQUE INDEX indexname ON tablename
(attr1 ASCDESC, attr2 ASCDESC, ...) - Adds an index on the specified attributes to a
table
48System Information In ORACLE
- Find all of the tables for a user
- SELECT FROM ALL_CATALOG WHERE OWNER userid
- SELECT FROM USER_CATALOG (or CAT)
- Show the attributes and types of data for a
particular table in SQLPlus - DESCRIBE tablename
49Running commands
- Create file with SQL and SQLPlus commands in it.
- Use a plain text editor and NOT a word processor
(or save as text only) - Give the file the extension .sql
- From inside SQLPlus type
- START filename
50Simple formatting in SQLPlus
- SET PAGESIZE 500
- SET LINESIZE 79
- PROMPT stuff to put out to screen
- TTITLE title to put at top of results pages
- COLUMN col_name HEADING New Name
51Outputting results as a file
- SPOOL filename
- Commands
- everything that you see is copied to the file
until - SPOOL STOP
- File will be created with everything between the
SPOOL commands