complex search using mysql-- help!

[danielsikes]

Hi.

I am trying to perform a "process of elimination" type of search using MySql.

I have a table with a lot of values that people could search for- and currently if they search for 1 value it works great. But if they search for lets say "value1 value2" it doesn't work at all because there is no entries with those values.

So to solve that problem- i explode based off of spaces and then I have an array of different values so it would be like this

arrayVal[0] = value1
arrayVal[1] = value2

etc

My question is how can I perform a query that selects all of the possible matches to "value1" store them then search "value2" against the results from "value1". I hope this makes sense.

Any help would be greatly appreciated!

Thanks.

[hericles]

If I understand the question you aren't looking for the results that could be value 1 OR value but those results that include value 1 and 2. If you have got the values in an array you can append them all to one SQL statement using the AND clause. E.g.
SELECT * from some table WHERE col = value1 AND col2 = value2 etc...

The result will be the rows that match all of the criteria.
Otherwise you can extract out the rows matching value 1 and store them in a dataTable and run filtering queries against that using LINQ. This might be better depending on exactly what you need to filter and the structure of the data.

[danielsikes]

@hericles

Thanks for the reply.

Actually I should probably be more descriptive with my situation.

The db structure is like this:

it is a inline cross referencing table designed for searching...

so i have "search_id" "group_id" "field" "value"

and it works where the user can search off of entry of the value field.

so for example it would be:
(this is just an example of my db search table structure)

"search_id"=>1 "group_id"=> 123456, "field"=> "first_name", "value"=>"somebody"
"search_id"=>2 "group_id"=> 123456, "field"=> "last_name", "value"=>"special"
"search_id"=>3 "group_id"=> 123456, "field"=> "phone_num", "value"=>"123-456-7890"

"search_id"=>1 "group_id"=> 789012, "field"=> "first_name", "value"=>"somebody"
"search_id"=>2 "group_id"=> 789012, "field"=> "last_name", "value"=>"else"
"search_id"=>3 "group_id"=> 789012, "field"=> "phone_num", "value"=>"123-555-1234"

and so on and so forth with various groups like that that are cross referenced based off of "group_id"

The user can search the "value" field in my database. Iguess the reason I designed it that way was because I wanted them to just type anything and get any result.
The query returns the group_id that matched their search "value" and then I just list the information assoicated with that group_id.

What i need to be able to do is search like this:

Notice how somebody is listed as the first_name 2 times? Well there are a lot of similiar entries etc that a user can search through.

If they just typed in "somebody" then it would return the group_id's in an array with the 2 unique group_ids.

If they typed in "somebody special" then it would need to know that the search was only referring to the group_id of 123456.

and so I couldn't search using sql like this "SELECT * FROM search WHERE value = 'somebody' and 'special' because the value isn't equal to somebody and special it is equal to one OR the other one. I tried using OR as well, and it doesn't do what I need either.

Does this make any more sense?

Thanks so much for your help!

-Dan

[smantscheff]

Depending on the size of your database it might be feasible to use concat:

select group_id from search where '%somebody%' like group_concat(value, ' ') and '%special%' like group_concat(value, ' ') group by group_id;

[mehfooz]

--The function should take as parameter the search value
--then parsed as argument to the function such as below
--Infact since there may be one or more group_id of that value, you should use RECORD such as below;

CREATE OR REPLACE FUNCTION searchFunction(val TEXT)RETURNS TEXT AS
$$
    DECLARE
        searchRec RECORD;
        result TEXT='';
    BEGIN 
        FOR searchRec IN SELECT * FROM tblSearch WHERE value=val
        LOOP
            result=result||searchRec.group_id||'\n';
        END LOOP;
        RETURN result;
    END;
$$LANGUAGE plpgsql;