Oracle Select not working correctly with PHP

mercrapper

Hi all,

I am having problems with my select statement from my PHP script.

SELECT MESSAGEID,SENDER,RECEIVER,APPLICATION,TO_CHAR(CREATETIME,'YYYY/MM/DD HH:MI:SS AM') AS CREATETIME,CONTROLNO 
FROM MMC_MESSAGE 
WHERE CREATETIME>='28-Oct-05' 
ORDER BY CREATETIME DESC, MESSAGEID

This exact same query runs fine using an Oracle front end tool (SQL Tools).
I get the results I expect (2 records).

When I run this in my PHP script, I get ALL records back as if the WHERE clause was not there. This only happens when I try to filter using a date. If I filter using any of the other columns (text), it workd fine.

I am using the ADODB abstraction layer.

Any help would be great.

Thanks
Steve

gardnc

Steve,

To debug this print the SQL query out right after you define it like:

$sql = "select * .....ect";
echo $sql;exit;

Then, look at that statement in the browser, then copy and past to try it in a command prompt of other administration for Oralce and see it it works. I'll bet it doesn't.

mercrapper

I have, that is where I got the SQL statement from that is posted.
I just formatted it a little for the post.

gardnc

I'm not a SQL guy, but I have done a lot SQL work in PHP and never had any issues with the ADODB. I have however, had tons of problems thinking my query was right but after printing out the query that PHP was creating I found many, many errors. That's why I suggested printing out the query from the php page to be sure there was not a missing or added character, etc. I don't follow the query at all - I guess it's becaus it is Oracle. I wondered how the parser would know that "CREATETIME>" was a field and an operator not just a field withput the space between. But again I'm not into SQL, I put spaces every where just so I can see better. Anyway, sorry I couldn't help.

Larry

mercrapper

Thanks for the help Larry.
Basically my query is trying to get all records where "CREATEDATE" is on or after October 28, 2005

Anybody else have any more ideas?

Sxooter

OK, can we see the PHP script that is running this? Oracle takes a few extra steps to run a query (basically every query is prepared then execute).. Here's an example of how I run mine:

putenv("TNS_ADMIN=/u01/app/oracle/product/orcl10gc/network/admin/");
putenv("ORACLE_HOME=/u01/app/oracle/product/orcl10gc/");
$oconn = oci_connect("USERNAME","*********","DBNAME");
$query = "select * from schema.table order by somefield desc";
$res = oci_parse($oconn,$query);
usleep(100);
if (oci_execute($res)){
        usleep(100);
        print "<TABLE border \"1\">";
        $first = 0;
        while ($row = @oci_fetch_assoc($res)){
#               $row = @oci_fetch_assoc($res);
                if (!$first){
                        $first = 1;
                        print "<TR><TH>";
                        print implode("</TH><TH>",array_keys($row));
                        print "</TH></TR>\n";
                }
                print "<TR><TD>";
                print @implode("</TD><TD>",array_values($row));
                print "</TD></TR>\n";
        }
        print "</TABLE>";
}
?>

mercrapper

I use ADODB and have set it up to run with multiple databases concurrently, so my code looks a little funky.

but here are the parts of interest.

I connect to each of my databases using ADODB and put the resource ids in an array. This is an example of one of those connections. I also start the SQL statement for that database.

// load adodb library
require_once 'adodb/Adodb.inc.php';
$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
$ADODB_COUNTRECS = false;

$db_name = "DEV";
// Connect to Oracle
$db[$db_name] = NewADOConnection('oci8');
$Connect_Result = $db[$db_name]->Connect("server:port", "user", "password", "connect string");
if ($Connect_Result === false) {
	unset($db[$db_name]);
	$log[] = "Warning : Unable to connect to Oracle database ($db_name)<br>";
}
$db[$db_name]->debug=true;
$sql_select[$db_name] = "SELECT MESSAGEID, SENDER, RECEIVER, APPLICATION, CREATETIME, CONTROLNO FROM MMC_MESSAGE";

I then retreive the data submitted from a form and load a $filers array (not shown). I then append the results from the $filters array to the query and execute the query for each database. The select resource id is then placed on another array.

//Execute query
while (list($key,$cur_db) = each($db)) {
	if (is_object($cur_db)) {
		$query = $sql_select[$key];
		if (isset($filters[$key])) {
			$query .= " WHERE $filter";
		}
		$query .= " ORDER BY CREATETIME ".SORT_DIRECTION.", MESSAGEID";
		//$query = "SELECT MESSAGEID,SENDER,RECEIVER,APPLICATION,TO_CHAR(CREATETIME,'YYYY/MM/DD HH:MI:SS AM') AS CREATETIME,CONTROLNO FROM MMC_MESSAGE WHERE CREATETIME > '01-Oct-05' ORDER BY CREATETIME DESC, MESSAGEID";
		//$query = "SELECT COUNT(*) FROM MMC_MESSAGE WHERE CREATETIME BETWEEN '01-Oct-05' AND '01-Oct-06' ORDER BY CREATETIME DESC, MESSAGEID";
		$log[] = log_message("I1", "Execute Query [$key]");
		$log[] = log_message("I1", "SQL : $query");
		$Select_Result[$key] =& $cur_db->Execute($query);
		if ($Select_Result[$key] === false) {
			unset($Select_Result[$key]);
			$log[] = log_message("W1","Warning : Unable to execute query ($key)");
			continue;
		}
	}
}
if (count($Select_Result) <= 0) {
	$log[] = log_message("E1","Error : No queries were successful");
	quit(&$smarty,$log);
}

Here I loop through the resource id array for the successful queries and pull the results. I also flush the output after FLUSH_LIMIT records (currently set to 100).

//Get results
$curr_count = 0;
$records = array();
$result_count = array();
while (count($Select_Result) > 0) {
	while (list($key,$cur_select) = each($Select_Result)) {
		$j=0;
		while (!$cur_select->EOF && $j <= FLUSH_LIMIT) {
			$j++;
			$records[] = $cur_select->fields;
			$cur_select->MoveNext();
		}
		$result_count[$key] += $j;
		$curr_count += $j;
		if($cur_select->EOF) {
			unset($Select_Result[$key]);
		}
	}
	//Flush output to browser
	if (count($records)>0) {
		$records = array_csort($records,"CREATETIME",CSORT_DIRECTION,"MESSAGEID");
		$curr_records = array_slice($records,0,FLUSH_LIMIT);
		$records = array_slice($records,FLUSH_LIMIT);
		$smarty->assign('records',$curr_records);
		$smarty->display('index_body.tpl');
		ob_flush();
		flush();
		reset($Select_Result);
	}
}

This works fine as long as I do not have a data in my where clause.

Please let me know if you need more iformation.

Thanks
Steve

Sxooter

Well, I'd try running the same query through the oracle interface directly and seeing if it works. There may well be a bug int he ADODB interface for Oracle.

mercrapper

Hi everybody,

Thanks for all you help. I figured it out.
ADODB requires the date in the where clause to be Y-m-d.
This way it cn convert the data to the correct format for the specific database.

Thanks again
Steve