Complicated search query

[Puiu]

I have a database with 2 tables: Images and Keywords.
In the Keywords table i have the following data:

IdKeyword_____IdImage_________Keyword
------------------------------------------------
1____________10__________forest wood elf green
2____________11__________city steel car yellow subway
3____________12__________bird computer blue sky

On my website the user enters in a textbox these words: forest city bird
The select query would be something like this:

Select i.* from Images i, keywords k where i.idimage=k.idimage and k.keyword like forest city bird

This query doesnt return anything. I know why, but I

 

[Nerseus]

If I interpret your sample data right, you have keywords all grouped together, so that one row of data has the value "forest wood elf green"? That seems like a bad design.

But you can still make your query work, it just wont be as effecient or as accurate. Youll need to parse the text entered by the user into separate strings and then build multiple LIKE statements.

So if they enter forest city bird, you have to break that into forest, city and bird. Heres some sample code:

string fullSearchString = "forest city bird";
string[] searchStrings = fullSearchString.Split(" ");

Youll probably want something more robust than "Split", to remove two spaces and etc. - I leave that up to you.

Now build your WHERE clause to look like this:

...WHERE i.idimage=k.idimage AND (k.keyword like %forest% and k.keyword LIKE %city% AND k.keyword LIKE %bird%)

That would use an AND join - all keywords must be present in a single file. If you want an OR join, change the ANDs to ORs inside the parens above.

The ineffeciency comes from doing a LIKE with a wildcard on the front, as in %forest%. Its much more efficient to just put one on the end, as in forest%. Or, if you require exact matching, then put the keywords in the table one at a time. If thats possible, just ask and we can help.

-ner

 

[kejpa]

I have a database with 2 tables: Images and Keywords.
In the Keywords table i have the following data:

IdKeyword_____IdImage_________Keyword
------------------------------------------------
1____________10__________forest wood elf green
2____________11__________city steel car yellow subway
3____________12__________bird computer blue sky

From my point of veiw IdKeyword looks like an Identity field , right? Not too sure you need it, IdImage and Keyword would make a good primary key.
The structure you have is right just keep one keyword in each record.

IdImage______Keyword
------------------------
10__________forest
10__________wood
10__________elf
11__________city
11__________steel
11__________car
11__________yellow
11__________subway
12__________bird
12__________computer
12__________blue
12__________sky

Which will be able to give you a query like...

Select i.* from Images i 
inner join Keywords k on k.IdImage=i.IdImage 
where k.Keyword in (city, bird, forest)

 

[Puiu]

Thank you people, I

 

[kejpa]

Disk space is cheap. Wildcard table scans are expensive.
Id go for one keyword per record, the only overhead storage is the imageId.

Your where clause should be m.[description] like bird%
Youre missing the quotes around the string to search for.

Another thing...
Avoid using sp_ as prefix to your stored procedures. If youre unlucky enough M$ comes up with a system stored procedure with the same name and yours will never run, not even when prefixing it with your database name

 

[Puiu]

Thank you for the advice Kejpa!
If i use SP_Search m.[description] like bird% i get the following error: Line 1: Incorrect syntax near ..

If i use SP_Search m.[description] like bird% i get the error:
Line 1: Incorrect syntax near bird.

I know im close

 

[Nerseus]

Try:
SP_Search m.[description] like bird%

You need to double up the single quotes inside of quotes. To test future strings, use SELECT on your string to show what it is. For example:

Create Proc SP_Search
@WClause varchar(1000)

as

DECLARE @sql varchar(1000)

SET @sql = select distinct m.idmedia, m.idcampaign, m.[description] from Media m, keywords k where m.idmedia=k.idmedia AND ( + @WClause + ) 

SELECT @sql
-- exec (@sql)

I commented out the exec line so you can test the string.

I suspect youre still going to have a problem since your "exec" line is wrapping @WClause with single quotes.

I cant say this loud enough, but [highlight]you really, REALLY should be using parameters for dynamic SQL in SQL Server[/highlight]. Look up the proc sp_executesql for a start. It will give samples on how to do dynamic SQL properly.

It may be a bit of a learning curve, but not more than an hour or two to get it working. Thats certainly faster than debugging a custom dynamic SQL solution, which is also more likely to be buggy.

-ner

 

[Puiu]

Ive got it!!!
-----------------------------------
create proc SP_Search
@WClause varchar(1000)

as
declare @SQL varchar(1000)

set @SQL=select distinct m.idmedia, m.idcampaign, m.[description] from Media m, keywords k where m.idmedia=k.idmedia AND (

set @SQL =@SQL + @WClause + )

--Select @SQL
exec (@SQL)
-------------------------

Sorry Nerseus, i didnt quite understand what you ment by you "really, REALLY should be using parameters for dynamic SQL in SQL Server.", i thought that @WClause was my parameter, but you probably ment what i did now...anyway thank you for the answer again!
Im happy now!

 

[PlausiblyDamp]

sp_executesql. You really should avoid concatenating strings to generate SQL, regardless of where it is done you introduce the potential for exploits.