Solved

ColdFusion List/Query Problem

Posted on 2004-10-12
3
143 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
3 Comments
 
LVL 10

Accepted Solution

by:
Mause earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction In this tutorial, I'll explain how to create an animated progress meter in a wireframe prototype developed using Axure RP 7.0 - a leading prototyping tool for designing web sites and software. (For more information about Axure and gett…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
The purpose of this video is to demonstrate how to create a Printer Friendly PDF on a WordPress Page. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome Screenshot” Google Chrome Extension, and SmallPDF.com Log…
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. 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 : Go t…

929 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now