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!
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
// 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:MI:SS'))");
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>";
?>I suggest making a new test database for the script to use. You will need to declare the database name here:
$db = "";
// add db name to above like so:
$db = "mydatabasename";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"> </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, 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
// 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"> </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>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]
Change this:
$stuff=ocifetchstatement ($Statement, &$arr, 0, 20, OCI_FETCHSTATEMENT_BY_ROW);
foreach($arr as $key => $row)
{
$optionlist.= "<option value=\"$row[0]\">$row[0]/$row[1]";
}to look like this:
$stuff=ocifetchstatement ($Statement, &$arr, 0, 20, OCI_FETCHSTATEMENT_BY_ROW);
echo('<pre>');
print_r($arr);
echo('</pre>');
foreach($arr as $key => $row)
{
$optionlist.= "<option value=\"$row[0]\">$row[0]/$row[1]";
}It will allow you to see what $arr looks like.
you are indeed a star! that has mede it much clearer and easier to understand! my page now appears like this!
Array
(
[0] => Array
(
[PASSENGERID] => 0001
[SURNAME] => Jones
[FIRSTNAME] => Lee
[ADDRESS1] => 29 Birches Head Rd
[ADDRESS2] => Birches Head
[ADDRESS3] => Stoke
[TELEPHONE] => 65656576
[NEXTOFKIN] => Carole
[KINTELEPHONE] => 767896767
)
[1] => Array
(
[PASSENGERID] => 0003
[SURNAME] => Facey
[FIRSTNAME] => Phil
[ADDRESS1] => vfvv
[ADDRESS2] => fvfvf
[ADDRESS3] => vfdvfdv
[TELEPHONE] => 234343
[NEXTOFKIN] => ewfeewf
[KINTELEPHONE] => 34232434
)
)
Book A Flight
Booking ID (B000):
Passenger ID (0000): //
Flight ID (F000):
Route ID: East Midlands to London Heathrow East Midlands to London Gatwick East Midlands to Brussels East Midlands to Dublin East Midlands to Amsterdam Schiphol
Seat Number:
Make sure you enter valid (existing) entries into the Foreign ID columns!
Page last modified: 04/25/2005 12:46:45
but the i can still not see how to select the passengerid into the combo box! sorry too be a pain! thanks again for your help! it is much appreciated!!
$stuff=ocifetchstatement ($Statement, &$arr, 0, 20, OCI_FETCHSTATEMENT_BY_ROW);
foreach($arr as $key => $row)
{
$optionlist.= "<option value=\"$row['PASSENGERID']\">$row['PASSENGERID']/$row['FIRSTNAME'] $row['SURNAME']";
}That should do the trick.
still the same problem! its not readin this bit properly
$optionlist.= "<option value=\"$row\">$row/$row $row";
i tried takin the ' out as it at first was givin me this error
Parse error: parse error, expecting `T_STRING' or `T_VARIABLE' or `T_NUM_STRING' in /var/www/htdocs/prin/u2o30/airports/verydifficult.php on line 26
but that didnt work either!
im sorry to hassle you!
i do appreciate this! thanks!
$stuff=ocifetchstatement ($Statement, &$arr, 0, 20, OCI_FETCHSTATEMENT_BY_ROW);
foreach($arr as $key => $row)
{
$optionlist.= "<option value=\"{$row['PASSENGERID']}\">{$row['PASSENGERID']}/{$row['FIRSTNAME']} {$row['SURNAME']}";
}Silly me. Try this instead.
$stuff=ocifetchstatement ($Statement, &$arr, 0, 20, OCI_FETCHSTATEMENT_BY_ROW);
foreach($arr as $key => $row)
{
$optionlist.= "<option value=\"{$row}\">{$row}";
}
:cheesy:
ive changed it to that as that is all i need!
Thank you so so so so so much!!!!!!!!!!!!!!!!
thanks!
you are a star!!!!
:cheesy:
Me again with one further question! if i may!
how would i go about finding,a most frequent flier, or most popular flight!
i obviously have to use my bookings table as this is where all of this information is stored, but how do i go about doing a count, where it counts the occurrance of passenger in the bookings table, and then displays how many times each occur?
$sql = "select count(passengerid) from Booking";
i know that that is how to get a count of the total passengers in the table, but how do i get a count of each individual one?
thank you again :cheesy:
$sql = "SELECT passengerid COUNT(*) FROM Booking GROUP BY passengerid";
i have tried that but it gives me these errors
Warning: ociexecute(): OCIStmtExecute: ORA-00923: FROM keyword not found where expected in /var/www/htdocs/prin/u2o30/airports/passengercount.php on line 20
All Bookings in the Database:
Warning: ocifetch(): OCIFetch: ORA-24374: define not done before fetch or execute and fetch in /var/www/htdocs/prin/u2o30/airports/passengercount.php on line 36
thank you
done it hahahahaha
$sql = "SELECT passengerid, COUNT(*) FROM Booking GROUP BY passengerid";
i just missed a comma out! :mrgreen:
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.