Results 1 to 14 of 14
  1. #1
    Newbie
    Join Date
    January 15th, 2008
    Location
    Portage, MI
    Posts
    39
    Matching up Brandname_ID with Desc
    I have almost completed my brandname table and several other tables and now need to match up my descriptions with the brand names.

    Scenario:

    I have 5 merchants, many with duplicate products, which means making a brandname table. Now how do I match up the right description in each merchant product table with the right brandname_id other than doing it manually?

    Can this be done using SQL syntax or PHP or do I need to figure out REGEX. I wanted to have this up by this weekend and but this has been a major head scratcher. Hopefully I am making this harder than it needs to be.

    I hate to keep asking questions but it is the easiest way for me to learn. Many thanks for those who have already helped me, someday I can pay this forward to someone with many of the same problems I have learning this.

    Have a great day

    Al

  2. #2
    ABW Ambassador Doug247's Avatar
    Join Date
    January 18th, 2005
    Location
    DE USA
    Posts
    931
    So, you want to list all products where Brand = SelectedBrand?

    You might try something like this:

    PHP Code:
    $query "select * from $tablename Where BRANDNAME = '$shoe_brand'  Order by PRODUCTNAME"
    Last edited by Doug247; April 2nd, 2008 at 08:50 AM.
    Thanks,
    Doug

  3. #3
    ABW Ambassador PatrickAllmond's Avatar
    Join Date
    September 20th, 2005
    Location
    OKC
    Posts
    1,219
    For each product that you are inserting you should be checking your brand table to see if that brand exists.

    If it does, get the brandid and assign it to that product.

    If it does not ADD the brand, get back the brandid, and then assign it to the product.

    When all is said and done every product should have a brand id.

    Not sure why you would do this...but... when you ADD the brandid in your brand table you could also add the merchant information into the brand table at the same time. So your brand table has three columns 'brandid, merchantname, brandname'.
    ---
    This response was masterly crafted via the fingers of Patrick Allmond who believe you should StopDoingNothing starting today.
    ---
    Focus Consulting is where I roll | Follow @patrickallmond on Twitter
    Search Engine Marketing | Search Engine Optimization | Social Media | Online Video

  4. #4
    Newbie
    Join Date
    January 15th, 2008
    Location
    Portage, MI
    Posts
    39
    Thanks Doug,

    I understand the query but what I am trying to do is add a ProductName_ID column into each product table from each merchant.

    I have one table with all the product names, with four columns, cat_id, scat_id, prodname_id, prodname (I had to normalize the products since many of the merchants have matching products) now I need to assign the prodname_id to each respect description, price, etc.., with the products on each merchant table.

    I can leave the prodname column in the merchants tables making it easier to match up, then ignore that column

    Hope that clarifies it.

    Al

  5. #5
    ABW Ambassador bettylou's Avatar
    Join Date
    December 27th, 2005
    Location
    Indiana
    Posts
    595
    Are you trying to create one base product and then match all the merchant's exact same product to that? Such as:

    Base Product: Memorex High Speed CD_RW

    3 of your merchants sell Memorex High Speed CD_RW boxes and you want to match them to that product?

  6. #6
    Newbie
    Join Date
    January 15th, 2008
    Location
    Portage, MI
    Posts
    39
    Bettylou,

    That's what I think needs to be done, otherwise you would have duplicate brand names, and in order to pull each merchant info for a specific product you need to tie them together. I figured making a productname lookup table would solve that. I just need to find a way to give each product its prodname_id automatically because if one merchant adds new products you would need to recode every time.

    Or would it be better to put all the products into one large table with columns for each merchant?
    Last edited by country; April 2nd, 2008 at 11:50 AM. Reason: Adding to

  7. #7
    ABW Ambassador bettylou's Avatar
    Join Date
    December 27th, 2005
    Location
    Indiana
    Posts
    595
    So, are you wanting to show all the products from all merchants? In other words, do you want to show all the merchants that sell the Memorex CDs? Or only one?

    Either way, you would still need some type of Master Products Table. Which would presumably be your brandname table.

    There really isn't a foolproof way to do this automatically. At least initially you are most likely going to have to do it most of in it manually. You could do something like this:

    select from merchant1, merchant2, merchant3 where product_description regexp 'green shoes'

    See, the thing about this method is that you are going to get every product that contains 'green shoes' even if they aren't the exact same product. You would return 'lime green shoes', 'men's green shoes', 'women's green shoes', 'green shoes for dogs', and so on. You would need to decide which are the appropriate green shoes for what you are looking for.

    For one of my sites, I created sort of a frontend method of doing this. I created a page that asks for a keyword, then a query is made that returns all the products that qualify for that keyword. Then I would check the box beside the products that are the same and give them a product id that matched the master table. It was very time consuming but still much quicker than doing it through phpmyadmin.

    Do you happen to have the book by Kevin Yank called Database Driven Websites Using PHP and MySQL?

  8. #8
    Newbie
    Join Date
    January 15th, 2008
    Location
    Portage, MI
    Posts
    39
    I am trying to do a comparison site where I show a specific product with description and prices from all my merchants, giving them the option of where to buy.

    That is the reason for the prodname table and then tying it to each specific merchant if they have that product, allowing me to pull from each merchant table the info I need.

  9. #9
    ABW Ambassador Doug247's Avatar
    Join Date
    January 18th, 2005
    Location
    DE USA
    Posts
    931
    I may be wrong but I don't think you need multiple tables to do the comparison. If all products were in one table with a column in the table fo the merchant name you could have something like this.

    PHP Code:
    $query "select * from $tablename Where BRANDNAME = '$brand' and ProductName = '$name' Order by Price"

    That query shoud return all your products of a given Brand with a a given name, ordered by price. The merchant name should be pulled in the query so you can display it as well.

    I believe you can also use a '%' as a wildcard and match say the first 10 character of a product name. I forget how to do that though...
    Thanks,
    Doug

  10. #10
    Newbie
    Join Date
    January 15th, 2008
    Location
    Portage, MI
    Posts
    39
    Doug,

    If I put all my products into one table along with merchant name and product name is that going to affect performance since it isn't normalized? The method I had planned would have normalized tables.

    You would have duplicate product names and merchant names. I am thinking of putting all products into one large table (many null values, since not all merchants carry the same products) and adding a scat_id, prodname_id and merchant_id column. That would eliminate the need for matching prodname_id to each table.

    Matching the scat_id with the wildcard value would get me there and updating would be a lot easier.

  11. #11
    Newbie
    Join Date
    January 15th, 2008
    Location
    Portage, MI
    Posts
    39
    Bettylou,

    Sorry I missed the last line on your post.

    Yes I do have that book and am going back through it. I found the section on the front page and will implement that, Thanks.

  12. #12
    Newbie
    Join Date
    January 15th, 2008
    Location
    Portage, MI
    Posts
    39
    Doug,

    Here is an example of the % character:


    mysql_query("UPDATE tablename
    SET Category = 'Accessories'
    WHERE SDescription LIKE '%whatyouwanttomatchup%'") or die(mysql_error());

    Works really well

    AL

  13. #13
    ABW Ambassador Doug247's Avatar
    Join Date
    January 18th, 2005
    Location
    DE USA
    Posts
    931
    Al,
    I just saw that you are a newbie to ABW. Welcome, this is a great resource. I'm not sure if you saw this thread, http://forum.abestweb.com/showthread.php?t=60200 but it might help you with regard having all the data in one table. I am using much of what is in that thread to retool and old datafeed site.
    Thanks,
    Doug

  14. #14
    Newbie
    Join Date
    January 15th, 2008
    Location
    Portage, MI
    Posts
    39
    Doug,

    I read that thread many times over already but missed the part about optimizing the table. My whole system is based on that thread. Auto updating and the whole lot. Thanks for reminding me of that, that helped tremendously.

    That gave me the direction to go in. Import all the products into one table and then optimize.

    Thanks again,

    Al

  15. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Creating database of all products in country with images , desc and UPC.
    By zikr in forum Programming / Datafeeds / Tools
    Replies: 11
    Last Post: February 18th, 2014, 02:59 PM
  2. A Theory For Better Matching
    By Cheesehead in forum Search Engine Optimization
    Replies: 4
    Last Post: March 11th, 2006, 04:47 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
  •