Coldfusion and PHP introduction University of California Berkeley

Coldfusion and PHP introduction University of California, Berkeley School of Information IS 257: Database Management IS 257 – Fall 2006. 10. 17 SLIDE 1

Lecture Outline • Review – Databases for Web Applications – Overview • Cold. Fusion – Dive. Shop in Cold. Fusion • PHP – Dive. Shop in PHP • More on ORACLE and SQL-Plus IS 257 – Fall 2006. 10. 17 SLIDE 2

Lecture Outline • Review – Databases for Web Applications – Overview • Cold. Fusion – Dive. Shop in Cold. Fusion • PHP – Dive. Shop in PHP • More on ORACLE and SQL-Plus IS 257 – Fall 2006. 10. 17 SLIDE 3

Dynamic Web Applications 2 Web Server Internet Files CGI DBMS Server database Clients IS 257 – Fall 2006. 10. 17 SLIDE 4

Server Interfaces SQL HTML DHTML Web Server Java. Script Native DB Interfaces Database Web DB CGI App ODBC Web Server API’s Cold. Fusion Native DB interfaces JDBC Ph. P Perl Web Application Server Adapted from John P Ashenfelter, Choosing a Database for Your Web Site IS 257 – Fall 2006 Java ASP 2006. 10. 17 SLIDE 5

Web Application Server Software • • Cold. Fusion PHP ASP All of the are server-side scripting languages that embed code in HTML pages IS 257 – Fall 2006. 10. 17 SLIDE 6

Lecture Outline • Review – Databases for Web Applications – Overview • Cold. Fusion – Dive. Shop in Cold. Fusion • PHP – Dive. Shop in PHP • Introduction to ORACLE and SQL-Plus IS 257 – Fall 2006. 10. 17 SLIDE 7

Web Application Server Software • • Cold. Fusion PHP ASP All of the are server-side scripting languages that embed code in HTML pages IS 257 – Fall 2006. 10. 17 SLIDE 8

Cold. Fusion • Developing WWW sites typically involved a lot of programming to build dynamic sites – e. g. Pages generated as a result of catalog searches, etc. • Cold. Fusion was designed to permit the construction of dynamic web sites with only minor extensions to HTML through a DBMS interface IS 257 – Fall 2006. 10. 17 SLIDE 9

Cold. Fusion • 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 Cold. Fusion service) {Apache, ISAPI, NSAPI, WSAPI} – Special CGI scripts for other servers IS 257 – Fall 2006. 10. 17 SLIDE 10

What Cold. Fusion is Good for • Putting up databases onto the Web • Handling dynamic databases (Frequent updates, etc) • Making databases searchable and updateable by users. IS 257 – Fall 2006. 10. 17 SLIDE 11

Requirements • Unix or NT systems • Install as Super. User • Databases must be defined via “data source names (DSNs) by administrator IS 257 – Fall 2006. 10. 17 SLIDE 12

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. IS 257 – Fall 2006. 10. 17 SLIDE 13

Templates • Assume we have a database named contents_of_my_shopping_cart. mdb -single table called contents. . . • Create an HTML page (uses extension. cfm), before <HEAD>. . . IS 257 – Fall 2006. 10. 17 SLIDE 14

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> <H 1>Contents of My Shopping Cart</H 1> <CFOUTPUT QUERY= ”cart"> <B>#Item#</B> <BR> #Date_of_item# <BR> $#Price# <P> </CFOUTPUT> </BODY> </HTML> IS 257 – Fall 2006. 10. 17 SLIDE 15

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. 75 IS 257 – Fall 2006. 10. 17 SLIDE 16

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> IS 257 – Fall 2006. 10. 17 SLIDE 17

More Templates <CFQUERY DATASOURCE = “AZ 2”> INSERT INTO Employees(firstname, lastname, phoneext) VALUES(‘#firstname#’, ‘#lastname#’, ‘#phoneext#’) </CFQUERY> <HTML><HEAD><TITLE>Employee Added</TITLE> <BODY><H 1>Employee Added</H 1> <CFOUTPUT> Employee <B>#firstname# #lastname#</B> added. </CFOUTPUT></BODY> </HTML> IS 257 – Fall 2006. 10. 17 SLIDE 18

CFML Cold. Fusion 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 IS 257 – Fall 2006. 10. 17 SLIDE 19

Cold. Fusion Diveshop • Examples from Fusion IS 257 – Fall 2006. 10. 17 SLIDE 20

Lecture Outline • Review – Databases for Web Applications – Overview • Cold. Fusion – Dive. Shop in Cold. Fusion • PHP – Dive. Shop in PHP • More on ORACLE and SQL-Plus IS 257 – Fall 2006. 10. 17 SLIDE 21

PHP • PHP is an Open Source Software project with many programmers working on the code. – Commonly paired with My. SQL, another OSS project – Free – Both Windows and Unix support • Estimated that more than 250, 000 web sites use PHP as an Apache Module. IS 257 – Fall 2006. 10. 17 SLIDE 22

PHP Syntax • Similar to “C” or Java (note lines end with “; ”) <HTML><BODY> <? php $myvar = “Hello World”; echo $myvar ; ? > </BODY></HTML> • Includes most programming structures (Loops, functions, Arrays, etc. ) • Loads HTML form variables so that they are addressable by name IS 257 – Fall 2006. 10. 17 SLIDE 23

Combined with My. SQL • DBMS interface appears as a set of functions: <HTML><BODY> <? php $db = mysql_connect(“localhost”, “root”); mysql_select_db(“mydb”, $db); $result = mysql_query(“SELECT * FROM employees”, $db); Printf(“First Name: %s n”, mysql_result($result, 0 “first”); Printf(“Last Name: %s n”, mysql_result($result, 0 “last”); ? ></BODY></HTML> IS 257 – Fall 2006. 10. 17 SLIDE 24

Diveshop PHP • Examples on Dream… IS 257 – Fall 2006. 10. 17 SLIDE 25

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 Server IS 257 – Fall 2006. 10. 17 SLIDE 26

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. movenext loop%> <% people. close %> IS 257 – Fall 2006. 10. 17 SLIDE 27

Lecture Outline • Review – Databases for Web Applications – Overview • Cold. Fusion – Dive. Shop in Cold. Fusion • PHP – Dive. Shop in PHP • More on ORACLE and SQL-Plus IS 257 – Fall 2006. 10. 17 SLIDE 28

Today • More on SQL and SQLPlus for data manipulation and modification IS 257 – Fall 2006. 10. 17 SLIDE 29

SELECT • Syntax: – SELECT [DISTINCT] attr 1, attr 2, …, attr 3 as label, function(xxx), calculation, attr 5, attr 6 FROM relname 1 r 1, relname 2 r 2, … rel 3 r 3 WHERE condition 1 {AND | OR} condition 2 ORDER BY attr 1 [DESC], attr 3 [DESC] IS 257 – Fall 2006. 10. 17 SLIDE 30

CREATE SYNONYM • CREATE SYNONYM newname FOR oldname; • CREATE SYNONYM BIOLIFE for ray. BIOLIFE; IS 257 – Fall 2006. 10. 17 SLIDE 31

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 <> not equal to a particular value LIKE ‘%wom_n%’ (Note different wild card) IN (‘opt 1’, ‘opt 2’, …, ’optn’) IS 257 – Fall 2006. 10. 17 SLIDE 32

Aggregate Functions • • COUNT(dataitem) AVG(numbercolumn) SUM(numbercolumn) MAX(numbercolumn) MIN(numbercolumn) STDDEV(numbercolumn) VARIANCE(numbercolumn) IS 257 – Fall 2006. 10. 17 SLIDE 33

Numeric Functions • • ABS(n) ACOS(n) ASIN(n) ATAN 2(n, m) CEIL(n) COSH(n) IS 257 – Fall 2006 • • EXP(n) FLOOR(n) LN(n) LOG(m, n) MOD(n) POWER(m, n) • • ROUND(n) SIGN(n) SINH(n) SQRT(n) TANH(n) TRUNC(n[, m]) 2006. 10. 17 SLIDE 34

Character Functions returning character values • • • CHR(n) CONCAT(char 1, char 2) INITCAP(char) LOWER(char) LPAD(char, n, char 2), RPAD(char, n, char 2) • LTRIM(char, n, cset), RTRIM(char, n, cset) IS 257 – Fall 2006 • REPLACE(char, srch, repl) • SOUNDEX(char) • SUBSTR(char, m, n) • SUBSTRB(char, m, n) • TRANSLATE(char, from, to) • UPPER(char) 2006. 10. 17 SLIDE 35

Character Function returning numeric values • ASCII(char) • INSTR(char 1, char 2[, m, n]) • INSTRB(char 1, char 2[, m, n]) • LENGTH(char) • LENGTHB(char) IS 257 – Fall 2006. 10. 17 SLIDE 36

Date functions • • ADD_MONTHS(dt, n) LAST_DAY(d) MONTHS_BETWEEN(d 1, d 2) NEW_TIME(d, z 1, z 2) -- PST, AST, etc. NEXT_DAY(d, dayname) ROUND(d, fmt) -- century, year etc. SYSDATE TRUNC(d, fmt) -- century, year, etc. IS 257 – Fall 2006. 10. 17 SLIDE 37

Conversion Functions • CHARTOROWID(char) • CONVERT(char, dchar, • TO_NUMBER(char, fmt schar) ) • HEXTORAW(char) • TO_MULTIBYTE(char) • RAWTOHEX(raw) • TO_SINGLE_BYTE(ch • ROWIDTOCHAR(rowid) ar) • TO_CHAR (date, fmt) • TO_DATE(char, fmt) IS 257 – Fall 2006. 10. 17 SLIDE 38

Create Table • CREATE TABLE table-name (attr 1 attr-type CONSTRAINT constr 1 PRIMARY KEY, attr 2 attr -type CONSTRAINT constr 2 NOT NULL, …, attr. M attr-type CONSTRAINT constr 3 REFERENCES owner. tablename(attrname) ON DELETE CASCADE, attr. N attr-type CONSTRAINT constr. N CHECK (attr. N = UPPER(attr. N)), attr. O 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) IS 257 – Fall 2006. 10. 17 SLIDE 39

Create Table • CREATE TABLE table-name ( attr 1 attr-type PRIMARY KEY, attr 2 attr-type NOT NULL, …, attr. M attr-type REFERENCES owner. tablename(attrname) ON DELETE CASCADE, attr. N attr-type CHECK (attr. N = UPPER(attr. N) attr. O attr-type DEFAULT default_value); – Without “CONSTRAINT” and name parts IS 257 – Fall 2006. 10. 17 SLIDE 40

Types • • VARCHAR 2(size) NUMBER(p, s) LONG -- long char data DATE -- from 4712 BC to 4714 AD RAW(size) -- binary LONG RAW -- large binary ROWID -- row reference CHAR(size) -- fixed length characters IS 257 – Fall 2006. 10. 17 SLIDE 41

Alter Table • ALTER TABLE table-name ADD attr 1 attrtype; • ALTER TABLE table-name ADD attr 1 CONSTRAINT xxx constrainvalue; • ALTER TABLE table-name MODIFY attr 1 optiontochange; • ALTER TABLE table-name DROP COLUMN attr 1; • Adds, drops or modifies a column in an existing database table. – Note: constrainvalue is any column constraint like ‘PRIMARY KEY’, REFERENCES, etc. IS 257 – Fall 2006. 10. 17 SLIDE 42

INSERT • INSERT INTO table-name (attr 1, attr 4, attr 5, …, attr. K) VALUES (“val 1”, val 4, val 5, …, “val. K”); • OR • INSERT INTO table-name SELECT col 1, col 2, col 3 as newcol 2, col 4 FROM xx, yy WHERE where-clause; • Adds a new row(s) to a table. IS 257 – Fall 2006. 10. 17 SLIDE 43

DELETE • DELETE FROM table-name WHERE <where clause>; • Removes rows from a table. IS 257 – Fall 2006. 10. 17 SLIDE 44

UPDATE • UPDATE tablename SET attr 1=newval, attr 2 = newval 2 WHERE <where clause>; • changes values in existing rows in a table (those that match the WHERE clause). IS 257 – Fall 2006. 10. 17 SLIDE 45

DROP Table • DROP TABLE tablename; • Removes a table from the database. IS 257 – Fall 2006. 10. 17 SLIDE 46

CREATE INDEX • CREATE [ UNIQUE ] INDEX indexname ON tablename (attr 1 [ASC|DESC][, attr 2 [ASC|DESC], . . . ]) • Adds an index on the specified attributes to a table IS 257 – Fall 2006. 10. 17 SLIDE 47

System 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; IS 257 – Fall 2006. 10. 17 SLIDE 48

Running 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 IS 257 – Fall 2006. 10. 17 SLIDE 49

Simple 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” IS 257 – Fall 2006. 10. 17 SLIDE 50

Outputting 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 IS 257 – Fall 2006. 10. 17 SLIDE 51