// Create sequence executePlainSQL("create sequence cid_sequence \r\n\t\t\t\t\t\tstart with 0 \r\n\t\t\t\t\t\tincrement by 1 \r\n\t\t\t\t\t\tminvalue 0\r\n\t\t\t\t\t\tmaxvalue 100000"); OCICommit($db_conn); } else { if (array_key_exists('insertsubmit', $_POST)) { //Getting the values from user and insert data into the table $tuple = array(":bind1" => $_POST['cid'], ":bind2" => $_POST['email'], ":bind3" => $_POST['password'], ":bind4" => $_POST['cname'], ":bind5" => $_POST['passport_country'], ":bind6" => $_POST['passport_num'], ":bind7" => $_POST['phone#'], ":bind8" => $_POST['address']); $alltuples = array($tuple); executeBoundSQL("insert into Customer values (\r\n\t\t\t\t\t\t\tcid_sequence.nextval, \t\r\n\t\t\t\t\t\t\t:bind2, \r\n\t\t\t\t\t\t\t:bind3, \r\n\t\t\t\t\t\t\t:bind4, \r\n\t\t\t\t\t\t\t:bind5, \r\n\t\t\t\t\t\t\t:bind6, \r\n\t\t\t\t\t\t\t:bind7, \r\n\t\t\t\t\t\t\t:bind8)", $alltuples); OCICommit($db_conn); } else { if (array_key_exists('updatesubmit', $_POST)) { // Update tuple using data from user $tuple = array(":bind1" => $_POST['oldName'], ":bind2" => $_POST['newName']); $alltuples = array($tuple); executeBoundSQL("update tab1 set name=:bind2 where name=:bind1", $alltuples); OCICommit($db_conn); } else { if (array_key_exists('dostuff', $_POST)) { } } } } if ($_POST && $success) { //POST-REDIRECT-GET -- See http://en.wikipedia.org/wiki/Post/Redirect/Get header("location: oracle-test.php"); } else { // Select data... $result = executePlainSQL("select * from Customer"); printResult($result); }
$query = "select {$minmax}(avg(wage)) from employee group by jobt"; $result = executePlainSQL($query); printNested($result, $minmax); } else { if (array_key_exists('dividesubmit', $_POST)) { $query = "select e.sin, e.name, e.phone from employee e where not exists (select ob.sin from OperatedBy ob where ob.sin = e.sin)"; #$query = "select e.sin, e.name from employee e where not exists ((select v.vid from vehicles v) except (select ob.vid from OperatedBy ob where ob.sid = e.sin))"; $result = executePlainSQL($query); printDivide($result); } else { if (array_key_exists('vinsertsubmit', $_POST)) { //Getting the values from user and insert data into the table $tuple = array(":bind1" => $_POST['vid'], ":bind2" => $_POST['capacity'], ":bind3" => $_POST['vmode'], ":bind4" => $_POST['cost'], ":bind5" => $_POST['model'], ":bind6" => $_POST['age']); $alltuples = array($tuple); // executeBoundSQL("insert into tab1 values (:bind1, :bind2)", $alltuples); executeBoundSQL("insert into vehicles values (:bind1, :bind2, :bind3, :bind4, :bind5, :bind6 )", $alltuples); OCICommit($db_conn); } else { if (array_key_exists('vupdatesubmit', $_POST)) { if (!empty($_POST['vid'])) { $vid = $_POST['vid']; $capacity = $_POST['capacity']; $vmode = $_POST['vmode']; $cost = $_POST['cost']; $model = $_POST['model']; $age = $_POST['age']; $query = "update vehicles set "; $query .= !empty($_POST['capacity']) ? "capacity={$capacity}," : ""; $query .= !empty($_POST['vmode']) ? "vmode='{$vmode}'," : ""; $query .= !empty($_POST['cost']) ? "cost='{$cost}'," : ""; $query .= !empty($_POST['model']) ? "model='{$model}'," : "";
// check if email is taken $q = "select * from Customer where email = '" . $_POST['email'] . "'"; $numrows = countRows($db_conn, $q); if ($numrows == 1) { $success = 0; ?> <script type="text/javascript"> alert("Error: Email address already registered. Please try a different email address"); </script> <?php } else { $success = 1; //Getting the values from user and insert data into the table $tuple = array(":bind1" => $_POST['cid'], ":bind2" => $_POST['email'], ":bind3" => $_POST['password'], ":bind4" => $_POST['cname'], ":bind5" => $_POST['passport_country'], ":bind6" => $_POST['passport_num'], ":bind7" => $_POST['phone#'], ":bind8" => $_POST['address'], ":bind9" => 0); $alltuples = array($tuple); executeBoundSQL("insert into Customer values (\r\n\t\t\t\t\t\t\t\tcid_sequence.nextval, \t\r\n\t\t\t\t\t\t\t\t:bind2,\r\n\t\t\t\t\t\t\t\t:bind3,\r\n\t\t\t\t\t\t\t\t:bind4,\r\n\t\t\t\t\t\t\t\t:bind5,\r\n\t\t\t\t\t\t\t\t:bind6,\r\n\t\t\t\t\t\t\t\t:bind7,\r\n\t\t\t\t\t\t\t\t:bind8,\r\n\t\t\t\t\t\t\t\t:bind9\r\n\t\t\t\t\t\t\t\t)", $alltuples); OCICommit($db_conn); } } } if ($_POST && $success) { ?> <script type="text/javascript"> alert("Successfully registered. Click OK to be redirected to the login page."); location = "login.php"; </script> <?php } //Commit to save changes... OCILogoff($db_conn); } else {
} else { if (array_key_exists('updatesubmit', $_POST)) { // Update tuple using data from user $tuple = array(":bind1" => $_POST['oldName'], ":bind2" => $_POST['newName']); $alltuples = array($tuple); executeBoundSQL("update tab1 set name=:bind2 where name=:bind1", $alltuples); OCICommit($db_conn); } else { if (array_key_exists('dostuff', $_POST)) { // Insert data into table... executePlainSQL("insert into tab1 values (10, 'Frank')"); // Inserting data into table using bound variables $list1 = array(":bind1" => 6, ":bind2" => "All"); $list2 = array(":bind1" => 7, ":bind2" => "John"); $allrows = array($list1, $list2); executeBoundSQL("insert into tab1 values (:bind1, :bind2)", $allrows); //the function takes a list of lists // Update data... //executePlainSQL("update tab1 set nid=10 where nid=2"); // Delete data... //executePlainSQL("delete from tab1 where nid=1"); OCICommit($db_conn); } } } } if ($_POST && $success) { //POST-REDIRECT-GET -- See http://en.wikipedia.org/wiki/Post/Redirect/Get header("location: oracle-test.php"); } else { // Select data...
echo "<br>Got data from table customers:<br>"; echo "<table>"; echo "<tr><th>User</th>" . " " . "<th>Address </th>" . " " . "<th>Password </th>" . " " . "<th>Phone </th></tr>"; while ($row = OCI_Fetch_Array($result, OCI_BOTH)) { echo "<tr><td>" . " " . $row["USERNAME"] . " </td><td>" . " " . $row["ADDRESS"] . " </td><td>" . " " . $row["PASSWORD"] . "</td><td>" . " " . $row["PNUMBER"] . "</td></tr>"; //or just use "echo $row[0]" } echo "</table>"; } // Connect Oracle... if ($db_conn) { if (array_key_exists('register', $_POST)) { //Getting the values from user and insert data into the table $tuple = array(":bind3" => $_POST['username'], ":bind2" => $_POST['address'], ":bind4" => $_POST['password'], ":bind1" => $_POST['pnumber']); $alltuples = array($tuple); executeBoundSQL("insert into customers values (:bind1, :bind2, :bind3, :bind4, 0)", $alltuples); OCICommit($db_conn); } if ($_POST && $success) { //POST-REDIRECT-GET -- See http://en.wikipedia.org/wiki/Post/Redirect/Get header("location: register.php"); } else { // Select data... $result = executePlainSQL("select * from customers"); } //Commit to save changes... OCILogoff($db_conn); } else { $e = OCI_Error(); // For OCILogon errors pass no handle echo "<div class='alert alert-danger' role='alert'>";