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 - jasendorf@lcounty.com 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:clever@ukl.uni-freiburg.de] > Sent: Thursday, April 05, 2001 2:21 AM > To: php-windows@lists.php.net; JAsendorf@lcounty.com > 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" <JAsendorf@lcounty.com> > > Mi > 20:35 > > Betreff: > Oracle record counting > An: > "Php-Windows (E-mail)" <php-windows@lists.php.net> > > > > 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 - jasendorf@lcounty.com > 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 > > fc@clever-soft.com > 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: php-windows-unsubscribe@lists.php.net > For additional commands, e-mail: php-windows-help@lists.php.net > To contact the list administrators, e-mail: > php-list-admin@lists.php.net >