Results 1 to 10 of 10
  1. #1
    Full Member
    Join Date
    January 18th, 2005
    Posts
    362
    I got a feed and want to seperate categories into another table.. what's an easy way to do this automatically where the category is given an id number that is used across all other related tables? There is no category id number currently.. just a category name in each product row.

  2. #2
    Newbie
    Join Date
    January 18th, 2005
    Posts
    13
    Maybe I can be of assistance.

    Basically what you'd want to do is separate the category from the product by creating a table just for categories.

    Example:
    id | Category
    1 | Clothing
    2 | Electronics

    Inside of the product table the category field would be an integer type field and to reference the category, you would use the category id instead of the field.

    Example:
    id | Product Name | Category
    1 | Red Dress | 1
    2 | Color TV | 2

    If you let me know which database application you currently use, I can tell you exactly how to go about it automatically.

    Jerome Carty
    Chief Executive Officer
    Loucon Corp

  3. #3
    Full Member
    Join Date
    January 18th, 2005
    Posts
    362
    yes something like that.

    When it comes to sorting using mysql instead of php I figure it would be easier if categories were in another table with an ID to reference the product table rather than pulling the category names from the product table(which are not unique but would be unique in a seperate category table).. I'm sort of stumbling towards a goal here as I learn this stuff so I hope that made sense.

    I'm using php and mysql.

  4. #4
    Action Jackson - King of the World
    Join Date
    January 18th, 2005
    Posts
    2,201
    Ok I have to ask why not create a separate table for the categories and call them from the main table?

    I do it this way and it seems to work fine. Any issues I should be aware of?

  5. #5
    Full Member
    Join Date
    January 18th, 2005
    Posts
    362
    explain I'm still digging through mysql tutorials.. I understand the concept of relational databases and creating them from scratch but squeezing a datafeed into one and manipulating it or normalizing the feed without extensive manual labor is another matter I'm trying to wrap my brain around.

  6. #6
    Action Jackson - King of the World
    Join Date
    January 18th, 2005
    Posts
    2,201
    Hi Canadian:

    Simple. Just make a table for the categories and put something like this in the sql query

    insert into categorytablename select category from maintable

    Hope that makes sense

  7. #7
    Member mikew's Avatar
    Join Date
    January 18th, 2005
    Location
    southern California
    Posts
    60
    Assuming you want one entry in the category table for each category you need to do this:

    insert into categorytablename select category from maintable group by category

    A nice thing to do is to add a column to your category table for the number of different products in the category(say product_count) then you can do this:

    insert into categorytablename (category, product_count) select category, count(*) from maintable group by category

    You can use this to display the number of products per category in menus etc.

    Now if you want one table entry per product you are better off creating an index on the the maintable itself rather than creating a separate category table.

  8. #8
    Full Member
    Join Date
    January 18th, 2005
    Posts
    362
    good stuff, gives me a platform to dive into mysql from.

    I think I better concentrate on mysql more than php for a bit, seems pulling and displaying with php is the easier part while inserting, formatting, manipulating a datafeed with thousands of products and mish mash of categories is the harder part.

  9. #9
    Member mikew's Avatar
    Join Date
    January 18th, 2005
    Location
    southern California
    Posts
    60
    <BLOCKQUOTE class="ip-ubbcode-quote"><font size="-1">quote:</font><HR>manipulating a datafeed with thousands of products and mish mash of categories is the harder part. <HR></BLOCKQUOTE>

    Quite true. I normalize all my feeds into a single file with all the fields I want; one of which is the merchant name. Just prior to loading I run a Perl script to filter that file and adjust categories accordingly. For example under the apparel category I'll see sub-categories like, "boots", "shoes", "shoes and boots" etc. all this will be placed in a single "footwear" category.

    Perl is good for this because Perl's regular expression capabilities allow me to search for key expressions in the product name and/or description and to do quick substitutions.

  10. #10
    Newbie
    Join Date
    January 18th, 2005
    Posts
    13
    Sounds good. You've gotten some good responses. Sorry I wasn't able to reply again I forgot to turn notifications back on.

    Very interesting count how you load your products. We currently have 2 scripts. One to download the file and 1 to place the file in the table (cleaning it up and creating keywords for the fulltext index before hand) and that's pretty simple.

    I've been trying to figure out the best way to use the categories that merchants provide simply because they are so vague sometimes.

    If there was more detail, it would make many people's lives a little easier.

    I hope all is going well for you Canadian eh. Will check back again.

  11. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. CSS or Tables?
    By Cheesehead in forum Programming / Datafeeds / Tools
    Replies: 32
    Last Post: July 10th, 2007, 02:35 AM
  2. Tables problem
    By zimmy in forum WebMerge (Fourthworld.com)
    Replies: 26
    Last Post: May 26th, 2004, 09:20 AM
  3. Need Help With Tables & IE
    By sweat in forum Programming / Datafeeds / Tools
    Replies: 6
    Last Post: July 10th, 2003, 12:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •