| Author |
 Topic  |
|
Shaggy
Support Moderator
Ireland
6780 Posts |
 Posted - 14 September 2005 : 11:13:37
|
Ay, that's where it's going to require a few tests. Also, you should plan ahead to be able to expand those lists upon suggestion from your members.
< |
 Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
 |
|
|
zinpin
Junior Member
Australia
202 Posts |
Posted - 19 September 2005 : 05:51:42
|
Shaggy I've come across a problem I hope you can give me an idea on how to solve.
A number of fields in the table are storing a number of answers in a comma delimited text. Now I need to somehow be able to search those fields within a query, any ideas on the best way to do that. If you have any code examples would be great.< |
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 19 September 2005 : 07:20:25
|
Again, it's not the prettiest solution, for each selected item in our search form, I've added to the WHERE clause of the SQL pulling the records from the database something along the lines of:... AND '"&selecteditem&"' IN (MY_FIELD) Told you it wasn't pretty!
< |
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
zinpin
Junior Member
Australia
202 Posts |
Posted - 19 September 2005 : 07:28:10
|
he he that's OK if it works what more do you want? 
wonder though if it works the same if "selecteditem" is a comma del list: ie "01, 22, 23, 18, 13"
edit... sorry mate most probably it does... he he what am I thinking. sorry about that
it's been a long day today  < |
Edited by - zinpin on 19 September 2005 07:30:53 |
|
|
|
StephenD
Senior Member
Australia
1044 Posts |
Posted - 19 September 2005 : 07:33:48
|
quote:
Originally posted by zinpin
wonder though if it works the same if "selecteditem" is a comma del list: ie "01, 22, 23, 18, 13"
Hey Zinpin, hope that list isn't the duration measured in hours of your past relationships!
Sorry... couldn't resist. Fun aside, I'm following your progress here with the code and hope it works out mate.< |
|
|
|
zinpin
Junior Member
Australia
202 Posts |
Posted - 19 September 2005 : 07:36:20
|
BTW are you working with access or sql?
he he Stephen... good pot shot 
Hopefully soon might have a mod for other people too....< |
Edited by - zinpin on 19 September 2005 07:40:34 |
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 19 September 2005 : 07:57:43
|
quote:
Originally posted by zinpin
he he that's OK if it works what more do you want?
wonder though if it works the same if "selecteditem" is a comma del list: ie "01, 22, 23, 18, 13"
edit... sorry mate most probably it does... he he what am I thinking. sorry about that
Actually, it won't as that will be a string, not a list.
We're running off the latest version of MySQL.
< |
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
zinpin
Junior Member
Australia
202 Posts |
Posted - 19 September 2005 : 08:08:30
|
Oops, so let me see if I've got this right, you convert the string over to an array and then cycle through that to see if any values match data in the field?
< |
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 19 September 2005 : 08:18:54
|
Exactly
There are better ways ofdoing it, I just haven't had the time to implement and test them yet.
< |
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
zinpin
Junior Member
Australia
202 Posts |
Posted - 19 September 2005 : 08:35:45
|
Just taking it one step further and hope I'm still with you on this, this would mean that a large number of records would have to be retrieved from the database first and then run the list code so that it can disgard the ones that are not exactly matching. Then build a second list from these then finally run a new select statement just to bring up the final selection. If this is the case it sounds like a slow process. I'm only guessing but it sounds slower than having all the fields as individual values in the initial select statement?
< |
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 19 September 2005 : 08:52:33
|
No, you can do it all in one, single statement. If your initial statement is:
SELECT [FIELDS] FROM MEMBERS WHERE [CRITERIA]
Then just loop through the items selected by the visitor an append them on to that statement so you end up with something like:
SELECT [FIELDS] FROM MEMBERS WHERE [CRITERIA] AND "&selecteditem1&" IN (MY_FIELD) AND "&selecteditem2&" in (MY_FIELD)
< |
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
zinpin
Junior Member
Australia
202 Posts |
Posted - 19 September 2005 : 09:00:45
|
Got it :) Thanks Shaggy.
Ive got about 2 fields with about 20 answers in each one and a few others with about 5.
Ill try this method and time it. I will also insert the actual fields in the table and try the normal way too and see which one is faster. I think it will be interesting.
If anyone on the other hand has done something like this please let us know as this will save me about half a days work and about 10 coffees ;)< |
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 19 September 2005 : 09:10:26
|
What I plan on doing when I have the time is creating two new tables (lets work with interests for now), the first one (INTERESTS) to hold the complete list of interests each with a unique identifier. The second table (MEMBER_INTEREST) would then have two fields, one for the MEMBER_ID and one for the INEREST_ID. Instead of an array of the items the visitor selected when searching, you'd just need a comma seperated list now. Working that way, the SQL staement would now look like this:
SELECT M.[FIELDS] FROM MEMBERS M,MEMBER_INERESTS MI WHERE [CRITERIA] AND MI.MEMBER_ID=M.MEMBER_ID AND MI.INTEREST_ID IN ("&selecteditems&")
That's all just in my head at the moment, though 
< |
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
zinpin
Junior Member
Australia
202 Posts |
Posted - 19 September 2005 : 09:21:54
|
It sounds to me that we're on the same track trying to accomplish the same thing... for now though I think it's time for me to shut down for the night :)
Once again thanks mate and chat to you soon.< |
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 19 September 2005 : 09:23:31
|
No worries, man, good to see your project moving forward. Bear in mind though that when we eventually launch Woo.ie in Australia, we're going to be running all our competitors out of town!
< |
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
Topic |
|
Personals Mod
