SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Wizard
    Join Date
    Nov 2000
    Location
    Chico, Ca
    Posts
    1,125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Full-Text Search Paging (SQL - Server)

    I'm trying to page a full-text Search: here is my T-SQL:

    Code:
     SELECT 
     	Products.ProductID,
     	Products.Name,
     	Categories.CategoryID,
     	Categories.Name as CatName,
     	Products.Description, 
     	Products.ImageName,
     	KEY_TBL.RANK
     	FROM Categories
     	Inner
     		join Category_Product
     		on Categories.CategoryID = Category_Product.CategoryID
     	inner join Products 
     		on Category_Product.ProductID = Products.ProductID
     	INNER JOIN FREETEXTTABLE(dbo.Products, *, @Keywords) AS
     	KEY_TBL ON Products.ProductID = KEY_TBL.[KEY] order by RANK, Categories.CategoryID DESC
    Here is how I've done without full-text paging:

    Code:
     
     if(@CategoryID != -1 and @Count = 1 )
     Begin
     	-- now return the results:
     	select count(*) AS SearchCount
     
     	from Categories
     	inner
     		join Category_Product  
     		on Categories.CategoryID = Category_Product.CategoryID
     	inner
     		join Products
     		on Category_Product.ProductID  = Products.ProductID 
     
     	where Category_Product.CategoryID =  @CategoryID 
     	
     	set rowcount 0 
     
     End
     
     if(@CategoryID != -1 and @Count = 0)
     Begin
     	--find the first record to return
     	select @id = Products.ProductID
     	from Categories
     	inner
     		join Category_Product  
     		on Categories.CategoryID = Category_Product.CategoryID
     	inner
     		join Products
     		on Category_Product.ProductID  = Products.ProductID 
     
     	where Category_Product.CategoryID =  @CategoryID order by Categories.Name ASC
     
     	-- find out how many rows to return, and set the rowcount:
     	--set @End = @Paging + 
     	set rowcount @Increment
     	
     	-- now return the results:
     	select 
     	Products.ProductID,
     	Products.Name,
     	Products.Description,
     	Products.ImageName,
     	Categories.Name as CatName,
     	Categories.CategoryID
     
     	from Categories
     	inner
     		join Category_Product  
     		on Categories.CategoryID = Category_Product.CategoryID
     	inner
     		join Products
     		on Category_Product.ProductID  = Products.ProductID 
     
     	where Category_Product.CategoryID =  @CategoryID AND Products.ProductID >= @id  order by Categories.Name ASC
     	
     	set rowcount 0 
     
     End
    When I use the above method, it returns the "Id@" variable, instead of the resultset.

    Has anyone worked with Full-Text Searching?

    Thanks,

    Chuck
    "Happiness doesn't find you, you find happiness" -- Unknown
    www.chuckknows.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    nope, i haven't worked with Full-Text Searching

    i just thought i'd mention that your web site doesn't work in IE6 on windows (which is the browser used by, what, maybe 85-90% of the people on the internet)

    i get a file download prompt that says "Some files can harm your computer. If the file information below looks suspicious, or you do not fully trust the source, do not open or save the file"

    this is likely to scare the #%$@ out of most people

    thought you should know
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    Nov 2000
    Location
    Chico, Ca
    Posts
    1,125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937

    i just thought i'd mention that your web site doesn't work in IE6 on windows (which is the browser used by, what, maybe 85-90% of the people on the internet)
    hmmm, I viewed it in IE6 and it looks fine. Can you describe the problem a little more ? What res you are at?

    Quote Originally Posted by r937
    i get a file download prompt that says "Some files can harm your computer. If the file information below looks suspicious, or you do not fully trust the source, do not open or save the file"

    this is likely to scare the #%$@ out of most people

    thought you should know
    I noticed the same thing. Any idea how to fix it?
    "Happiness doesn't find you, you find happiness" -- Unknown
    www.chuckknows.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i tried your site at http://web-sniffer.net/ and everything looks okay there

    maybe it's the mime type you're serving it as? i couldn't see that in the headers

    i've heard that internet explorer has trouble if a document is sent as xml mime type

    maybe this is a related issue?

    your site looks okay in firefox, but how many people use that, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •