looking through old posts/emails

am 13.09.2009 23:55:12 von Fred Silsbee

enclosed is a php for oracle that worked on Windows AND Fedora 9

/*
Oracle 11g1 version handle_log_book11g1.php
drop sequence log_id;
create sequence log_id
increment by 1
start with 1;

drop table log_book_id;

create table log_book_id ( log_id number primary key, fdate date, actype varchar2(16), acid varchar(16), nlandings number, nhoursnumber);

insert into log_book_id values (logid.nextval, TO_DATE('08/12/1973','MM/dd/YYYY'),'C150','N5787G',1,1.8);

insert into log_book_id values (logid.nextval, TO_DATE('08/17/1973','MM/dd/YYYY'),'C150','N5787G',3,1.5);

insert into log_book_id values (logid.nextval, TO_DATE('08/26/1973','MM/dd/YYYY'),'C150','N5787G',10,1.8);

insert into log_book_id values (logid.nextval, TO_DATE('09/01/1973','MM/dd/YYYY'),'C150','N5293S',9,1.7);
*/
//construct a global variable for the form profile
$fields = array("fdate", "actype", "acid", "nlandings", "nhours");

global $connection;
session_start();
$db = "(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = landon)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = LMKGDN)
)
)";

$user = $_SESSION['userx'] ;
$password = $_SESSION['passwordx'] ;
$user = "lmkiii";
$password = "xxxxxxxxx" ;

if(!$connection && $user && $password)
{
if ($connection=oci_connect($user, $password, $db))
{
echo "Successfully connected to Oracle.\n";
}
else
{
$err = OCIError();
echo "Oracle Connect Error " . $err['message'];
}
}

if(isset($_POST['delete'])){
deleteRecords();
} elseif(isset($_POST['login'])){
loginOracle();
} elseif (isset($_POST['continue'])){
displayDeleteForm();
} elseif (isset($_POST['new'])){
displayEntryForm();
} elseif (isset($_POST['update'])){
showRecord();
} elseif (isset($_POST['timeInType'])){
timeInType();
} else {

//first time execution
echo <<

HTML;
$_SESSION['userx'] = $_POST[$user] ;
$_SESSION['passwordx'] = $_POST[$password] ;
}

//.......................................................... .................function loginOracle()

function loginOracle(){
global $connection;

$db = "(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = LANDON)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = LMKGDN)
)
)";
$_SESSION['userx'] = $_POST['user'];
$_SESSION['passwordx'] = $_POST['password'];
$connection=oci_pconnect($_POST['user'], $_POST['password'], $db);
if ($connection)
{
echo "Successfully connected to Oracle.\n";
//default action

displayDeleteForm();
}
else
{
$err = OCIError();
echo "Oracle Connect Error " . $err['message'];
}
}

//.......................................................... .................function displayDeleteForm()

function displayDeleteForm(){
//get $fields into the function namespace
global $fields;
global $connection;

/* Create and execute query. */
// Loop through each row, outputting the actype and name
echo << Pilots Logbook entries stored in Oracle Relational Database
under Redhat Fedora 9 Linux

HTML;

//get log_book_id table information
$user = "select * from log_book_id order by log_id";
$result = oci_parse($connection, $user);
$r = oci_execute($result);

//display log_book_id information

while ($newArray = oci_fetch_assoc($result)) {
foreach ($fields as $field){
$fieldx = strtoupper($field);
${$field} = $newArray[$fieldx];
}
$rowID = $newArray['LOG_ID'];

echo <<

HTML;

} // while
echo <<

checked	rowID	fdate	actype	acid	nlandings	nhours

Check to select record	$rowID	$fdate	$actype	$acid	$nlandings	$nhours

HTML;
} //close function

// ............................................................ ..... function timeInType()
//

function timeInType()
{

global $connection;

$query = "select actype,sum(nhours) from log_book_id group by actype";

$statement = oci_parse ($connection, $query);
oci_execute ($statement);
// Loop through each row, outputting the actype and name
echo <<

HTML;

while ($row = oci_fetch_array ($statement, OCI_ASSOC)) {
$actype = $row['ACTYPE'];
$sum = $row['SUM(NHOURS)'];
echo <<

HTML;
} // while

$query = "select sum(nhours) from log_book_id";

$statement = oci_parse ($connection, $query);
oci_execute ($statement);
// Loop through each row, outputting the actype and name

while ($row = oci_fetch_array ($statement, OCI_ASSOC)) {
$sum = $row['SUM(NHOURS)'];
echo <<

HTML;
} // while

echo <<

HTML;
}

// ............................................................ ..... function deleteRecords()
//

function deleteRecords()
{

global $connection;
// Loop through each log_book_id with an enabled checkbox

if (!isset($_POST['checkbox'])){
displayDeleteForm();
return true;
}
//else

foreach($_POST['checkbox'] as $key=>$val){
$toDelete[] = $key;
}
//expand the array for an IN query
$where = implode(',', $toDelete);
$query = "DELETE FROM log_book_id WHERE log_id IN ($where)";
$result = oci_parse($connection, $query);
$r = oci_execute($result);
// Commit transaction
$committed = oci_commit($connection);
// Test whether commit was successful. If error occurred, return error message
if (!$committed) {
$error = oci_error($connection);
echo 'Commit failed. Oracle reports: ' . $error['message'];
}
// Should have one affected row
if ((oci_num_rows($result)== 0) ) {
echo "

There was a problem deleting some of the selected items.

".oci_error().'

';
// exit();
} else {
echo "

The selected items were successfully deleted.

";
}
//direct the user back to the delete form
displayDeleteForm();
} //end function

//.......................................................... .................function insertRecord()

function insertRecord()
{
// global $rowInsert;
global $connection;

global $dFields;
// Retrieve the posted log book information.
global $fields;
// $messages = array();
//add some very crude validation
foreach ($fields as $field){
if (empty($_POST[$field])){
$messages[] = "You must complete the field $field \r\n";
} else {
// $dFields[$field] = mysql_real_escape_string(trim($_POST[$field]));
$dFields[$field] = trim($_POST[$field]);
}
}

if (count($messages)>0) {
displayEntryForm($messages, $dFields);
exit();
}
//end validation

$rowInsert = $_SESSION['rowInsert'] ;
// $rowInsert = $_COOKIE('row_insert');

//get variables into the namespace
extract($dFields);
// Insert the log book information into the log book table

if($rowInsert)
{
$query = "UPDATE log_book_id set fdate = '$fdate', actype='$actype', acid='$acid', nlandings='$nlandings', nhours='$nhours' where log_id='$rowInsert'";
}
else
{
$query = "INSERT INTO log_book_id (log_id , fdate, actype, acid, nlandings, nhours) values (logid.nextval,'$fdate','$actype','$acid','$nlandings','$nho urs')";
}

$rowInsert = 0;
$_SESSION['rowInsert'] = $rowInsert;
$result = oci_parse($connection, $query);
$r = oci_execute($result);
// Display an appropriate message
if ($r) {
echo "

Product successfully inserted!

";
}else {
echo "

There was a problem inserting the log book!

";
$error = oci_error($connection);
echo 'Insert failed. Oracle reports: ' . $error['message'];

}
//direct the user back to the entry form

// Commit transaction
$committed = oci_commit($connection);
// Test whether commit was successful. If error occurred, return error message
if (!$committed) {
$error = oci_error($connection);
echo 'Commit failed. Oracle reports: ' . $error['message'];
}
// Should have one affected row
if ((oci_num_rows($result)== 0) ) {
echo "

There was a problem inserting some of the selected items.

".oci_error().'

';
// exit();
} else {
echo "

The selected items were successfully inserted.

";
}
displayDeleteForm();
}

//.......................................................... .................function showRecord()

function showRecord() {
// show selected record
global $fields;
global $dFields;
global $connection;

global $rowInsert;
// Loop through each log_book_id with an enabled checkbox
if (!isset($_POST['checkbox'])){
displayDeleteForm();
return true;
}
//else

foreach($_POST['checkbox'] as $key=>$val){
$toDelete[] = $key;
}

// errors
//get log_book_id table information
$user = "select * from log_book_id where log_id = $toDelete[0]";

$result = oci_parse($connection, $user);
$r = oci_execute($result);

//display log_book_id information

$newArray = oci_fetch_assoc($result);
foreach ($fields as $field){
$fieldx = strtoupper($field);
${$field} = $newArray[$fieldx];
$dFields[$field] = ${$field};
}
$rowID = $newArray['LOG_ID'];
$rowInsert = $rowID;

$_SESSION['rowInsert'] = $rowInsert;
// setcookie('row_insert',$rowInsert)
//note that we do not rely on the checkbox value as not all browsers submit it
//instead we rely on the name of the checkbox.

// insert the row information in the entry form
global $fields;
//end validation
displayEntryForm();
}

//.......................................................... .................function displayEntryForm()
function displayEntryForm($errorMessages=null, $dFields=null){
if (!empty($errorMessages)){
echo "

".implode('
', $errorMessages) . "

";
}

global $dFields;
//sort out field values
global $fields;
foreach ($fields as $field){
$fieldx = strtoupper($field);
if (isset($dFields[$field])){
${$field} = $dFields[$field];
} else {
${$field} = '';
}
}
// print_r($dFields);
echo <<

Flight Date:

value="$fdate" />

Aircraft Type:

value="$actype" />

Aircraft ID:

value="$acid" />

Number Landings:

value="$nlandings" />

Number Hours:

value="$nhours" />

HTML;
} //end display function
?>

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

A/C Type	Time in Type
$actype	$sum
Total Hours =$sum