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]
>
--
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]
