[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • Last Modified:

ColdFusion List/Query Problem

I am in the process of developing a new backend for a series of E-commerce websites, all which presently run off of separate databases. As the websites begin to share products I am looking at a way to develop one database where I am able to just "tag" products to appear on multiple websites, therefore reducing having to do duplicate data entry. In a table named "websites" I set up one record for each website, defining color schemes, META tags, and other website-specific information.

With this table I am able to copy the source files for the backend into multiple IIS websites, changing only one value in the application.cfm file (website_id) to point to a record number in "websites".

A table named "products" contains product SKUs, pricing, and the like. In theory I would like to be able to set up a field in this table where I would insert a list such as "1,3,7", which would relate to the website ID numbers from "websites" that the product in question should appear on. This however, is where I run into problems; I can't seem to write a query that pulls the proper products.

For example, I have set up a website called "Test Website" in the "websites" table; the record number for this entry is "2". Several products in the "products" table are tagged with "2,7,10", meaning they are to show up on websites with record numbers 2, 7, or 10. Using the LIKE operator I was able to pull all the products tagged "2,7,10" because clearly there is a "2" in there... but as you are aware, it's also pulling products that are tagged like "21,33" and "12,15,17", because of the "2"s.

Any suggestions?
0
villagelighthouse_llc
Asked:
villagelighthouse_llc
1 Solution
 
MauseCommented:
Hi

Make an other table websites_products(website_id,product_id)
it will look like this:
1,1 (website 1, product 1)
1,3 (website 1, product 3)
1,7 (website 1, product 7)
2,1 (website 2, product 1)
2,5 (website 2, product 5)
2,6 (website 2, product 6)

so your query will look something like this:
select *
form websites, products, websites_products
where website_ID = #website_ID#
and  websites_products.website_id =  websites.website_ID
and  websites_products.product_id = products.products_id

or use a listfind function of you DB

Mause
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now