PHP function to perform SQL query not working

Question

I am trying to use a function to perform an SQL query and return the result. Doing a manual SQL query with the same query as the below works and returns the correct result but doing it within PHP is not working at all (page is blank).

connection.php

<?php
require_once("settings.php");
$conn = oci_connect($db_user, $db_pass, $db_ip.'/'.$db);
if (!$conn) {
 $e = oci_error();
 trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
?>

functions.php

<?php
require_once("connection.php");
function GetInvoice($n) 
 // returns invoice number for given order number
{ 
 $stid = oci_parse($conn, 'SELECT InvoiceNo FROM Orders WHERE OrderNo = $n');
 oci_define_by_name($stid, 'InvoiceNo', $InvoiceNo);
 oci_execute($stid);
 echo "$InvoiceNo";
} 
GetInvoice(999645);
?>

Doing the manual SQL query shows a resulting InvoiceNo, it is just not working in PHP.

Thanks

Steve

• bksi: Any errors in the error log?
• Hüseyin BABAL: You are using variables that outside the function's scope. You need to pass $conn to your function. See my answer for more details

Answer 1

You are trying to use a variable outside of function inside the function. That variable is not in function's scope. Due to this, $conn is undefined. Also, you have used $InvoiceNo withoud fetch result, I did some refoctoring on that place. You can use following;

function GetInvoice($n, $conn) 
 // returns invoice number for given order number
{ 
 $stid = oci_parse($conn, 'SELECT InvoiceNo FROM Orders WHERE OrderNo = $n');
 oci_define_by_name($stid, 'InvoiceNo', $InvoiceNo);
 oci_execute($stid);
 while (oci_fetch($stid)) {
 echo "$InvoiceNo";
 }
}
GetInvoice(999645, $conn);

• user2656114: Thanks for the answer! I have tried that and I still see a blank page with no result shown. I tested the SQL query again manually a moment ago and it shows a result. Do you have any other ideas I can try?
• Hüseyin BABAL: @user2656114 Could you please put error_reporting("E_ALL"); at the top of problematic page, so you can see error on page.

Answer 2

The parameters you are passing in functions, seems undefined

as you have created your function needs only one parameter, then from where this will get this variable $InvoiceNo

oci_define_by_name($stid, 'InvoiceNo', $InvoiceNo);