Solved

ColdFusion List/Query Problem

Posted on 2004-10-12
3
162 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 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
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 integrate Mailchimp with Facebook. This will be demonstrated using a Windows 8 PC. Mailchimp and Facebook will be used. Log into your Mailchimp account. : Click on your name. Go to Account Setti…
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…

730 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