I have a person table, the person table has a field which has their interests... In a form they choose which interests they have i.e. swimming, tennis through checkboxes. What i don't get IS how do i create a query based on this? (for example one person likes swimming and the other tennis:
SELECT * from Person WHERE interests LIKE '%Swimming%Tennis%';
the above gives me 0 results any ideas?
> oh wait... i just realised... I have a serious of
> checkboxses where the user selects their intrests.
> This will be one string is it possible to have both
> intrests in a % ?
See, there is always an encore despite initial perfection.
No, nothing standard that I know of. I don't think it would be difficult to parse the string and create the appropriate where clause. Here are a couple of ways (not tested so use at your own risk):
//
// Old way to parse a string
//
String aString = "word1 word2 word3";
StringTokenizer parser = new StringTokenizer(aString);
while (parser.hasMoreTokens()) {
System.out.println(parser.nextToken());
}
//
// New way to parse a string
//
String str = "This is a string object";
String[] words = str.split (" ");
for (int i=0; i < words.length; i++) {
System.out.println (words[i]);
}
> Ok then thers another problem say if the user chooses
> 3 interests what do i do then when my query has only
> 2 fields which is before the OR and after the OR.
I'm probably tired, but I have no idea what that sentence means.
> I'm using a prepared statement i basically
> pmst.setString(interest)
I think I get the gist of your question, but I am guessing.
You have a case where you sometimes have 2 and sometimes 3 different values that you want to query for.
Here are a couple of ideas, one related to programming and the other related to your database schema.
From a programming perspective, you will have to use two distinct (separate) PreparedStatements, one that handles two values and one that handles three.
From a database perspective, having to code SQL like this makes me think that the schema (the tables and columns) may not be correct. Your table must look like something like this:
PERSON
NAME
INTERESTS
But could look like this:
PERSON
NAME VARCHAR
TENNISBOOLEAN
SWIMMINGBOOLEAN
TABLETENNISBOOLEAN
Now your query could look like this, which is a bit simpler:
SELECT * FROM MEMBER WHERE TENNIS = true AND SWIMMING = true
This isn't 100% correct either, you should have multiple tables to represent the implied relationship, but this is the simplest implementation that will make it easy to code the SQL. Another option would be to have a single column INTERESTS and have a numeric code for the different possibilites (i.e. TENNIS = 1, SWIMMING = 2, TENNIS AND SWIMMING = 3, etc.). Again, not third normal form, but OK when doing something simple.
Just some thoughts, hope i didn't confuse you too much with this.
>From a programming perspective, you will have to use two distinct (separate) PreparedStatements, one that handles two values and one that handles three.
how about if i have 10 checkboxses will i have to do that for those too?
If so i can use the while loop you have in your StringTokenizer and count them then basically do an if statement saying that if theres 3 intrests choosen then use the 3 valued prepared statement. But that would be messy.
>From a database perspective, having to code SQL like this makes me think that the schema (the tables and columns) may not be correct. Your table must look like something like this:
Yes that's what it looks like i basically have a NAME, EMAIL and INTRESTS. Is there a better way of doing what i'm trying to do in terms of the database scheme or programmatically? I just want users to search for paritcular intresests they share, so they choose the intreersts via a series of check boxes:
[X] Tennis
[X] Basketball
[ ] Netball
Initially there are users in the database which have these values are stored like:
Tennis, Basketball in the INTRESTS column. Having a textbox works perfectly fine but a checkbox would be more better.
You can avoid difficult application issues by enabling a more appropriate schema.
Here is a very basic schema that may or may not work for you, but I hope it will give you some ideas. I did not create any unique indexes in this sample, but both PERSON and INTEREST table would have a unique index created on them. The PERSON_INTEREST table would have a non-unique index created for it. I also included a query that shows how to correctly query against this schema. This is in Oracle syntax, but the DDL should be similar to whatever DBMS you are using, and the SQL should be the same.
I'll be honest, this might be overkill for what you are attempting, but if you plan on extending your project / assignment in anyway, you would be better off with a more complete schema. I hope it helps. Good luck on your project / assignment.
CREATE TABLE PERSON (PERSON_ID NUMBER,NAME_LAST VARCHAR2(20), NAME_FIRST VARCHAR2(20), EMAIL VARCHAR2(30));
CREATE TABLE INTEREST(INTEREST_ID NUMBER, INTEREST_DESC VARCHAR2(20));
CREATE TABLE PERSON_INTEREST (PERSON_ID NUMBER,INTEREST_ID NUMBER);
INSERT INTO PERSON VALUES(1, 'SMITH','JOHN','JOHNSMITH@MAIL.COM');
INSERT INTO PERSON VALUES(2, 'JONES','FRED','FREDJONES@MAIL.COM');
INSERT INTO PERSON VALUES(3, 'ANDERSON','TOM','TOMANDERSON@MAIL.COM');
INSERT INTO PERSON VALUES(4, 'JACOBSEN','SAM','SAMJACOBSEN@MAIL.COM');
INSERT INTO PERSON VALUES(5, 'PUCKETT','KIRBY','KIRBYPUCKETT@MAIL.COM');
INSERT INTO INTEREST VALUES (1,'TENNIS');
INSERT INTO INTEREST VALUES (2,'SWIMMING');
INSERT INTO INTEREST VALUES (3,'BOWLING');
INSERT INTO INTEREST VALUES (4,'TABLE TENNIS');
INSERT INTO INTEREST VALUES (5,'FISHING');
INSERT INTO PERSON_INTEREST VALUES (1,1);
INSERT INTO PERSON_INTEREST VALUES (1,3);
INSERT INTO PERSON_INTEREST VALUES (1,5);
INSERT INTO PERSON_INTEREST VALUES (2,2);
INSERT INTO PERSON_INTEREST VALUES (2,3);
INSERT INTO PERSON_INTEREST VALUES (3,1);
INSERT INTO PERSON_INTEREST VALUES (3,2);
INSERT INTO PERSON_INTEREST VALUES (4,2);
INSERT INTO PERSON_INTEREST VALUES (4,3);
INSERT INTO PERSON_INTEREST VALUES (5,4);
INSERT INTO PERSON_INTEREST VALUES (5,5);
//
// Find all the Interests for a Person
//
SELECT
A.NAME_LAST,
A.NAME_FIRST,
B.INTEREST_DESC
FROM
PERSON A,
INTEREST B,
PERSON_INTEREST C
WHERE
A.NAME_LAST = 'SMITH'
AND A.NAME_FIRST = 'JOHN'
AND A.PERSON_ID = C.PERSON_ID
AND B.INTEREST_ID = C.INTEREST_ID
ORDER BY 3
NAME_LASTNAME_FIRSTINTEREST_DESC
-- -- -
SMITHJOHNBOWLING
SMITHJOHNFISHING
SMITHJOHNTENNIS
//
// Find all people who likes to swim and bowl
//
SELECT
A.NAME_LAST,
A.NAME_FIRST
FROM
PERSON A,
INTEREST B,
PERSON_INTEREST C
WHERE
B.INTEREST_DESC IN ('SWIMMING', 'BOWLING')
AND B.INTEREST_ID = C.INTEREST_ID
AND A.PERSON_ID = C.PERSON_ID
GROUP BY
A.NAME_LAST,
A.NAME_FIRST
ORDER BY 1,2
NAME_LASTNAME_FIRST
-- -
ANDERSON TOM
JACOBSEN SAM
JONESFRED
SMITHJOHN
The last query was actually an OR condition, not an AND condition which is why it brought back 4 names rather then 2. Here is an AND query that is a little ugly but works. It is supposed to return two people and it does. I didn't have much time to look at this, so there may be other / better ways too. If it were me, I probably wouldn't go to these lengths for an assignment (if that's what this is), but it might give you ideas (I hope). Again, best of luck.
//
// Find all people who likes to swim AND bowl
//
SELECT
A.NAME_LAST,
A.NAME_FIRST
FROM (
SELECT
A.NAME_LAST,
A.NAME_FIRST
FROM
PERSON A,
INTEREST B,
PERSON_INTEREST C
WHERE
B.INTEREST_DESC = 'SWIMMING'
AND B.INTEREST_ID = C.INTEREST_ID
AND A.PERSON_ID = C.PERSON_ID) A,
(SELECT
A.NAME_LAST,
A.NAME_FIRST
FROM
PERSON A,
INTEREST B,
PERSON_INTEREST C
WHERE
B.INTEREST_DESC = 'BOWLING'
AND B.INTEREST_ID = C.INTEREST_ID
AND A.PERSON_ID = C.PERSON_ID) B
WHERE A.NAME_LAST = B.NAME_LAST
AND A.NAME_FIRST = B.NAME_FIRST
NAME_LASTNAME_FIRST
-- --
JACOBSEN SAM
JONESFRED
Yes, PreparedStatements don't play well with lists of items. There really isn't a way around that aspect of PreparedStatements. You don't have to use ? place holders for the list, you can provide the actual list within your PreparedStatement (kind of a dynamic PreparedStatement which of course is an oxymoron).
Yes, that would get you there. Most Java purists would suggest that you use StringBuffer and .append(...) rather then String contatenation for efficiency sake, but again, in a small program it won't make enough difference to matter. It is a good idea in general to use StringBuffer for this purpose though.
StringBuffer sb = new StringBuffer(500); // User defined initial size
sb.append('SELECT * from Member WHERE ');
sb.append('PS_INTRESTS LIKE '%Tennis%');
sb.append('OR PS_INTRESTS LIKE '%Swimming%');
sb.append('OR PS_INTRESTS LIKE '%Hockey%');
//Use sb.toString() to convert to a String.
And of course using proper quoting it looks like this:
StringBuffer sb = new StringBuffer(500); // User defined initial size
sb.append("SELECT * from Member WHERE ");
sb.append("PS_INTRESTS LIKE '%Tennis%' ");
sb.append("OR PS_INTRESTS LIKE '%Swimming%' ");
sb.append("OR PS_INTRESTS LIKE '%Hockey%' ");
//Use sb.toString() to convert to a String.
how about
select
a.person_id,
a.name_first,
a.name_last
from
person a,
(select
p_i.person_id,
i.interest_desc
from
person_interest p_i,
interest i
where i.interest_id = p_i.interest_id) ints_with_desc
where a.person_id = ints_with_desc.person_id
and ints_with_desc.interest_desc in ('SWIMMING', 'BOWLING')
group by
a.person_id,
a.name_first,
a.name_last
having count(1) = 2
you can dynamically code this so that the following line
and ints_with_desc.interest_desc in ('SWIMMING', 'BOWLING')
and this line
having count(1) = 2
are dynamically updated depending on what you are after - ie use PreparedStatement and build the 'in' statement and set the 'count = ' on the fly.
sooner or later i knew the work around i did was going to bite me in the butt. I've began working on a mobile version of my application now the mobile version adds semi colons at the end after each hobbie and writes it to the db. Both the web version and mobile use the same back end code. So retreiving the hobbie fields is then an issue.
and phawdon, hope you don't mind me asking a few questions with your solution... Wouldn't i have the same problem as i did with the work around? i.e. if i write the values with semi colons in one field then retreiving this would be a problem?
