Many thanks, Will. Obvious now I look at it again, duh me.
Anyhoo, I had this (probably dumb) idea of combining sever fulltext search
query results into one table, turning into a lot more work than I expected
(+ learning curve).
Maybe there is a better way of going about this:-
Scenario - Cart
Structure
Main Categories -> Sub Categories => Products => Product Options
I want to search in fields from two tables at a time.
So it will output two "groupings" :-
1. List of product/options
2. Categories (main and sub)
Currently, I've added all four tables to the FULLTEXT catalog.
I'm running 4 queries on that from CF, each one looks like this:
<cfquery datasource="#application.dsn#" name="stockTable">
SELECT TOP 100 PERCENT Search_Table.RANK, dbo.list_stock_shop.catMainID,
dbo.list_stock_shop.catmainDisplay, dbo.list_stock_shop.openClosed,
dbo.list_stock_shop.catMainDescr,
dbo.list_stock_shop.catMainFullDescr, dbo.list_stock_shop.catMainPhoto,
dbo.list_stock_shop.catSubID,
dbo.list_stock_shop.catSubDisplay,
dbo.list_stock_shop.grid, dbo.list_stock_shop.catSubDescr,
dbo.list_stock_shop.catSubFullDescr,
dbo.list_stock_shop.catSubPhoto,
dbo.list_stock_shop.stockID, dbo.list_stock_shop.created,
dbo.list_stock_shop.stockDisplay,
dbo.list_stock_shop.stockTitle,
dbo.list_stock_shop.taxRate, dbo.list_stock_shop.stockDescr,
dbo.list_stock_shop.stockDescrShort,
dbo.list_stock_shop.taxExempt,
dbo.list_stock_shop.delivery, dbo.list_stock_shop.navAdmin,
dbo.list_stock_shop.stockItemID,
dbo.list_stock_shop.stockItemDisplay,
dbo.list_stock_shop.cost, dbo.list_stock_shop.price,
dbo.list_stock_shop.salePrice,
dbo.list_stock_shop.itemTitle,
dbo.list_stock_shop.itemDescr, dbo.list_stock_shop.stockItemPhoto,
dbo.list_stock_shop.inStock,
dbo.list_stock_shop.onOrder,
dbo.list_stock_shop.allocated, dbo.list_stock_shop.soldQuan,
dbo.list_stock_shop.minQuan,
dbo.list_stock_shop.maxQuan,
dbo.list_stock_shop.reorderQuan, dbo.list_stock_shop.saleFrom,
dbo.list_stock_shop.saleTo,
dbo.list_stock_shop.projected
FROM FREETEXTTABLE(tbl_stock, *, '#myCleanSearch#', 1000)
Search_Table INNER JOIN
dbo.list_stock_shop ON Search_Table.[KEY] =
dbo.list_stock_shop.stockID
ORDER BY Search_Table.RANK DESC
</cfquery>
Ignore the o/d on the field list, I'll cut to minimum when I'm done.
My solution as it is will work, but it feels very ugly and process heavy.
What I wonder is, can the search be run on more than one table into the
catalog at one time? Am I going about this the right way?
Jenny
-----Original Message-----
From: William Seiter [mailto:[email protected]]
Sent: 23 April 2011 17:51
To: cf-talk
Subject: RE: Problem Using QueryNew
You are using a simple cfloop from to, instead of a cfquery or a cfloop
query.
Because of this, when you call "#stockTable.stockTitle#", it defaults to the
first line of the query.
Either change your loop to a cfloop query="stockTable">
Or change your data call to "#stockTable.stockTitle[R]#"
William
-----Original Message-----
From: Jenny Gavin-Wear [mailto:[email protected]]
Sent: Saturday, April 23, 2011 9:45 AM
To: cf-talk
Subject: Problem Using QueryNew
My first time using created queries, I can't understand why this is putting
the same value on every row.
<cfset stock = QueryNew("StockTitle, Varchar")>
<cfoutput>
<cfloop from="1" to="#stockTable.recordcount#" index="R">
<cfset newrow = QueryAddRow(stock,1)>
<cfset temp = QuerySetCell(Stock, "stockTitle", "#stockTable.stockTitle#",
R)>
</cfloop>
</cfoutput>
<cfoutput query="stock">
#stock.stockTitle#<br />
</cfoutput>
No virus found in this outgoing message.
Checked by AVG - www.avg.com
Version: 9.0.894 / Virus Database: 271.1.1/3591 - Release Date: 04/23/11
07:36:00
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:343918
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
