I've got a table called "plants" which, up-until-recently, was being searched by category using the following SQL (and using the CategoryID field):
SELECT plants.Latin_Name, Size.ekm_ID, plants.Common_Name, plants.Short_Description, plants.Thumb_URL, plants.CategoryID, plants.ProductID, Size.Size, Size.Price, Size.MostPopular FROM plants INNER JOIN Size ON plants.CategoryID = Size.CategoryID AND plants.ProductID = Size.ProductID WHERE (plants.CategoryID = @CategoryID) AND (Size.MostPopular = 'True') AND (Size.Availability = 'True') ORDER BY plants.Latin_Name
I now want to offer users two ways of searching for the plant they want - I need to add another set of categories which plants can be searched by. This is what I mean...
Category Set One (Traditional Categories) - Aquatics, Acers, Climbers, Conifers, Exotics, Fruit, Grasses/Bamboo, Heathers, Hedging, Herbs, Perennials, Rhododendrons, Roses, Shrubs, Topiary
Category Set Two (More Recent Way of Grouping Plants) - Roses that grow on you, hedge your bets, sun & shady characters, social climbers, shady characters, sun shine boys, the fruitful good life, herbs 'what you got cooking, sensory sensations, silent whispoers, architectural plants, wildlife lovers, rockery rockers, trees for rockery & patio, trees for gardens, trees for smallholdings & farms, perennial penchant, groundcover groundhogs
Every plant we sell appears in 1 category from category set one and one or two categories from category set 2.
The relevant fields in the plant table are: CategoryID (traditional categories), NewCategoryID1 (1st category from category set 2), NewCategoryID2 (2nd category from category set 2 if applicable)
I have a "categorysearch.aspx" page currently set up to deal with category set 1 and I'd like to use this same page to display the results of a category set 2 search - i.e. I'd like the SQL to search for
WHERE CategoryID = @CategoryID OR NewCategoryID = @NewCategoryID OR NewCategoryID2 = @NewCategoryID2
where @CategoryID, @NewCategoryID, @NewCategoryID2 are appended as a querystring
I also need to keep the other parts of my SQL query after the "WHERE" - i.e. AND (Size.MostPopular = 'True') AND (Size.Availability = 'True') ORDER BY plants.Latin_Name
I think this might just be a case of putting brackets in the right places to make it work, however, I'm having a lot of trouble with it so any help you could offer me would be really appreciated.
WHERE (CategoryID = @CategoryID OR NewCategoryID = @NewCategoryID OR NewCategoryID2 = @NewCategoryID2) AND (Size.MostPopular = 'True') AND (Size.Availability = 'True') ORDER BY plants.Latin_Name
Nice with the blossoming! If I were you, I'd stop doing it that way and instead writing it in code-behind - it's much simpler!
You can see how this is done, under the ******* below. Above it, I put some example code for getting the "snippets" in the right place. Note that there is a very fast full-text search method there too (using len instead of LIKE) - it seems like you have a lot of code, so it might come in handy.
Let me know if I'm not clear enough, but as I said, this is just example code.
Ifordet.Length > 1 Then '"ordet" simply means "the word" in Swedish, and in my code (not presented here) I loop it to parse several words, quotes etc
If ordet.StartsWith("-") Then
icke =" NOT " 'becomes AND NOT (...)
ordet = ordet.Remove(0, 1)'removes minus sign End If If ordet.Length > 1 Then 'the sql string is being built If OnlynamesearchCheckBox.Checked = True Then 'search only the name
tillagg += "AND " & icke & " len(aname) > len(Replace(aname, '" & ordet & "', '')) " 'this is fast!Else
tillagg +="AND " & icke & " len(aname+ber+kontaktnamn) > len(Replace(aname+ber+kontaktnamn, '" & ordet & "', '')) " 'search all text fields End If End If
'The string itself, the "answer" to your question:
ResultDataSource.SelectCommand ="SELECT * FROM Mytable " & _ "WHERE shown = 1 " & tillagg & "ORDER BY sortering"
Coding is a nine-to-five job: Nine PM to Five AM.