Hello Everyone,

I'm in the process of taking a flat site with a lot of data to a site that will display the data dynamically from a MYSQL database using PHP.

When I was pulling the data off the website and into the database, I gave each item a value for location on the website - in fact, I gave it 2. One value was PAGE (corresponding to the html page) and the other was TABLE (corresponding to the table on that page). For example - Item 1 might have a PAGE value of 'buffing' and a TABLE value of 3. I thought this would be a good idea so that when I started making the recordsets for each dynamic table, I could simply call them up... SELECT <fields> WHERE PAGE ='buffing' and TABLE='1'.

However, this was not a good method in retrospect since I have now multiple records of the same items (but with different locations) which will be a pain for updating prices and if customers want to perhaps search and sort items later on. It just does not seem efficient to have multiple records of the same thing, just because they appear multiple times on the site. So I thought of having one column instead of 2, and listing the locationS that the items are listed on in that. IE buffing1, sanding3, etc. Then I could call the values with SELECT <fields> WHERE LOCATIONS LIKE %Buffing1%. I've never used a LIKE command, is this a case in which I would want to apply it?

Secondly, I had read something (but can't find it any longer) awhile back about structuring a database like this with two separate tables. One would contain the locations and then I would need to JOIN them later. Apparently something about it being faster/leaner. But I can't understand why. Could someone please explain the benefit of this (if there is one) and if it looks like it might suit this application well? Or if I should just go with my current path in the paragraph above?

Note: The table has about 1500 or so records and about 40 fields. There are about 75 site pages.

Sincerely,
Colin

Note: There is some sample data if you click through to the products at www.abraREMOVETHISmetal.com IE Abrasives -> Fine Grinding & Polishing -> Belts.

Yes, you seem to have done it wrong. But no need to panic.

Every "item" (whatever it is -- mine are jigsaw puzzles) needs to have a unique identifying code ("product code", whatever). In your case you need to add this as a (new?) field. Then you need to remove duplicates.

Then, as you say, you need an index from "page" to "the items on this page" which might be a separate table. You need this to generate the text of each page.

It is probably a good idea for each item to have a "home page", which is a single value being the principal page on which you can find it. You need this to generate a link to where the item can be seen when you provide some sort of search function.

hth