Create PHP AQ Producer Web App

Posted on 16th October 2017

Now that we have created the tables, Advanced Queues and procedures to Enqueue and Dequeue, we can move on to the PHP part. We have two PHP webpages and the first webpage we will create is the "Salary Review Form" page which acts as a AQ Producer by enqueing messages to the Oracle AQ.

The Salary Review Form page has two textboxes to input and Employee's employee id and the Percentage of salary increment.

An Employee's manager will input these fields and submits the form. The submitted form data is send using POST method to the page itself with the form action $_SERVER["PHP_SELF"]

When the PHP pages receives the submitted POST data, it will establish a database connection to your Oracle DB using the oci_connect function.

$conn = @oci_connect('HR', 'Password', 'localhost/MYDB');

Next, we pass the SQL statement that calls the enq PL/SQL procedure to oci_parse function.

$stid = oci_parse($conn, "begin enq(:employee_id, :incr_percent); end;");

Then we bind the variables :employee_id and :incr_percent to data that was received from the POST.

$emp_id = $_POST["emp_id"];
$incr_percent = $_POST["incr_percent"];

oci_bind_by_name($stid, ":employee_id", $emp_id, 6);
oci_bind_by_name($stid, ":incr_percent", $incr_percent, 2);

Finally we call the oci_execute function to execute the SQL statement.

oci_execute($stid);

Source Code

Here is the full source code of the PHP script. Copy it to a file named salary-review.php and save it in your web servers root.

This script also uses a CSS stylesheet, which is optional. However you could download it from the link here.

<html>
<head>
<link href="my_sample_style.css" rel="stylesheet" type="text/css" />
</head>
<body>
<?php if (empty($_POST)): ?>
 <h2>Salary Review Form</h2>
 <form action=<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?> method="post">
   <table>
	<tr>
     <td><label for="eid">Enter Employee ID</label></td>
     <td><input type="text" name="emp_id" id="eid"  maxlength="6" size="6"/></td>
   </tr>
   <tr>
    <td><label for="inc">Salary Increment %</label></td>
    <td><input type="text" name="incr_percent" id="inc" maxlength="2" size="2"/></td>
   </tr>
   <tr>
     <td colspan="2"><input type="submit"></td>
   </tr>
  </table>
 </form>
<?php else:

  /* Connect to HR Schema */
  $conn = @oci_connect('HR', 'password', 'localhost/MYDB');

  if (!$conn) {
   die("Database Connection Error");
  }

  /* Parse PL/SQL procedure call statement */
  $stid = oci_parse($conn, "begin enq(:employee_id, :incr_percent); end;");

  /* Get input values from POST */
  $emp_id = $_POST["emp_id"];
  $incr_percent = $_POST["incr_percent"];

  /* Bind variables */
  oci_bind_by_name($stid, ":employee_id", $emp_id, 6);
  oci_bind_by_name($stid, ":incr_percent", $incr_percent, 2);

  /* Execute PL/SQL procedure */
  oci_execute($stid);
?>
  <p>Salary increment of <?php echo $incr_percent ?>% for Employee ID <?php echo $emp_id ?> is now submitted for approval.</p>

<?php endif; ?>

</body>
</html>

« Dequeue Procedure PHP AQ Consumer »

Comments

Nothing yet..be the first to share wisdom.

PHP with Oracle AQ

Create PHP AQ Producer Web App

Source Code

Post a comment

Comments