Hi John,
I see in your code that currently you are using
while ( OCIFetchInto ( $stmt_search , $results ) ) {
and
//for ( $i = 0; $i < $nrows; $i++ ) {
//reset($results);
//$data = $column['value'];
is not being used.
But you are actually using the variables for which you did a
OCIDefineByName to print out the data. I would say that a
FetchIntoStatement will not be able fill these variables as it operates on
the complete result set. Thus you have to pull the data from the array.
When you use FetchInto the defined variables can be filled as PHP and
Oracle then do actually know which row you are currently pointing to, as
FetchInto moves to the next record.
FetchIntoStatement fetches all rows, thus it is not pointing to a specific
row.
Let me know what you get. Maybe it would be good to add a note about this
to the Manual
Florian
"Asendorf, John" <[EMAIL PROTECTED]>
05.04.01 14:19
An: Florian Clever <[EMAIL PROTECTED]>, [EMAIL PROTECTED]
Kopie:
Thema: RE: [PHP-WIN] Oracle record counting
It's a bit long... and it's bastardized in that there are a number of
lines
commented out because I'm experimenting. The following code gives me a
list
of customers to choose from. It works now, but I wanted to make sure I
get
at least ONE result. If not I want to kick back an error. If there is a
better IF statement I can use to make sure I have at least one result,
that
would be fine too.
//start our SQL
$sql_search = "SELECT CUSTID_NUM, CUST_NAME, CUST_ADDR1,
CUST_ADDR2,
CUST_CITY, CUST_PHONE FROM cfull2.tbl_dl_customers ";
//determine what kind of search we're doing with an if,
elseif and
else
if ( $input_cust_name && $input_address ) { //SEARCH using both name
and address
if ( $debug ) { //DEBUG
echo "\n<br />DEBUG:
Searching using both name and
address<br />\n";
}
$sql_search .= "WHERE UPPER(CUST_NAME)
LIKE
UPPER('%$input_cust_name%') AND UPPER(CUST_ADDR1) LIKE
UPPER('%$input_address%')";
}
elseif ( $input_cust_name ) { //SEARCH
using only
customer name
if ( $debug ) { //DEBUG
echo "\n<br />DEBUG:
Searching using just name<br
/>\n";
}
$sql_search .= "WHERE UPPER(CUST_NAME)
LIKE
UPPER('%$input_cust_name%')";
}
else { //SEARCH
using address
if ( $debug ) { //DEBUG
echo "\n<br />DEBUG:
Searching using just address<br
/>\n";
}
$sql_search .= "WHERE UPPER(CUST_ADDR1)
LIKE
UPPER('%$input_address%')";
}
$sql_search .= " ORDER BY CUST_NAME";
// $stmt_search will now contain a statement handle which
is ready
to execute
$stmt_search = OCIParse ( $connection , $sql_search );
OCIDefineByName( $stmt_search , "CUSTID_NUM" ,
$CUSTID_NUM );
OCIDefineByName( $stmt_search , "CUST_NAME" , $CUST_NAME
);
OCIDefineByName( $stmt_search , "CUST_ADDR1" ,
$CUST_ADDR1 );
OCIDefineByName( $stmt_search , "CUST_ADDR2" ,
$CUST_ADDR2 );
OCIDefineByName( $stmt_search , "CUST_CITY" , $CUST_CITY
);
OCIDefineByName( $stmt_search , "CUST_PHONE" ,
$CUST_PHONE );
OCIExecute ( $stmt_search , OCI_DEFAULT );
//$nrows = OCIFetchStatement( $stmt_search , $results );
//echo "<P>There are $nrows records containing your
criteria.</P>";
//if ( $nrows > 0 ) {
echo "<TABLE BORDER=\"0\" width=\"100%\">\n";
echo "<TR>\n";
echo "<TD>Name</TD><TD>Address</TD><TD>City</TD><TD>Phone</TD>\n";
echo "</TR>\n";
//for ( $i = 0; $i < $nrows; $i++ ) {
//reset($results);
while ( OCIFetchInto (
$stmt_search , $results ) ) {
//format the phone number
$CUST_PHONE = format_phone ( $CUST_PHONE );
echo "<TR>\n";
//$data = $column['value'];
echo "<TD><a
href=\"dl_post_2.php?input_customer=$CUSTID_NUM\">";
echo
"$CUST_NAME</a></TD>";
echo
"\n<TD>$CUST_ADDR1 <br>
$CUST_ADDR2</TD> \n";
echo
"<TD>$CUST_CITY</TD>\n<TD>$CUST_PHONE</TD>\n";
echo "</TR>\n";
}
//}
echo "</TABLE>\n";
//}
//else {
// echo "<h2>Unable to locate any customers with that search
criteria.</h2><BR>\n";
//}
//free resources
OCIFreeStatement ( $stmt_search );
---------------------
John Asendorf - [EMAIL PROTECTED]
Web Applications Developer
http://www.lcounty.com - NEW FEATURES ADDED DAILY!
Licking County, Ohio, USA
740-349-3631
Aut insanit homo, aut versus facit
> -----Original Message-----
> From: Florian Clever [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, April 05, 2001 2:21 AM
> To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Subject: [PHP-WIN] Oracle record counting
>
>
> Hi John,
>
> your first example looks right. Can you send us the code you
> use to list
> the records. I would expect the problem in there, because in
> your second
> example basically the FetchStatement is not doing anything as the
> statement has not been executed.
> You need to loop through the result set array differntly after the
> FetchStatement, then looping through the results without exec.
>
> BTW: I had performance drops with the FetchStatement.
>
> Florian
>
>
> Von:
> "Asendorf, John" <[EMAIL PROTECTED]>
>
> Mi
> 20:35
>
> Betreff:
> Oracle record counting
> An:
> "Php-Windows (E-mail)" <[EMAIL PROTECTED]>
>
>
>
> I'm attempting to work on a little oracle search...
>
> My problem is that if I put the OCIExecute BEFORE the
> OCIFetchStatement
> line, I get one error ( Warning: OCIFetchInto: ORA-01002: fetch out of
> sequence in d:\InetPub\wwwroot\etc. ) but if I put OCIExecute
> AFTER the
> OCIFetchStatement I get a different error (Warning: OCIFetchStatement:
> ORA-24338: statement handle not executed in ).
>
> In this order:
>
> OCIExecute ( $stmt_search , OCI_DEFAULT );
> $nrows = OCIFetchStatement( $stmt_search , $results );
> echo "<P>There are $nrows records containing your criteria.</P>";
>
> I get the number of records, but the I don't get the reords listed.
>
>
>
> In this order:
>
> $nrows = OCIFetchStatement( $stmt_search , $results );
> echo "<P>There are $nrows records containing your criteria.</P>";
> OCIExecute ( $stmt_search , OCI_DEFAULT );
>
> I get the list of records, but no amount of records... I
> tried running
> a
> reset( $results); on it but that didn't seem to help...
>
> Can someone help? Oracle 8.1.6 client (7.3.4 server) NT4
> IIS4 PHP 4.0.4
> CGI
>
> Thanks,
>
> John
>
>
> ---------------------
> John Asendorf - [EMAIL PROTECTED]
> Web Applications Developer
> http://www.lcounty.com - NEW FEATURES ADDED DAILY!
> Licking County, Ohio, USA
> 740-349-3631
> Aut insanit homo, aut versus facit
> --
>
> Florian Clever
>
> [EMAIL PROTECTED]
> http://www.Clever-Software-Solutions.de/
>
> PGP Key @ ldap://certserver.pgp.net/
>
> "Always listen to experts. They'll tell you what can't be
> done, and why.
> Then do it. " -- Robert A. Heinlein
>
> --
> PHP Windows Mailing List (http://www.php.net/)
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> To contact the list administrators, e-mail:
> [EMAIL PROTECTED]
>
