Page 1 of 1

PHP Oracle Row Fetching Problem

Posted: Fri Apr 23, 2010 5:33 pm
by Squeage
Hi all. I have a group project for my database course, and one of my duties is to get the search page working. The code I'm going to show involves taking in a query string from a seperate function (that works), and executing it. Then a string variable, $display_string, holds html code as well as the query results while being fetched. My problem is this: when the code executes I keep getting an ORA-24338 error saying that a statement handle isn't being executed. It says this occurs on the line where the while loop for fetching is at. I thought I could figure it out eventually but I haven't had any luck after a couple days of trial and error and research. I hope you guys can help me out.

Code: Select all

if($action == 'do_search'){
        $sql = handle_search();
        throw new redirect('search.php');
}
        $result = oci_parse($connection,$sql);

        oci_execute($result);

$result = empty($result)? '' : $result;

$ncols = oci_num_fields($result);

$display_string = "<table border=1 cellpadding=5>\n";
$display_string .= "<tr>\n";
$display_string .= "<th>Address</th><th>City</th><th>State</th><th>Year</th><th>Size</th><th>Bedrooms</th><th>Bathrooms</th><th>Livingrooms</th><th>Basement</th><th>House Type</th><th>Model</th><th>Price</th>\n</tr>\n";

//Loop through the query results and print them in to the table
while ($line = oci_fetch_array($result)) {
        $display_string .= "<tr>\n";
        foreach ($line as $col_value)
                $display_string .= " <td>$col_value</td>\n";
        $display_string .= "</tr>\n</table>";
}

// Free resultset
oci_free_statement($result);

// Closing connecion
oci_close($connection);
The error message is:
Warning: oci_fetch_array() [function.oci-fetch-array]: ORA-24338: statement handle not executed in /.../public_html/dbproj2/search.php on line 48

Re: PHP Oracle Row Fetching Problem

Posted: Fri Apr 23, 2010 6:01 pm
by mikosiko
I'll check the values $sql and $result to be sure that they are valid

the meaning of ora-24338 http://ora-24338.ora-code.com/ seems it is pointing that some of those items are incorrect

Re: PHP Oracle Row Fetching Problem

Posted: Fri Apr 23, 2010 7:17 pm
by mecha_godzilla
Would agree with that - try commenting out the rest of the code after the if() statement (and also the 'throw new redirect' statement nested in it) and echo $sql to see what it contains. Hope I'm not being a bit thick here but is handle_search() another function in your script or is this some kind of stored procedure - have never worked with Oracle server but would be interested to know how things are supposed to be done in it.

I've also just noticed that the rest of the script will execute even if $action does not equal 'do_search' - is this right? I would have thought that the rest of the script would need to be nested in the if() statement, otherwise if the if() statement isn't getting the right value then the rest of the result handling occurs without the original query being run (IE the script is trying to work with data that doesn't exist.)

HTH,

Mecha Godzilla

Re: PHP Oracle Row Fetching Problem

Posted: Sat Apr 24, 2010 12:41 pm
by Squeage
Ok I'll try checking what those variables are before they are handled. And ya handle_search is a function that another group member did that basically puts the query into $sql. I'll get back to you guys if I figure it out.

Re: PHP Oracle Row Fetching Problem

Posted: Sat Apr 24, 2010 8:08 pm
by Squeage
Ok guys, I took what you suggested and with some trial and error, I was able to get everything to print out without any errors. I still have one problem though. When the query results get looped and printed, somehow a double of every column value is printed as well. Here is an example of some output:
(it's a table of house listings. The columns are Address, City, State, Year, etc.)
123 N. Ashland St. | 123 N. Ashland St. | Chicago | Chicago | IL | IL | 2003 | 2003
It should be:
123 N. Ashland St. | Chicago | IL | 2003

I can't figure out why since when I do the same exact query in the sqlplus (Oracle database command line) it brings up what it should. Here the code for the entire file in case it's something I'm not seeing.

Code: Select all

<?php

session_name("homelisting");
session_start();


set_include_path('syst:templates');
require_once('db.conf');
require_once('redirect.php');
require_once('template.php');

// Make a connection to the database
$dbconn = oci_connect($dbuser,$dbpass,$sid) or die("Couldn't make a connection!");

$loggedIn = false;

if(!empty($_SESSION['loggedIn']) && $_SESSION['loggedIn'] == true){
	$loggedIn = true;
}
if(!empty($_SESSION['isAdmin']) && $_SESSION['isAdmin'] == true){
        $isAdmin = true;
}else{
	$isAdmin= false;
}

$search = empty($_POST['search'])? '' : $_POST['search'];
$action = empty($_POST['action']) ? '' : $_POST['action'];

$display_string = "";
$sql = null;
$result = null;
$parsed = null;
if($action == 'do_search'){
  	$query = handle_search();
//	throw new redirect('search.php');

	$parsed = oci_parse($dbconn,$query);
	$parsed = empty($parsed)? '' : $parsed;
	oci_execute($parsed);

	$display_string = "<table border=1 cellpadding=5>\n";
	$display_string .= "<tr>\n";
	$display_string .= "<th>Address</th><th>City</th><th>State</th><th>Year</th><th>Size</th><th>Bedrooms</th><th>Bathrooms</th><th>Livingrooms</th><th>Basement</th><th>House Type</th><th>Model</th><th>Price</th>\n</tr>\n";

	//Loop through the query results and print them in to the table
	while ($line = oci_fetch_array($parsed)) {
		$display_string .= "<tr>\n";
		foreach ($line as $col_value) {
			$display_string .= " <td>" . $col_value . "</td>\n";
		}
		$display_string .= "</tr>\n";
	}
	$display_string .= "</table>\n";
	// Free resultset
	oci_free_statement($parsed);
}

// Closing connecion
oci_close($dbconn);

$username = empty($_SESSION['username'])? '' : $_SESSION['username'];
$errMsg = empty($_SESSION['errMsg'])? '' : $_SESSION['errMsg'];

$tmpl = new Template();

$tmpl->parsed = $parsed;
$tmpl->display_string = $display_string;
$tmpl->search = $search;
$tmpl->errMsg = $errMsg;
$tmpl->loggedIn = $loggedIn;
$tmpl->username = $username;
$tmpl->isAdmin = $isAdmin;
print $tmpl->build('banner.tmpl');
print $tmpl->build('search.tmpl');
print $tmpl->build('bottom.tmpl');




function handle_search(){

include_once('safe.php');

$search = $_POST['search'];

$search = safe($search);

$sql = "SELECT address,city,state,yearbuilt,house_size,noofbedrooms,noofbathrooms,nooffamilyrooms,hasbasement,house_type,model,price FROM Houselisting WHERE address LIKE '%" . $search . "%' OR city LIKE '%" . $search . "%' OR state LIKE '%" . $search . "%' OR yearbuilt LIKE '%" . $search . "%' OR house_size LIKE '%" . $search . "%' OR house_type LIKE '%" . $search . "%' OR model LIKE '%" . $search . "%' OR price LIKE '%" . $search . "%'";

return $sql;
}
?>
Usually when I get double output it's because of my loops, but I've checked my loops over and over again and I'm pretty sure it's correct. Any help would be great. Thanks again.

Re: PHP Oracle Row Fetching Problem

Posted: Sat Apr 24, 2010 9:03 pm
by mikosiko
my suggestion... read the description of the oci_fetch_array and pay attention to the second parameter (the one you aren't using... )...

http://php.net/manual/en/function.oci-fetch-array.php

Miko

Re: PHP Oracle Row Fetching Problem

Posted: Sun Apr 25, 2010 5:01 am
by Squeage
Well I was going to answer to that with the fact that I have stared at that php.net page for forever tryin to get things right and I kept coming to the conclusion that I know I am using the fetch to get an associative array and since the default second parameter for oci_fetch_array is OCI_BOTH (OCI_ASSOC + OCI_NUM) then it shouldn't be a problem...BUT I took another look at it since you mentioned it and I decided to just set it to OCI_ASSOC to see what happens. And you were totally RIGHT!!! Thanks a ton for your help. I appreciate it so much. I'll be sure to come here more often if I get stumped again.