Results 1 to 18 of 18
  1. #1
    Newbie
    Join Date
    January 15th, 2008
    Location
    Portage, MI
    Posts
    39
    Moving specific rows into new tables
    I've hit another speed bump and need help again.

    What I have is multiple affiliates with very different categories and some having cat,subcat, prodgroup and others having only cat.

    I have been researching this for a couple of days now and am stumped on how to proceed. What is the best method for handling this? (Just asking myself)

    My thoughts are to create my own navigation, which will allow a much more detailed and broken down navigation and then moving only the desc., urls and other pertinent info. columns into the respective tables.

    My question is: How do I move specific rows from aff. that do not have these categories, subcat or prodgroups, which means I can't refer to specific words in specific columns? I also can't do it by Sku since not all aff. have that info.

    It would need to be based on keywords.

    Any thoughts would be appreciated.

    Thanks in advance for any help given and many thanks to those that have already advanced me on my journey.

    Al

  2. #2
    ABW Ambassador bettylou's Avatar
    Join Date
    December 27th, 2005
    Location
    Indiana
    Posts
    595
    Hi country,

    You don't really need to move the actual product rows into a new table. You can just assign each product to a category with a lookup table. You will first need to decide your category names and what subcategories they are to have.

    You could place the products into their categories either by hand or with a regular expression statement (much faster).

    Let me know if you want to give a little more detailed example. This drove me nuts too. It will take a little time but it isn't as hard as you think.


    bettylou

  3. #3
    Newbie
    Join Date
    August 4th, 2007
    Posts
    19
    This one can be a stickler and something I recently worked through. I hope I am addressing your question correctly.

    I created master categories and subcategories based on each specific site. I then manually remap the merchant's cat/subcat to my own. It's a bit of work up front but I only need to update it when I add a new merchant. The final categorization for my site is of course based on the new cat/subcat map.

    I really can't think of an automated way to do this since each merchant has such different data in their feed. Tackling it manually is the only way I found to keep the data clean.

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

    I had a feeling that was what it was going to take. I don't know any regex but I guess its time to learn. If you could give an example that would be great.

    I am also not familiar with a "lookup table" to much of an extent so I will look into that too.

  5. #5
    Newbie
    Join Date
    January 15th, 2008
    Location
    Portage, MI
    Posts
    39
    PaulenaC,

    I thought about doing it manually but with 10,000 products that would be challenge to say the least. I plan on learning REGEX and doing it automatically.

    I already have the CAT. and SUBCAT's that I want, so on to learning some more code.

    PaulenaC, Bettylou
    Any resources that you recommend for REGEX?

  6. #6
    ABW Ambassador bettylou's Avatar
    Join Date
    December 27th, 2005
    Location
    Indiana
    Posts
    595
    Al,

    WARNING: LONG-WINDED EDUCATIONAL CONTENT FOLLOWS!!

    I hate regular expressions and they hate me but they certainly do come in handy.

    A lookup table is just a small table that does wonderful things! It simply holds usually only two values.

    You probably already have a products table that holds the information from your data feeds. For this example, I will simply name mine 'products' and give it only two fields, product_id and product_name.

    You said that you already have categories named. You should have these in their own table, each with a unique id.

    You also said you already have your subcategories named. You should have these in a subcategories table, each also with its own unique id.

    Now create another table named catlookup (or whatever you wish). Create 2 fields-- one named catlookup_catid and another named catlookup_subcatid (again these can be named whatever you want).

    The catlookup table will allow you to have as many or as few subcategories that you want assigned to each category. So let's say you have a main category named "Overhead Lighting" with an id of 1. And you have a subcategory named "Chandeliers" with an id of 25 that you want to be in the main category of overhead lighting. In your catlookup table, insert the values of 1 into the field for catlookup_catid and 25 into the catlookup_subcatid field. Now you can do this for all subcategories that you want to be associated with a main category.

    Now you will also need a product lookup table to assign the products to their subcategory, create it the same way as above but name it something like product_lookup and name the fields prodlookup_subcat and prodlookup_product. You can insert the values for the products that you want to assign to their appropriate subcategories.

    When you need to do the query to display the products in the "Overhead Lighting" category which has an id of 1, it would look like this:

    select product_id, product_name from products, catlookup, product_lookup where catlookup_id = '1' AND catlookup_subcatid = prodlookup_subcat AND prodlookup_product = product_id

    I hope that this made a little bit of sense. Let me know if you have questions.

  7. #7
    Newbie
    Join Date
    January 15th, 2008
    Location
    Portage, MI
    Posts
    39
    This makes sense, and a lot easier than multitudes of tables. I still plan on learning REGEX to automate it. I'll study it tonite and will post back tomorrow or in a few days.

    Thanks for the info.

  8. #8
    ABW Ambassador PatrickAllmond's Avatar
    Join Date
    September 20th, 2005
    Location
    OKC
    Posts
    1,219
    I don't see how regex is needed for this at all. This is how I do it:

    I have an merchant category to my categoryid xref table. The table has a couple of text columns for the merchant category information, and an int column with my categoryid. So if the merchant gives me a category of 'Clothes->Womens Pants' I can map that to my categoryid of "7" (from my categories table, of course). If the merchant gives me a category of "Electronics//Cables" I can map that to my categoryid of "99". If the merchant gives me a category in one column of "Shoes" and a subcategory in another column of "Loafers" I can map that to my categoryid of 574.

    I can then categories my products in one of two ways:

    1. In my product table add a column called 'merchantcategorid'. It maps to the merchantcategory table above.

    2. Write a code to sweep through the product table nightly and categorize all my products based on the values in my merchant category table.

    This solves your real issue - mapping your merchant categories to yours.

    Patrick
    ---
    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

  9. #9
    Newbie
    Join Date
    January 15th, 2008
    Location
    Portage, MI
    Posts
    39
    Patrick,

    I guess I am having a hard time wrapping my head around this method.

    Does this work if you only have one merchant or if you have 5 or more?

    My problem is: How do I break down the merchants that have multiple products in one category and no subcategories. In other words the products need to be split up into more precise subcategories and prod. groups.

    What code would you write to categorize the products on the product table?

    That is what I am looking for. Some merchants are fine, others a headache.

  10. #10
    ABW Ambassador PatrickAllmond's Avatar
    Join Date
    September 20th, 2005
    Location
    OKC
    Posts
    1,219
    This will work for 1 or 100 merchants, but based on what you are saying I see that it may have to be tweaked a little bit.

    Challenge:
    You have a merchant product. The merchant category information that comes is not specific enough. You have a more precise category structure and you want to fit each of the merchant products into your category structure. The best way right now is too eyeball each product and pick what it goes into. But that is time consuming. So you need an automated what to get this done.

    Solution:
    It is still basically the same as my last post. But I think maybe what need is a table that has keywords that you would find in a product. When you find those keywords you want to say "Ahhhh... that product goes in my category 234".

    I create that same "merchantcategory" table and give it three column.
    merchantcategoryid, merchantid, merchantcategory, merchantsubcatgory, keywords, mycategoryid

    As you get new products into your database you want to take the merchantid, category information and subcategory information and add it to this table (if it does not exist). After you import some data (1000 sample records) you are going to end up with new rows that have no keyword and no mycagegoryid information. You then have to add your information in there so when you do queries in the future you can figure out which of your categories each product should be displayed in.

    The more I look at this the more complicated it is getting. This may be a pretty advanced topic. But then again... so is regex
    ---
    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

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

    My idea is along the same line, in fact I thought of exactly what you are thinking but in order to clean up my database and make it easier for looking up problems later (lord knows they'll come) I have decided to move all my necessary columns to a new table, and add brandname_id, merchant_id, cat_id and subcat_id based on a keyword matchup.

    All but one merchant have a keyword column with brand name and product name in them so I got lucky in that respect. Already have Cat table done, most of subcat done and brandname in their own table already. Will have to some tweaking but plan on having all this done by the end of the week.

    Then its on to the template pages and figuring out how they work

    Do I need a template table if I am using two different templates, one for nav and one for product or if/else statements?

    This has been quite a challenge for a newbie in MySQL/PHP but I love a challenge.

    Thanks for all the info,

    Al

  12. #12
    ABW Ambassador PatrickAllmond's Avatar
    Join Date
    September 20th, 2005
    Location
    OKC
    Posts
    1,219
    Can you explain what you are templating?
    ---
    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

  13. #13
    Newbie
    Join Date
    January 15th, 2008
    Location
    Portage, MI
    Posts
    39
    I use a three column layout template and in my cat and subcat nav pages I would use the left for full site nav, center for product nav and right for deal of day or whatever. In my product pages I wanted to use the right column to put some of the pricing but that can be changed and leave it with the other cross sell products.

    Do you usually use one template layout for php or can you do various layouts?

  14. #14
    ABW Ambassador PatrickAllmond's Avatar
    Join Date
    September 20th, 2005
    Location
    OKC
    Posts
    1,219
    All depends on personal preference. I use alot of the 4 pane design for everything. Top, left side, center content, bottom footer.
    ---
    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

  15. #15
    ABW Ambassador bettylou's Avatar
    Join Date
    December 27th, 2005
    Location
    Indiana
    Posts
    595
    Quote Originally Posted by country
    Do I need a template table if I am using two different templates, one for nav and one for product or if/else statements?
    Personally, I don't have any type of templates table. I have the if/else statements for that. For example, if you only want to show pricing for a specific category of products:

    if ($cat == 'lamps'){
    echo $price;
    }else{
    whatever you want to put in its place;
    }


    Hope that makes sense.

  16. #16
    Newbie
    Join Date
    February 27th, 2005
    Posts
    2
    How to Delete a Subcategory in this PHP Code
    Alright now lets say I download the feed file from the merchant. I have alll the categories and subcategories. Now I have a Subcategory Or a Category that I would like to get rid of from the feed file, what php code do I use in the following php code to do this? Lets say I want to delete a Books category or Subcategory but keep all the other categories before uploading back to the server.


    PHP Code:
       $sql mysql_query("insert into $temptable (Sku,Name,Merchantid,Merchant,Buyurl,Smimage,Lgimage,Price1,Price2,Description,Category,Subcategory,Subcategory1,Subcategory2,Subcategory3) values ('$iSku','$iName','$iMerchantid','$iMerchant','$iUrl','$iThumbnail','$iLarge','$iPrice1','$iPrice2','$iDescription','$iCustom1','$iCustom2','$iCustom3','$iCustom4','$iCustom5')") or die(mysql_error()); 
                  
    $rec++;
    //put out . once 50 records are inserted
    if($rec 50 == 0)
    {
    echo 
    ".";
    }
     
                    } 
                   
    $row++;
                         }    

    gzclose ($feed);
    echo 
    "Temp table for the WHOLE $tablename feed has been created.  Please wait whilst we sort through the categories and put them in different tables<br />"

    //find distinct categories
    $sqld=mysql_query("SELECT DISTINCT Category FROM $temptable") or die(mysql_error());
    if (
    $sqld)
    {
    $ctablename='';
    //now create table for each of them and insert products into that table
    while ($fetchd=mysql_fetch_array($sqld))
    {
    $ctgo=$fetchd['Category'];
    $ctg=trim($ctgo);
    //get rid of spaces
    $nctg=str_replace(' ','',$ctg);
    //count the products and only proceed if products>10
    $count = @mysql_query("SELECT COUNT(*) FROM $temptable WHERE Category = '$ctgo'"); 
    $count mysql_fetch_array($count); 
    extract($count); 
    $count $count['0'];

    if (
    $count>10)
    {
    if (
    $ctg!='Default')
    {
    $ctablename=$tablename.$nctg;
    }
    if (
    $ctg=='Default')
    {
    $ctablename=$tablename."Misc";
    }
    mysql_query("drop table $ctablename"); 
              
    mysql_query("CREATE TABLE $ctablename (
              rid INT NOT NULL AUTO_INCREMENT, 
    PRIMARY KEY (rid),
    Sku TEXT NOT NULL,
    Name TEXT NOT NULL,
    Merchantid MEDIUMINT NOT NULL,
    Merchant TEXT NOT NULL,
    Buyurl blob NOT NULL, 
    Smimage blob NOT NULL, 
    Lgimage blob NOT NULL, 
    Price1 varchar(7) NOT NULL default '0.00', 
    Price2 varchar(7) NOT NULL default '0.00',
    Description TEXT NOT NULL,
    Category TEXT NOT NULL,
    Subcategory TEXT NOT NULL,
    Subcategory1 TEXT NOT NULL,
    Subcategory2 TEXT NOT NULL,
    Subcategory3 TEXT NOT NULL,
    FULLTEXT (Name,Category)
              
                    )"
    ) or die(mysql_error());

    //now insert products into that table only if there are categories
    $o=0;

    $sqlu=mysql_query("SELECT * FROM $temptable WHERE Category='$ctg'") or die(mysql_error());
    while (
    $fetchu=mysql_fetch_array($sqlu))
    {
    $Sku=addslashes($fetchu['Sku']);
    $Name=addslashes($fetchu['Name']);
    $Merchantid=addslashes($fetchu['Merchantid']);
    $Merchant=addslashes($fetchu['Merchant']);
    $Url=addslashes($fetchu['Buyurl']);
    $Thumbnail=addslashes($fetchu['Smimage']);
    $Large=addslashes($fetchu['Lgimage']);
    $Price1=addslashes($fetchu['Price1']);
    $Price2=addslashes($fetchu['Price2']);
    $Description=addslashes($fetchu['Description']);
    $Custom1=addslashes($fetchu['Subcategory']);
    if (
    $ctg=='Default' AND empty($CUstom1))
    {
    $Custom1='General';
    }
    $Custom2=addslashes($fetchu['Subcategory1']);
    $Custom3=addslashes($fetchu['Subcategory2']);
    $Custom4=addslashes($fetchu['Subcategory3']);

    if (!empty(
    $Custom1))
    {
    $sqlin mysql_query("insert into $ctablename (Sku,Name,Merchantid,Merchant,Buyurl,Smimage,Lgimage,Price1,Price2,Description,Category,Subcategory,Subcategory1,Subcategory2,Subcategory3) values ('$Sku','$Name','$Merchantid','$Merchant','$Url','$Thumbnail','$Large','$Price1','$Price2','$Description','$Custom1','$Custom2','$Custom3','$Custom4','$Custom5')") or die(mysql_error());
    if (
    $sqlin)
    {
    $o++;
    }
    }

    }
    echo 
    "Table $ctablename created and $o products inserted<br />";
    }
    }

    //delete localfile and original tablename
    unlink("$localfile");
    mysql_query("drop table $temptable");

    ?> 

  17. #17
    Newbie
    Join Date
    January 15th, 2008
    Location
    Portage, MI
    Posts
    39
    Morning Rocket,

    The way I do it is to make a search and replace file with all my codes to make all my changes.

    Change product name, descriptions, delete categories, add columsn, delete columns, whatever.

    I keep the import file seperate for the testing phase(to make sure you are making all the changes that you want) and then when all my testing is done then combine the import file with the SR file. Then import into a new table. All done automatically.


    I think this will do it for you its what I use;
    mysql_query("DELETE FROM tablename WHERE ProdGroup LIKE 'Laptop%'")or die (mysql_error());

    The % is a wild card, it can be used in a couple of different ways and is very handy.

    Laptop$%- and all after
    %Laptop -and all before
    %Laptop% -matching all cases of Laptop
    Laptop- matching only Laptop

    Hope that helps
    Last edited by country; April 12th, 2008 at 05:22 AM. Reason: add to

  18. #18
    Member
    Join Date
    April 11th, 2008
    Location
    Oregon
    Posts
    82
    Thank you country for asking the question I needed answered.

  19. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Matching Specific Sales To Specific Clicks
    By blitwin in forum Midnight Cafe'
    Replies: 1
    Last Post: May 7th, 2008, 07:40 PM
  2. Update mysql rows with checkboxes
    By mobilebadboy in forum Programming / Datafeeds / Tools
    Replies: 3
    Last Post: January 26th, 2005, 09:27 PM
  3. Newbie Question - Rows and Columns
    By ktmkiddy in forum WebMerge (Fourthworld.com)
    Replies: 7
    Last Post: September 5th, 2004, 11:29 AM
  4. SUGGESTION: 40 rows instead of 20 in reports
    By MichaelColey in forum Rakuten LinkShare - LS
    Replies: 0
    Last Post: February 13th, 2004, 06:12 AM
  5. rows
    By cowox in forum Programming / Datafeeds / Tools
    Replies: 0
    Last Post: January 2nd, 2003, 01:12 AM

Posting Permissions

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