Please send questions to
st10@humboldt.edu .
PHP and databases...
* so, a few words about databases to start
* what is a database table?
* you have some set/collection of things you are interested in;
* you express the items in this set as a table:
* where each row is one item in the set,
* where each column is a characteristic that you care about
for items in the set;
* (and a cell, the intersection of a row and a column,
is one of the characteristics of one of the set items...)
* a database table should have NO duplicate rows...
* a database table should have one or more columns that, together,
uniquely identify a row ---> called the primary key
* all the data in a single column is expected to be of the same
type --- OR a cell can be empty, the special value NULL
* if you were going to use a database management system (DBMS) to
create a database table, what would you have to indicate?
* the table name,
* the column names,
* the type of each column,
* and which columns make up the primary key;
* (and maybe more, but the above are the basics);
* consider a table empl (for employee)
* empl_num 4-character string
empl_last_name a character string up to 15 characters long
job_title a character string up to 10 characters long
mgr 4-character string
hiredate date
salary floating pt number < 10000 with 2 fractional places
commission floating pt number < 10000 with 2 fractional places
dept_num 3-character string
* empl_num is the primary key
* WHAT is a DATABASE QUERY?
* it is a statement asking questions about data in a database
table or tables;
* SQL - structured or standard query language - is a very common
language for both querying and manipulating database tables;
* SQL select statement is used to ask questions about data
in database tables.
* SQL select statement is VERY versatile --- but its simplest versions
are also quite straightforward:
select column_name, column_name, column_name ...
from table_name
order by column_name, column_name
select empl_last_name, hiredate
from empl
order by hiredate
... would show the employee last names and hire dates in
order from earliest to latest hire date
select salary
from empl
... would show just the salaries of employees, in who-knows-what
order;
* and, to see ALL the columns in a table, use * after the select:
select *
from table_name
select *
from empl
select *
from empl
order by salary
* a collection of SQL statements in a file is sometimes called a SQL
script;
* so -- how can we create a table in the first place?
* SQL has a create table statement;
* you can see 3 of these in a SQL script set_up_ex_tbls.sql
...and we'll use this script to set up some example tables
for PHP to play with.
* TONIGHT: connecting to Oracle
(and HOPEFULLY connecting to mySQL either Thursday or the next
Tuesday)
* Oracle set-up: (since Peter Johnson HAS set up accounts for
everyone in class...)
* log into redwood,
open .login
add 5 lines
setenv ORACLE_SID student
setenv ORAENV_ASK NO
source /usr/local/bin/coraenv
setenv LD_LIBRARY_PATH $ORACLE_HOME/lib
setenv TWO_TASK student
save .login
run at the redwood> prompt
redwood> source .login
...and FROM NOW ON you should be set.
* NOW, when you are logged onto redwood, you can directly
access your Oracle account by:
redwood> sqlplus
* if you see a prompt SQL> after entering username and
password, you are in sqlplus!
* you can type SQL commands directly here (but they need to end
with a semicolon)
* if you paste in the lines in set_up_ex_tbls.sql (from
my web page under Miscellany), you can create 3 tables,
including an empl table
* and you can EXIT sqlplus using either
SQL> exit
or
SQL> quit
* if you have sqlplus open and change something, a program
outside (like PHP) won't see those changes until they
are committed, either when you exit sqlplus OR you type
the commit command:
SQL> commit;
(or you can undo changes SINCE the previous commit with
rollback: (this does not include table creations and table
deletions --- those do an auto-commit on their own!)
SQL> rollback;
* now to PHP!
* look at try_oracle1.php (on SORREL, currently)
* aside: <input> tag type of password creates a text field
suitable for entering password --- shows * instead of what
is being typed.
(and this is being used in try_oracle1.php)
* here, we are using OCI to connect to Oracle in PHP
(one of several options...)
* for example: (assuming variables $username and $password were
previously set):
// try to connect to Oracle student database on redwood
$db = "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)
(HOST = redwood.humboldt.edu)
(PORT = 1521)) (CONNECT_DATA = (SID = STUDENT)))";
// now try to log on to this database
$conn = OCILogon($username, $password, $db);
* OCILogon is trying to connect to the specified Oracle database;
* OCILogon returns either a connection object, or false
if connection failed...
* that connection object can be used with a SQL statement in
in a string to OCIParse, to "set up" the query for execution:
$query = 'select hiredate, salary, commission '.
'from empl';
$stmt = OCIParse($conn, $query);
* and then you can execute the query with what OCIParse
returns and OCIExecute:
OCIExecute($stmt, OCI_DEFAULT);
* OCIFetch($stmt) (where $stmt is what OCIParse returned)
will return true if there's another row in the result
to fetch. So, you can use this to control a while loop:
while ($succ = OCIFetch($stmt))
* and, OCIResult($stmt, $column_name) will return the
value for column $column_name in the current row of
the query results
$curr_hiredate = OCIResult($stmt, "HIREDATE");
$curr_salary = OCIResult($stmt, "SALARY");
$curr_commission = OCIResult($stmt, "COMMISSION");
* (note that OCIResult returns null if that column
is empty for this row...)
* WHEN you are DONE with $stmt (the thing that OCIParse
returns), "free" it using
OCIFreeStatement($stmt);
* and, VERY IMPORTANT!!!!!!! be sure to CLOSE your connection
to the database before you are done!!!
OCILogoff($conn);
(where $conn is what was returned by OCILogon)
* you should be able to run this example by going to the URL:
http://www.humboldt.edu/~st10/f06cis180php/try_oracle1.php
* WARNING: our current version of Oracle won't send string
columns to PHP ?! (at the present time...)
* next: hopefully, PHP connecting to mySQL