Create PHP AQ Consumer Web App

Posted on 16th October 2017

The second PHP webpage we need to create is the Salary Review Approval page. This page acts as the consumer to the Oracle Advanced Queue.

When the PHP script executes, it makes a connection to the Oracle database using the oci_connect function.

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

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

$stdq = oci_parse($conn, "begin deq(:employee_id, :incr_percent); end;");

The parameters to the deq PL/SQL procedure are OUT parameters, which will get you the employee id and the increment percentage from the Oracle AQ.

oci_bind_by_name($stdq, ":employee_id", $emp_id, 6);
oci_bind_by_name($stdq, ":incr_percent",  $incr_percent, 2);
@oci_execute($stdq);

If Employee Id is not null, ie., there are messages in the Queue, then we run a second query to get employee details and display it on the page to Approve or Reject. Our page would then look like this:

In case the user rejects the review then the form is not submitted and the next message is dequeued from the Oracle AQ.

If the user press Approve then the form data is submitted using POST method to the same page.

<form action=<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?> method="post">

When the PHP page received the submitted form data, it will run a update query to update the salary of the employee.

$stupd = oci_parse($conn, "UPDATE EMPLOYEES SET SALARY=:salary WHERE EMPLOYEE_ID=:employee_id");

Source Code

Here is the full source code of the PHP script. Copy it to a file named review-approval.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

 /* Connect to HR Schema */
 $conn = @oci_connect('HR', 'password', 'localhost/MYDB');
 if (!$conn) {
  die("Database Connection Error");
 }

 if (empty($_POST)):

  /* Parse SQL to dequeue */
  $stdq = oci_parse($conn, "begin deq(:employee_id, :incr_percent); end;");

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

  /* Execute PL/SQL procedure to Dequeue */
  @oci_execute($stdq);

  if (!$emp_id) {
   die("Queue is empty");
  }
  
  /* Parse SQL to Get Employee Details */
  $stemp = oci_parse($conn, "SELECT * from EMPLOYEES WHERE EMPLOYEE_ID=:employee_id");
 
  /* Bind variables */
  oci_bind_by_name($stemp, ":employee_id", $emp_id, 6);

  /* Execute PL/SQL procedure to get Employee details */
  oci_execute($stemp);

  echo "<table>";
  $emp = oci_fetch_array($stemp, OCI_BOTH);
 
  $salary = $emp['SALARY'] + ($emp['SALARY'] * $incr_percent / 100);
 ?>

  <h2>Salary Review Aproval Form</h2>
   <form action=<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?> method="post">
	<table>
   	<tr><td>Employee ID</td><td><?php echo $emp_id ?></td></tr>
	<tr><td>First Name</td><td><?php echo $emp['FIRST_NAME']  ?></td></tr>
    <tr><td>Last Name</td><td><?php echo $emp['LAST_NAME'] ?></td></tr>
    <tr><td>Job ID</td><td><?php echo $emp['JOB_ID']  ?></td></tr>  
    <tr><td>Current Salary</td><td><?php echo $emp['SALARY']  ?></td></tr>
    <tr><td>Increment(%)</td><td><?php echo $incr_percent ?></td></tr>
    <tr><td>New Salary</td><td><?php echo $salary; ?></td></tr>
    <input type="hidden" name="empid" id="empid" value=<?php echo $emp_id ?> />
    <input type="hidden" name="empname" id="empname" value=<?php echo ($emp['FIRST_NAME'] .' '.$emp['LAST_NAME']) ?> />
    <input type="hidden" name="salary" id="salary" value=<?php echo $salary ?> />
    <tr><td colspan="2">
    <input type="submit" value="Approve"/>
   	<input type="button" value="Reject & Get Next" onclick="location.reload()"/>
   	</td>
   	</tr>
   	</table>
   </form>

<?php else:
  $salary = $_POST["salary"];
  $empname = $_POST["empname"];
  $empid = $_POST["empid"];

  /* Parse SQL to Get Employee Details */
  $stupd = oci_parse($conn, "UPDATE EMPLOYEES SET SALARY=:salary WHERE EMPLOYEE_ID=:employee_id");

  /* Bind variables */
  oci_bind_by_name($stupd, ":employee_id", $empid, 6);
  oci_bind_by_name($stupd, ":salary", $salary, 8);

  /* Execute PL/SQL procedure to get Employee details */
  oci_execute($stupd);

?>
  <p>Salary increment for <b><?php echo $empname ?></b> is now approved.
  The new salary is <b><?php echo $salary ?><b/>.</p>

<?php endif; ?>

</body>
</html>

« PHP AQ Producer

Comments

Nothing yet..be the first to share wisdom.

PHP with Oracle AQ

Create PHP AQ Consumer Web App

Source Code

Post a comment

Comments