Results 1 to 4 of 4
August 27th, 2003, 08:37 PM #1
I need a little help setting up some tables. Here is what I want to do... First off you should know I am a little new at PHP... anyway, for my coupon site I need to set up my database so that I can view my different merchants based on categories. My problem is that if I set up a table with the merchants I can only list that merchant for one category. But, if I set up a table by category, then I will have to enter the merchant in each category. I'm thinking there is a better way but don't know how to do it. I need a point in the right direction.
August 27th, 2003, 09:06 PM #2
So you want to be able to have many merchants in a category, and each merchant can be in more than one category?
You need a merchant table and a category table and a third table to link them.
Say you have two simple tables, one for the merchants, one for the categories.
Now if you were going to have just one category per merchant, you could add a third column to the merchant_table as you described.
To get many-to-many relationship between merchants and categories, you need a new table.
This table represents the relationships between merchants and categories. You have the ultimate flexiblity in that you can have merchants that are in many categories.
Say your merchant and category tables have the following data rows:
You'd fill your merchant_category table with the relationshions between merchants and categories using just their id_no's:
1 1 (merchant1 in category1)
2 1 (merchant2 in category2)
1 2 (merchant1 in category2)
2 3 (merchant2 in category3)
The sql to show the data looks like this:
select merchant_name, category_name
from merchant_table, category_table, merchant_category
where merchant_table.id_no = merchant_category.merchant_id
and category_table.id_no = merchant_category.category_id
order by category_name
the output will look like this:
Then you can run through the data and display it. Create a new header whenever you get a new category
That's probably a lot to swallow. I hope it helps.
August 28th, 2003, 06:26 AM #3
- Join Date
- January 18th, 2005
Nice post Scott.
What Scott is describing is a relational database. Here is a paper that is pretty decent on the subject.
There's a big learning curve but you do gain a lot of flexibility and performance.
August 28th, 2003, 02:35 PM #4
Excellent reply!!! MANY THANKS.
Watch out world, here I come.
By Justdoit in forum Midnight Cafe'Replies: 4Last Post: July 1st, 2013, 05:50 PM
By mellie in forum Minnesota Affiliate TaxReplies: 4Last Post: April 28th, 2009, 04:39 PM
By GravityFed in forum AvantLink -AVReplies: 2Last Post: January 8th, 2009, 06:25 PM
By rz44 in forum WebMerge (Fourthworld.com)Replies: 2Last Post: January 6th, 2005, 10:04 AM