Solved

ColdFusion List/Query Problem

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
The purpose of this video is to demonstrate how to update a WordPress Site’s version. WordPress releases new versions of its software frequently and it is important to update frequently in order to keep your site secure, and to get new WordPress…
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…

688 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