Transcript HTML forms with PHP and databases

Oracle Db
• Using SQL*Plus to Interact with Oracle
– User student accounts :S12344567 (Sstudentno)
• Password: ‘student’
– setenv ORACLE_HOME
/usr/local/oracle8i/product/8.1.6
– setenv LD_LIBRARY_PATH
/usr/lib:$ORACLE_HOME/lib
– set path=($path $ORACLE_HOME/bin)
– Connection to cse2030 DB:
$sqlplus S1234567/student@cse2030
SQL> (‘prompt’)
SQLPlus
• CREATE TABLE Emp(EmpID NUMBER,
Name VARCHAR2(30), PRIMARY KEY
(EmpID));
• INSERT INTO Emp VALUES(1, 'Alan
Turing');
• Another way is to use substitution variables.
INSERT INTO Emp VALUES(&id,
'&name');
– You will be prompted to enter a value for id,
type in 2 and hit enter. Next you will be
prompted to enter a value for name, type in
Samual Morse and hit enter
SQLplus cont.
• To repeat the command without re-typing it,
type the slash character ( / ) and then hit
enter.
• Your tables should have a primary key and
Foreign key for referential integrity in oneto-many relationships. EG Authors & Book
• Select * from emp;
– To retrieve all data
Primary keys by Ashley Cambrell
– CREATE TABLE test1(testid NUMBER
CONSTRAINT pk_test1_testid PRIMARY KEY,
testtext VARCHAR(255));
– CREATE SEQUENCE testid_seq INCREMENT BY 1;
– SELECT testid_seq.nextval FROM DUAL;
NEXTVAL
------- 1
1 row selected (0.00 seconds)
– SELECT testid_seq.currval FROM DUAL;
CURRVAL
------1
1 row selected (0.00 seconds)
Primary Key’s cont.
– CREATE TABLE test1(testid NUMBER
CONSTRAINT pk_test1_testid PRIMARY
KEY, testtext VARCHAR(255));
– INSERT INTO test1(testid, testtext)
VALUES(testid_seq.nextval, 'The lazy dog
jumped over the brown fox');
– SELECT * FROM test1;
TESTID TESTTEXT
------ -------------------------------------2 The lazy dog jumped over the brown fox
1 row selected (1.00 second)
Primary Key’s cont.
– INSERT INTO test1(testid, testtext)
VALUES(testid_seq.nextval, 'The lazy fox
jumped over the purple dog');
– SELECT * FROM test1;
TESTID TESTTEXT
------ --------------------------------------2 The lazy dog jumped over the brown fox
3 The lazy fox jumped over the purple dog
2 rows selected (0.00 seconds)
• Do a Commit before logout or data will be
lost !!!!
OCIPLogon
• OCIPLogon -- Connect to an Oracle database and
log on using a persistent connection. Returns a
new session.
– int OCIPLogon ( string username, string password [,
string db])
• CIPLogon() creates a persistent connection to an
Oracle 8 database and logs on. The optional third
parameter can either contain the name of the local
Oracle instance or the name of the entry in
tnsnames.ora to which you want to connect. If the
optional third parameter is not specified, PHP uses
the environment variables ORACLE_SID
(Oracle instance) or TWO_TASK (tnsnames.ora)
to determine which database to connect to.
OCILogOff
• OCILogOff -- Disconnects from Oracle
– int OCILogOff ( int connection)
• PHP oracle function documentation at
http://www.php.net/manual/en/function.ocip
logon.php
OCIParse
• OCIParse -- Parse a query and return a
statement
– int OCIParse ( int conn, string query)
• OCIParse() parses the query using conn. It
returns the statement identity if the query is
valid, FALSE if not. The query can be any
valid SQL statement or PL/SQL block.
• Don't forget that the queries on PHP DON'T use the
semicolon at the end of the SQL command.Putting a
semicollon at the end will give a "Invalid Character"
error.
OCIExecute
• OCIExecute -- Execute a statement
– int OCIExecute ( int statement [, int mode])
OCIExecute() executes a previously parsed
statement. (see OCIParse()). The optional mode
allows you to specify the execution-mode (default
is OCI_COMMIT_ON_SUCCESS). If you don't
want statements to be committed automatically
specify OCI_DEFAULT as your mode.
• Returns TRUE on success, FALSE on failure.
OCIError
• OCIError -- Return the last error of
stmt|conn|global. If no error happened returns
FALSE.
– array OCIError ( [int stmt|conn|global])
• OCIError() returns the last error found. If the
optional stmt|conn|global is not provided, the last
error encountered is returned. If no error is found,
OCIError() returns FALSE. OCIError() returns the
error as an associative array. In this array, code
consists the oracle error code and message the
oracle errorstring.
OCIError eg.
$c = ocilogon("u","p");
if (! $c) var_dump(ocierror()); // we have no connection yet
// so the error is stored global.
$s = ociparse($c,"select * from tubu");
if (! $s) var_dump(ocierror($c)); // parse failed - error is
// stored in connection handle
$err = ociexecute($s);
if ( !$err) var_dump(ocierror($s)); // error code for ociexecute()
// is stored in the statement handle
OCIFetchInto
• OCIFetchInto -- Fetches the next row into
result-array (can be ‘generic’ without using
column names)
– int OCIFetchInto ( int stmt, array & result [, int mode])
• OCIFetchInto() fetches the next row (for
SELECT statements) into the result array.
OCIFetchInto() will overwrite the previous
content of result. By default result will
contain a zero-based array of all columns
that are not NULL. The mode parameter
allows you to change the default behaviour.
OCIFetch
• OCIFetch -- Fetches the next row into
result-buffer
– int OCIFetch ( int statement)
• OCIFetch() fetches the next row (for
SELECT statements) into the internal
result-buffer.
• Used with OCIResult() to return individual
columns.
OCIResult
• OCIResult -- Returns column value for
fetched row
– mixed OCIResult ( int statement, mixed
column)
• OCIResult() will return everything as
strings except for abstract types (ROWIDs,
LOBs and FILEs).
– NOTE: OCIResult() requires the column name
to be written in capitals, so
OCIResult($stmt,"column") won't work, but
OCIResult($stmt,"COLUMN") will.
PHP / Oracle Example
<HTML> <HEAD>
<TITLE>Using PHP to Read a Table in Oracle </TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF">
<CENTER><B>Authors</B> <BR><BR>
<?php
$DB_USERNAME='jceddia';
$DB_PASSWORD='jpasswd';
$DB_DATABASE='cse2030';
$connection = ocilogon($DB_USERNAME,
$DB_PASSWORD, $DB_DATABASE);
$stmt = OCIParse($connection, "SELECT * FROM
author");
OCIExecute($stmt);
PHP / Oracle Example cont.
// Start of table and column headings (ID and Name)
print "<TABLE CELLSPACING=\"0\" CELLPADDING=\"3\" BORDER=\"1\">\n";
print " <TR><TH>AID</TH><TH>Firstname</TH><TH>Surname</TH></TR>\n";
// Loop through results
while(OCIFetch($stmt))
{
print " <TR>\n";
print " <TD>" . OCIResult($stmt, "AID") . "</TD>\n";
print " <TD>" . OCIResult($stmt, "FNAME") . "</TD>\n";
print " <TD>" . OCIResult($stmt, "SNAME") . "</TD>\n";
print " </TR>\n";
}
print "</TABLE>\n";
OCIFreeStatement($stmt);
OCILogoff($connection);
?>
Putting it all together
• It refers to HTML form, php and Oracle
• Two ‘scenarios’
– a. “Input scenario”
• form input is to be added/deleted/lookup/etc to
database eg. User login, add customer details etc
– b. “Output scenario”
• database contents is to be displayed on the form eg.
Once user logs in, display a welcome message with
their name from the database or display a product
list in the selection box etc
Input Scenario
• Define your HTML form to capture the data
you want (to be stored in the db).
• Design your php script to be called from the
‘form submit’ to do initial input validation
(eg. Name not blank). If input OK, connect
to database and insert/amend data.
• EG. See p-6-2.html for the form (calls)
– p-6-2.php This would validate data as per the
lab plus connect to db and issue an insert
statement using the ‘sequence’ for the primary
key and the form variables as the insert
VALUES
Input Scenario eg.
• Form has two variables ‘name’ and ‘occupation’
• Database has table ‘occupation’ with attributes
PersonId,Oname, Ooccupation and personseq as
the sequence to use for PK’s
• Insert statement (after connection):
$sqlstr = “INSERT INTO occupation(Personid, Oname, Ooccupation)
VALUES(personseq.nextval, “. $name.”,”.$occupation.”);”
$stmt = OCIParse($connection, $sqlstr");
OCIExecute($stmt);
// $err = ociexecute($s);
//if ( !$err) var_dump(ocierror($s));
Output Scenario
• Earlier example showed listing the table
contents as a HTML table.
• If you want to display data in a drop down
list box then change the HTML
echo “<select name="cars">”
while(OCIFetch($stmt))
{
echo " <option >”. OCIResult($stmt, “CARMAKE") .”\n";
}
echo "</select>\n";
• For a textfield, the VALUE would be set to OCIResult(…)
etc
Design Considerations
• Database should be specified first according
to functionality
• HTML forms designed to fulfil part of
functionality
• Navigation flow of forms
• Graphic content/CSS added