Ad

PHP And Oracle Stored Procedure

i am having a bit of trouble working out how to use PHP and call an oracle stored procedure.

Running the following command directly on SQL developer gives me the results I need:

 variable O_username varchar2;
 EXECUTE LOGINADMIN('John','John', 'Attractions', :O_username);

  print O_username;

However on PHP I am having trouble getting the results I require. This is my PHP code.

include 'connection.php';


 $username = ($_POST['uname']);
    $password = ($_POST['pass']);
    $role = ($_POST['admin_role']);
$query = "EXECUTE LOGINADMIN(:bind1,:bind2, :bind3, :bind4);
";
oci_bind_by_name($stmt, ":bind1", $username);
oci_bind_by_name($stmt, ":bind2", $password);
oci_bind_by_name($stmt, ":bind3", $role);
oci_bind_by_name($stmt, ":bind4", $result, 50);

$stmt = oci_parse($conn, $query);
oci_execute($stmt);
while ($row=oci_fetch_row($stmt))

$count = oci_num_rows($stmt);
ECHO $stmt;
ECHO $result;

echo OCI_RESULT ($stmt);
if ($count > 0 && $role == 'Attractions' ) {
        echo ("<SCRIPT LANGUAGE='JavaScript'>
        window.alert('Logged Succesfully!')
        window.location.href='attractionshome.php'
        </SCRIPT>");
           $_SESSION['attractionlogin'] = $username;
        exit();

It looks like I am having some trouble getting the input out so $result can see there is a variable for this.

thank you,

Ad

Answer

You first need to ensure that your stored procedure is correct, and it returns the expected result:

create or replace PROCEDURE loginadmin
(
v_username    IN STAFF.USERNAME%TYPE,
v_password    IN STAFF.PASSWORD%TYPE,
v_admin_role  IN ADMIN.ADMIN_ROLE%TYPE,
O_Username    OUT STAFF.USERNAME%TYPE
)
AS
  BEGIN
  SELECT STAFF.USERNAME INTO O_Username
  FROM STAFF 
  INNER JOIN ADMIN
  ON STAFF.NINO = ADMIN.NINO
  WHERE STAFF.USERNAME = v_username AND STAFF.PASSWORD = v_password and ADMIN.ADMIN_ROLE = v_admin_role;
END;
 /

Then execute it to test its result!

variable O_Username varchar2;
execute loginadmin ('testUsername', 'testPassword', 'testRole', :O_Username);
print O_Username;

Then in your php code, make sure you include the length as @tim have indicated. (in my example is 40). Then try to execute your stored procedure with a begin!

include 'connection.php';
$username = $_POST['uname'];
$password = $_POST['pass'];
$role = $_POST['admin_role'];
$query = 'begin loginadmin (:bind1,:bind2, :bind3, :bind4); end;';

$stmt = oci_parse($conn, $query);
oci_bind_by_name($stmt, ':bind1', $username);
oci_bind_by_name($stmt, ':bind2', $password);
oci_bind_by_name($stmt, ':bind3', $role);
oci_bind_by_name($stmt, ':bind4', $result, 40);
oci_execute($stmt);

Then have your code logic to check if the result matches the username and the role is Master for example and you know the rest!

if ($result == $username && $role == 'Master' ) {
        echo ("<SCRIPT LANGUAGE='JavaScript'>
        window.alert('Logged Succesfully!')
        window.location.href='master.php'
        </SCRIPT>");
           $_SESSION['attractionlogin'] = $username;
        exit();
    } 
Ad
source: stackoverflow.com
Ad