hello, i was wondering if you could help me out!

i am creating a database in oracle8i (sqlplus)

i am trying to allow on one of my pages for the user to select things from the table

i.e if there is a passenger table, i want a combo box that will display the different passengers to allow me to select them to input in my booking table!

any help would be much appreciated as this is really doing my head in!

thank you :o

You mean that you want to create a web page to do this? You can see how to create a dropdown box here

thanks for your reply, but thats not exactly what i meant,

i want the options in the combo box to be taken from my oracle table

so if in my passenger table i had

passengerID | passenger
0001 lee
0002 paul
0003 dave

in my combo box 001, 002, 003 would be displayed!

but if i added passenger 0004 to my passenger table then it would be automatically read from my passenger table and inserted into the combo box!

i can find the mysql/php code to do it, but not the oracle/php way to do it, and i can not figure it out from the mysql code!

thank you, it has puzzled me for a couple of weeks!

Have you seen this page or this page??

i havnt seen those pages no, they are very usefull indeed! I still cant find what i am after tho! :rolleyes:
but thanks for your time and help :mrgreen:

Have you been able to make a connection to the oracle database with php. That is the first hurdle you must pass. If not, try adapting the script on this page to work on your server.

[PHP]<?php
// script from http://us3.php.net/manual/en/function.ocilogon.php
echo "<pre>";
$db = "";

$c1 = ocilogon("scott", "tiger", $db);
$c2 = ocilogon("scott", "tiger", $db);

function create_table($conn)
{
$stmt = ociparse($conn, "create table scott.hallo (test varchar2(64))");
ociexecute($stmt);
echo $conn . " created table\n\n";
}

function drop_table($conn)
{
$stmt = ociparse($conn, "drop table scott.hallo");
ociexecute($stmt);
echo $conn . " dropped table\n\n";
}

function insert_data($conn)
{
$stmt = ociparse($conn, "insert into scott.hallo
values('$conn' || ' ' || to_char(sysdate,'DD-MON-YY HH24:MIS'))");
ociexecute($stmt, OCI_DEFAULT);
echo $conn . " inserted hallo\n\n";
}

function delete_data($conn)
{
$stmt = ociparse($conn, "delete from scott.hallo");
ociexecute($stmt, OCI_DEFAULT);
echo $conn . " deleted hallo\n\n";
}

function commit($conn)
{
ocicommit($conn);
echo $conn . " committed\n\n";
}

function rollback($conn)
{
ocirollback($conn);
echo $conn . " rollback\n\n";
}

function select_data($conn)
{
$stmt = ociparse($conn, "select * from scott.hallo");
ociexecute($stmt, OCI_DEFAULT);
echo $conn."----selecting\n\n";
while (ocifetch($stmt)) {
echo $conn . " [" . ociresult($stmt, "TEST") . "]\n\n";
}
echo $conn . "----done\n\n";
}

create_table($c1);
insert_data($c1); // Insert a row using c1
insert_data($c2); // Insert a row using c2

select_data($c1); // Results of both inserts are returned
select_data($c2);

rollback($c1); // Rollback using c1

select_data($c1); // Both inserts have been rolled back
select_data($c2);

insert_data($c2); // Insert a row using c2
commit($c2); // Commit using c2

select_data($c1); // Result of c2 insert is returned

delete_data($c1); // Delete all rows in table using c1
select_data($c1); // No rows returned
select_data($c2); // No rows returned
commit($c1); // Commit using c1

select_data($c1); // No rows returned
select_data($c2); // No rows returned

drop_table($c1);
echo "</pre>";
?> [/PHP]

I suggest making a new test database for the script to use. You will need to declare the database name here:

[PHP]$db = "";
// add db name to above like so:
$db = "mydatabasename";[/PHP]

The username and pass mentioned in the script are scott and tiger. You can use those values or different ones.

Give this a shot and see what happens, we will go from there

i can connect to oracle with php, i can view my tables, search them, insert things into them, delete things from them all using php.

but on my insert pages i can not fugure out how to take the information out of oracle and put it into a combo box!


<html>
<head><title>insert booking</title>
<link href="style.css" rel="stylesheet" type="text/css">
</head>
<body>
<?
// set-up data to insert
$vars=$HTTP_POST_VARS;
$BookingID =$vars[BookingID];
$PassengerID =$vars[PassengerID];
$FlightID = $vars[FlightID];
$RouteID =$vars[RouteID];
$SeatNumber = $vars[SeatNumber];
// connect to database
putenv("ORACLE_SID=area");
if ($Connection = ocilogon("u2o30", "u2o30"))
{
// assemble query
$Sql = "INSERT INTO booking (BookingID, PassengerID, FlightID, FlightDate, RouteID, Seatnumber, DateOfBooking)";
$Sql .= "VALUES ('$BookingID', '$PassengerID', '$FlightID', sysdate+1, '$RouteID', '$SeatNumber', SYSDATE)";
// parse it
$Statement = ociparse($Connection, $Sql);
if ($Statement)
{ PRINT "Editing data...<br><br>"; }
else
{ PRINT "Parse failure"; }
// execute query
if (ociexecute($Statement))
{ print ("Passenger $PassengerID Booked successfully.<br>\n"); }
else
{ print ("Passenger $PassengerID Did Not Book successfully<br>\n"); }
// log off
ocilogoff($Connection);
}
// handle failure to log on
else
{
var_dump( ocierror($Connection));
} // end of if expression


that is my insertbooking.php

<body>
<u>Book A Flight</u><br>
<form action="insertbooking.php" method="post">

<table width="80%" border="0" cellspacing="2">

<tr>
<td>Booking ID (B000):</td>
<td><input name="BookingID" type="text" size="30" maxlength="30" /></td>
</tr>
<tr>
<td>Passenger ID (0000):</td>
<td><input name="PassengerID" type="text" size="30" maxlength="30" /></td>
</tr>
<tr>
<td>Flight ID (F000):</td>
<td><input name="FlightID" type="text" size="30" maxlength="30" /></td>
</tr>
<tr>
<td>Route ID:</td>
<td><select name="RouteID">
<option value="01">East Midlands to London Heathrow</option>
<option value="02">East Midlands to London Gatwick</option>
<option value="03">East Midlands to Brussels</option>
<option value="04">East Midlands to Dublin</option>
<option value="05">East Midlands to Amsterdam Schiphol</option>
</select>
</td>
</tr>
<tr>
<td>Seat Number:</td>
<td><input name="SeatNumber" type="text" size="30" maxlength="30" /></td>
</tr>
<tr>
<td colspan="2">&nbsp;</td>
</tr>
<tr>
Make sure you enter valid (existing) entries into the Foreign ID columns!
<tr>
<br />
</tr>
</tr>
<tr>
<td colspan="2"><input name="submit" type="submit" value="Book" />
<input type="reset" name="Reset" value="Clear" /></td>
</tr>
</table>
</form>

<br />
Page last modified:
<script language="Javascript">
document.write(document.lastModified);
</script>
</body>

and that is my insertbooking.html

this combo box thing however i just cannot do!
thank you

I'm assuming you want this in insertbooking.html, but you will need to make it a php file as well so you will have to change the name. I'm also assuming that you have only 2 columns in your passenger table, so if you have more you will have to change $row[0] or $row[1] to the appropriate $row[i], also make sure passengertable is changed to the name of the table holding your passenger data. Don't expect this to work straight out as I don't have an oracle db to test on and I can never write code the first time without errors :p , but you should be able to see how to do it with some slight modification.

GL
[PHP]
<?php
// connect to database
putenv("ORACLE_SID=area");
if ($Connection = ocilogon("u2o30", "u2o30"))
{
// assemble query
$Sql = "SELECT * FROM passengertable"; // passengertable should be changed to
//the table that holds your passenger data
// parse it
$Statement = ociparse($Connection, $Sql);
if ($Statement)
{
if (ociexecute($Statement))
{
$stuff=ocifetchstatement ($Statement, &$arr, 0, 20, OCI_FETCHSTATEMENT_BY_ROW);

foreach($arr as $key => $row)
{
$optionlist.= "<option value=\"$row[0]\">$row[0]/$row[1]";
}
}
}
?>

<body>
<u>Book A Flight</u><br>
<form action="insertbooking.php" method="post">

<table width="80%" border="0" cellspacing="2">

<tr>
<td>Booking ID (B000):</td>
<td><input name="BookingID" type="text" size="30" maxlength="30" /></td>
</tr>
<tr>
<td>Passenger ID (0000):</td>
<td>
<select name="RouteID">
<?php echo($optionlist); ?>
</select>
</td>
</tr>
<tr>
<td>Flight ID (F000):</td>
<td><input name="FlightID" type="text" size="30" maxlength="30" /></td>
</tr>
<tr>
<td>Route ID:</td>
<td><select name="RouteID">
<option value="01">East Midlands to London Heathrow</option>
<option value="02">East Midlands to London Gatwick</option>
<option value="03">East Midlands to Brussels</option>
<option value="04">East Midlands to Dublin</option>
<option value="05">East Midlands to Amsterdam Schiphol</option>
</select>
</td>
</tr>
<tr>
<td>Seat Number:</td>
<td><input name="SeatNumber" type="text" size="30" maxlength="30" /></td>
</tr>
<tr>
<td colspan="2">&nbsp;</td>
</tr>
<tr>
Make sure you enter valid (existing) entries into the Foreign ID columns!
<tr>
<br />
</tr>
</tr>
<tr>
<td colspan="2"><input name="submit" type="submit" value="Book" />
<input type="reset" name="Reset" value="Clear" /></td>
</tr>
</table>
</form>

<br />
Page last modified:
<script language="Javascript">
document.write(document.lastModified);
</script>
</body>
[/PHP]

thanks alot mate! its not workin at the moment haha, ill have a little fiddle with it though and see how it goes!

thanks again!!! :mrgreen:

ive got it sort of workin now, but in the combo box it just displays '/' 2 of them! can you see how i am getting this problem?


$optionlist.= "<option value=\"$row[0]\">$row[0]/$row[1]";

i have 9 columns in my table, passengerid which is the one that i need is indeed [0]