what I need is a global or a plugin for DBMan SQL which allows to do a better database-search.
This is what I have:
A main table (products) with the columns:
[ID(INT)] [StoreID(INT)] [productID(INT)] [productname(VARCHAR)] [description(VARCHAR)] [produktURL(VARCHAR)] [imageURL(VARCHAR)] [price(VARCHAR)] [LastUpdate(DATE)] [brand(VARCHAR)]
In this table there are upto 1.500.000 products stored.
Then I have a second table (stores) from which I fetch the store-data for every single search-result with the columns:
[ID(INT)] [StoreID(VARCHAR)] [StoreName(VARCHAR)] [LogoURL(VARCHAR)] [LinksSQL_ID(VARCHAR)] [AffiliURL(VARCHAR)] [AffiliImgURL(VARCHAR)]
At the moment I can search my database only by a single word (this is what DBMan SQL can do) and display the search-results.
And this is what I want to do (to have):
Search the database via single TEXT-INPUT-FIELD for up to 3 or 4 keywords
The search results should be ordered by relevance. Therefore I want to be able to define (within the script-code would be ok) the search weight for the columns to be searched.
In my case I need Column :
[productname(VARCHAR)] = Search Weight=2
[brand(VARCHAR)] = Search Weight=2
[description(VARCHAR)] = Search Weight=1
Then the search results should be ordered (dependingon the search weights) by the following:
results where query is:
- keyword1 AND keyword2 AND keyword3 (... of course if keyword 2,3 exists)
- (keyword1 AND keyword2) OR (keyword1 AND keyword3) OR (keyword2 AND keyword3)
- keyword1 OR keyword2 OR keyword3
- keyword1% OR keyword2% OR keyword3%
- %keyword1% OR %keyword2% OR %keyword3%
(Or something like that what makes sense to order by relevance).
This is what I need first!
Secondly it would be great if I could optional search for things above plus entering a price minimum and a price maximum (FORM TEXT FILEDS: [keywords],[price-min],[price-max]. The prices are stored in the VARCHAR-field in the format 00.00 (example : 223.95)
If anyone could do this, please let me know!
P.S.: Of course should the output (search results) work with Span-Pages and the mh=xx (max hits)-tag
Shopping Portal Shop-Netz.de® | Partnerprogramme | Flugreisen & Billigflüge | KESTERMEDIA e.K. | European Affiliate Marketing Forum.