Thanks Kevin,
It works. Here is an example for anyone else who wants to count matching
Stings from two different tables.
Sincerely,
Tom Kochanowicz
Statement stmt1 = null;
ResultSet dataResultSet1 = null;
Statement stmt2 = null;
ResultSet dataResultSet2 = null;
PrintWriter outputToBrowser = response.getWriter();
stmt1 = dbConnection.createStatement();
dataResultSet1 = stmt1.executeQuery("SELECT Email, Phrase, EndDate
FROM Look4U");
response.setContentType("text/html");
int countForMatches = 0;
// HTML Header
outputToBrowser.println("<HTML><HEAD><TITLE>Look4U
Results</TITLE></HEAD>");
outputToBrowser.println("<BODY>");
// Outer loop checks for email & phrase.
while(dataResultSet1.next()){
//System.out.println("THE PHRASE IS " +
dataResultSet1.getString("Phrase").trim());
String phrase = dataResultSet1.getString("Phrase").trim();
String email = dataResultSet1.getString("Email").trim();
// Inner loop checks if the Phrase found above matches any of
the WhatAdSays01 info.
stmt2 = dbConnection.createStatement();
dataResultSet2 = stmt2.executeQuery("select
COUNT(WhatAdSays01) from AdInfoView WHERE WhatAdSays01 LIKE '%" + phrase + "%'");
int numRows = 0;
while(dataResultSet2.next()){
// Count the number of matches.
numRows = dataResultSet2.getInt(1);
countForMatches++;
}
if(numRows > 0){
outputToBrowser.println("DATA RESULT SET 2 = " +
numRows + " Matching the Phrase " + phrase +
" Your email address is: " + email + "<P>");
}
}
outputToBrowser.println(getServletInfo());
}
-----Original Message-----
From: A mailing list for discussion about Sun Microsystem's Java Servlet
API Technology. [mailto:[EMAIL PROTECTED]]On Behalf Of Kevin
Mukhar
Sent: Wednesday, March 29, 2000 5:33 PM
To: [EMAIL PROTECTED]
Subject: Re: SQL/Servlet Question
"blueads.com" wrote:
>
> In my servlet, I am doing a query of one table bringing back the data with
> the method dataResultSet1.getString() then using what I get back to look for
> a match in another table using the sql LIKE syntax. I just want to count the
> matches from my first table that match my second table. I interate through
> them using an outer and innner while statement.
> - Should I be using sql COUNT and if so, how do I get the count into a
> variable I can use in my servlet?
For the second query, yes. Using the COUNT SQL statement will return a single
row with a single record. Read it with getInt().
K Mukhar
___________________________________________________________________________
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff SERVLET-INTEREST".
Archives: http://archives.java.sun.com/archives/servlet-interest.html
Resources: http://java.sun.com/products/servlet/external-resources.html
LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
___________________________________________________________________________
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff SERVLET-INTEREST".
Archives: http://archives.java.sun.com/archives/servlet-interest.html
Resources: http://java.sun.com/products/servlet/external-resources.html
LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
