Gossamer Forum
Home : Gossamer Threads Inc. : Custom Modification Jobs :

DBManSQL AdvancedSearch

Quote Reply
DBManSQL AdvancedSearch
Hi,

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.

Example here:

http://www.shop-netz.de/...roduktname&mh=15

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

(like: .../db.cgi?keyword=yamaha+dvd+brenner&db=....)

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

Regards,
Manu

Shopping Portal Shop-Netz.de® | Partnerprogramme | Flugreisen & Billigflüge | KESTERMEDIA e.K. | European Affiliate Marketing Forum.

Last edited by:

ManuGermany: Jan 14, 2003, 11:58 PM
Subject Author Views Date
Post DBManSQL AdvancedSearch ManuGermany 2342 Jan 14, 2003, 11:54 PM