PHP (and MySQL)

1. PHP (and MySQL) University of California, Berkeley School of Information Management and Systems SIMS 257: Database Management

2. Getting started with ORACLE in SQL-- see assignment on website More on SQL and SQLPlus for data manipulation and modification Getting Started with ORACLE. Today

3. 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]

4. CREATE SYNONYM • CREATE SYNONYM newname FOR oldname; • CREATE SYNONYM BIOLIFE for ray.BIOLIFE;

5. 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 (‘opt1’, ‘opt2’,…,’optn’)

6. Aggregate Functions • COUNT(dataitem) • AVG(numbercolumn) • SUM(numbercolumn) • MAX(numbercolumn) • MIN(numbercolumn) • STDDEV(numbercolumn) • VARIANCE(numbercolumn)

7. 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]) Numeric Functions • EXP(n) • EXP(n) • FLOOR(n) • LN(n) • LOG(m,n) • MOD(n) • POWER(m,n)

8. 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) Character Functions returning character values

9. ASCII(char) INSTR(char1, char2[,m, n]) INSTRB(char1, char2[,m, n]) LENGTH(char) LENGTHB(char) Character Function returning numeric values

10. 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. Date functions

11. 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) Conversion Functions

12. Create Table • CREATE TABLE table-name (attr1 attr-type CONSTRAINT constr1 PRIMARY KEY, attr2 attr-type CONSTRAINT constr2 NOT NULL,…, attrM attr-type CONSTRAINT constref REFERENCES owner.tablename(attrname), attrN attr-type CONSTRAINT constrN CHECK (attrN = UPPER(attrN)); • Adds a new table with the specified attributes (and types) to the database.

13. Types • 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

14. Alter Table • ALTER TABLE table-name ADD COLUMN attr1 attr-type; • ALTER TABLE table-name ADD COLUMN attr1 CONSTRAINT xxx constrainvalue; • ALTER TABLE table-name MODIFY COLUMN attr1 optiontochange; • … DROP COLUMN attr1; • Adds a new column to an existing database table.

15. INSERT • INSERT INTO table-name (attr1, attr4, attr5,…, attrK) VALUES (“val1”, val4, val5,…, “valK”); • OR • INSERT INTO table-name SELECT col1, col2, col3as newcol2, col4 FROM xx, yy WHERE where-clause; • Adds a new row(s) to a table.

16. DELETE • DELETE FROM table-name WHERE<where clause>; • Removes rows from a table.

17. UPDATE • UPDATE tablename SET attr1=newval, attr2 = newval2 WHERE<where clause>; • changes values in existing rows in a table (those that match the WHERE clause).

18. DROP Table • DROP TABLE tablename; • Removes a table from the database.

19. CREATE INDEX • CREATE [ UNIQUE ] INDEX indexname ON tablename (attr1 [ASC|DESC][, attr2 [ASC|DESC], ...]) • Adds an index on the specified attributes to a table

20. 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 • DESCRIBE tablename;

21. 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

22. 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” • COLUMN col_name HEADING “New Name”

23. Outputting results as a file… • SPOOL filename • Commands • SPOOL STOP • File will be created with everything between the SPOOL commands

24. 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.

25. PHP Syntax • Similar to ASP • 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>

26. Combined with MySQL • 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 <br>\n”, mysql_result($result, 0 “first”); Printf(“Last Name: %s <br>\n”, mysql_result($result, 0 “last”); ?></BODY></HTML>

27. Examples with Diveshop