?
Solved

ColdFusion List/Query Problem

Posted on 2004-10-12
3
Medium Priority
?
174 Views
Last Modified: 2013-12-24
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
Comment
Question by:villagelighthouse_llc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 10

Accepted Solution

by:
Mause earned 2000 total points
ID: 12295242
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure (http://www.axure.com/). It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
This guide will walk you through the essential considerations and tech stack for building scalable websites. Know how to grow your business the smart way!
The purpose of this video is to demonstrate how to Import and export files in WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Click on Too…
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question