<!DOCTYPE html> <html lang="en" xmlns="http://www.w3.org/1999/xhtml"> <!-- Class examples from CS 328 - Week 12 Lecture 1 requires: * hum_conn_no_login.php, assumed to be in the same directory * stored function get_manager, (from the Week 5 Lab Exercise), assumed to exist in your Oracle student database Postback PHP to request info for a specific employee, that EITHER: * builds a dynamic select/drop-down form widget of current employees, OR * uses a BIND VARIABLE to more-safely build a dynamic select statement requesting info for a selected employee, including the name of their manager by: Sharon Tuttle last modified: 2026-04-13 you can run this using the URL: https:/nrs-projects.humboldt.edu/~st10/s26cs328/328lect12-1/328lect12-1-funct.php note: validated by: * getting an .xhtml version of the executed result with the FORM by copying the resulting source from a browser's "show source" for this document with the form and pasting it into a file ending with -1.xhtml on nrs-projects * getting an .xhtml version of the executed result with the FORM's RESPONSE by copying the resulting source from a browser's "show source" for this document with the form's response and pasting it into a file ending with -2.xhtml on nrs-projects * validating those two .xhtml versions using one of our usual validator links --> <head> <title> CS 328 Week 12 Lecture 1 - adding calling a stored function </title> <meta charset="utf-8" /> <?php // enable error reporting (for now, at least) for this // particular PHP document ini_set('display_errors', 1); error_reporting(E_ALL); // get the function hum_conn_no_login require_once("hum_conn_no_login.php"); ?> <link href="https://nrs-projects.humboldt.edu/~st10/styles/normalize.css" type="text/css" rel="stylesheet" /> </head> <body> <h1> Employee Info Request </h1> <?php // when I first reach this PHP, generate a form // to select an employee if ($_SERVER["REQUEST_METHOD"] == "GET") { ?> <form method="post" action="<?= htmlentities($_SERVER["PHP_SELF"], ENT_QUOTES) ?>"> <label for="empl_choice"> Select an employee: </label> <select name="empl_choice" id="empl_choice"> <?php $conn = hum_conn_no_login(); // remember, if REACH here, connection must have // succeeded // query for current employees $empl_query_str = "select empl_num, empl_last_name from empl order by empl_last_name"; $empl_stmt = oci_parse($conn, $empl_query_str); oci_execute($empl_stmt, OCI_DEFAULT); // build an option element for each employee while (oci_fetch($empl_stmt)) { // EITHER style of oci_result 2nd argument is fine here! // (BUT if giving projected column name, remember // NEEDS to be all-uppercase) $curr_empl_num = strip_tags(oci_result($empl_stmt, 1)); $curr_empl_name = strip_tags(oci_result($empl_stmt, "EMPL_LAST_NAME")); ?> <option value="<?= $curr_empl_num ?>"> <?= $curr_empl_num ?> - <?= $curr_empl_name ?> </option> <?php } // remember to free your statement, and close your connection, // when you are done with them!!!!!!! oci_free_statement($empl_stmt); oci_close($conn); ?> </select> <br /> <input type="submit" /> </form> <?php } // but if request method was "post", assume this is a // form response, and try to CAREFULLY/SAFELY query // info for the selected employee else { // I am assuming no nickname should have tags in it, // and am stripping any out, as well as removing // any leading and trailing blanks $chosen_empl_num = trim(strip_tags($_POST["empl_choice"])); $conn = hum_conn_no_login(); // note use of a BIND VARIABLE :chosen_empl_num, // NOT concatenation, to more SAFELY include the // selected employee's employee number in this // SELECT statement $get_empl_query = "select empl_last_name, job_title, salary from empl where empl_num = :chosen_empl_num"; $empl_info_stmt = oci_parse($conn, $get_empl_query); // need to BIND the bind variable TO a value oci_bind_by_name($empl_info_stmt, ":chosen_empl_num", $chosen_empl_num); // NOW execute! oci_execute($empl_info_stmt, OCI_DEFAULT); // empl_num is empl's primary key, so at MOST one row can result // from this particular query -- no loop needed here, // but DO still need to fetch that row! oci_fetch($empl_info_stmt); // again, demoing both kinds of 2nd argument for oci_result $chosen_empl_name = oci_result($empl_info_stmt, 1); $chosen_empl_job = oci_result($empl_info_stmt, 2); $chosen_empl_salary = oci_result($empl_info_stmt, "SALARY"); oci_free_statement($empl_info_stmt); // calling stored function get_manager to get this // employee's manager $get_mgr_str = "BEGIN :empl_mgr := get_manager(:empl_name); END;"; $get_mgr_stmt = oci_parse($conn, $get_mgr_str); // bind a value to the bind variable serving as the // argument in this function call oci_bind_by_name($get_mgr_stmt, ":empl_name", $chosen_empl_name); // you ALSO have to call oci_bind_by_name for // the output bind variable for the // function's returned value -- // but the 3rd argument is a PHP "thing" to be // ASSIGNED the value of that output bind variable // when this statement is executed // and the mandatory 4th argument in this case // is the maximum SIZE of what will be assigned // to the 3rd argument (assumed to be type string, // select the size accordingly) // It so happens get_manager returns a string of at MOST // 15 characters oci_bind_by_name($get_mgr_stmt, ":empl_mgr", $chosen_empl_mgr, 15); oci_execute($get_mgr_stmt, OCI_DEFAULT); // NOW $chosen_empl_mgr has the value from the function // call! oci_free_statement($get_mgr_stmt); ?> <p> For employee <?= $chosen_empl_name ?>: </p> <ul> <li> Employee number: <?= $chosen_empl_num ?> </li> <li> Job title: <?= $chosen_empl_job ?> </li> <li> Salary: $<?= $chosen_empl_salary ?> </li> <li> Their manager: <?= $chosen_empl_mgr ?> </li> </ul> <p> <a href="<?= htmlentities($_SERVER["PHP_SELF"], ENT_QUOTES) ?>"> Request another employee's info </a> </p> <?php // remember to free close your connection, // when you are done with it!!!!!!! oci_close($conn); } ?> <footer> <hr /> <p> Validate by pasting .xhtml copy's URL into<br /> <a href="https://validator.w3.org/nu"> https://validator.w3.org/nu </a> or <a href="https://html5.validator.nu/"> https://html5.validator.nu/ </a> </p> </footer> </body> </html>