Hi David,
I hope you're well. I've finally got the first stage of my website up {link saved}, but I need to make a few alterations to improve it's funtionality. I'm just testing it out now with some dummy data.
Im using the product mapping table (manually imported data using phpMyAdmin) to populate the generic information for each product. I've decided to only have comparison tables and not banner ads as well. I'm then using the feed management importation tool to populate each customers products. You will see on the product pages I have a tabbed comparison table (currently both tabs have the same comparison table in them though). So here is what I want to do:
1) I want to differ the comparison tables in each tab based on the category that the customers items fall within. I have the data and have no problem importing it, I'm just not sure how to restrict which items appear. I was thinking maybe something similar to a previous reply of yours (www.pricetapestry.com/node/5510) where I create a comparison table for each category in a separte html, which is then pulled into the respective tab.
2) Sometimes I'll need to show different tabs based on the item. I'm coding the brand variable to again pull in a html with the correct tabs. The problem I have is that the product mapping table doesn't contain "brands". How can I add a brands coloumn / data, and make sure it's picked up when I import the customers products? I would hopefully use the same method to populate additional generic information at a later stage.
3) Lastly, I was having trouble re-directing cron feed data to a different table(ie: pt_acustomerfeed), but I've found a way to do it manually, which is slower, but it will work for now until I can sort it out. Ideally, I'd like to upload minimal data through the merchant feed import (to pt_products), ie: merchant name, product name, primary key and comparison table description (I'm going to create that column in the database later). Then use the primary key to link it to the all the data that is in pt_acustomerfeed table (pulling across prices, buy url etc..). The feed import will only contain items I want to display, whereas the data in pt_acustomerfeed will contain all the merchants items. How do I a) import the minimal data, ie. I always have to import prices and the buy url; and b) how do I link the tables correctly (the primary keys in both tables are going to be the same column from the original data feed).
Sorry this is sooooo long David, I think these are the last few hurdles to get my website fully functional. Thanks again for all your help!
Kind Regards
Islam
Hi David,
That's perfect; thank you so much! I'm going to make a start on this now, but while I'm doing that can I just clarify a few points?
Re 1;
You mentioned that "de-duplication of products is based on merchant+product". In the code you allow the same named products (from the same merchant) to be displayed as long as they are in a different category; is that right? What would happen if the merchant had multiples of the same product in the same category i.e. differentiated by size (this is where the comparison table "product description" would be used to differentiate between them for anyone using the site) etc.. Is it possible to list them all in their respective tabs?
Re 3;
As I understand it, this would allow me to pull in generic product data to populate the product page, is that right? This would be perfect for everything outside of the comparison table; which I do need to do! I'm not sure if I'm missing something, or if I didn't explain it properly... this question was refering to how I could pull data from a table which I created. Please forgive me if I've misunderstood.
So, I basically have 3 tables: 1) pt_products, 2) pt_productsmap, and 3) pt_acustomerfeed.
pt_productsmap: I'm populating using an excel file and importing using phpMyAdmin. This will contain all the generic data (everything outside of the comparison table)
pt_acustomerfeed: This is the table which will contain all the merchants products ie the complete feeds from multiple merchants. I only want to show those that conform to the data in my product map, that's why I'm putting them in a separate table.
pt_products: These are the products from the merchants feed which conform to the map and those which will be shown on the website.
I know now how to populate the generic data, but when it comes to the comparison table, I'll need to pull the most up to date data from pt_acustomerfeed without adding the whole feed. The only information I'm adding through the feed import is the merchant name, product name, category and primary key. So, the only information that will appear in the comparison table from that would be the merchants name. The other variables (product description, delivery, price and buy url) would come from pt_acustomerfeed (which I'll manage using cron jobs in the future). In a way the product feed I'm importing into pt_products will act like a key to differentiate the products into their comparison tables and match up the product descriptions in the map (pt_productsmap) with those in the continuely updated full feed data (pt_acustomerfeed). Does that make sense? I'm not sure if this is the easiest way of doing it, but I don't want to risk the whole feed populating the website and reducing the ability for accurate comparisons.
Please let me know if you need me to clarify anything, I'm not finding it easy to explain...
Thanks again for the wonderful service!
Kind Regards
Islam
Hi David,
Sorry to bombard you with questions... I'm making progress, but I'm having some trouble with importing products from the same categories.
I don't have $dupe_key .= tapestry_mb_strtolower($searchName); in my version, so I changed (around line 362):
$dupe_key .= $searchName;
to
$dupe_key .= $searchName;
$dupe_key .= $importRecord["category"];
When I do this, it allows me to import a few more items, but not all of them. One of two things happens:
1) If you have 2 items with the same name but different categories, when you import the feed you get one of each of them in both tabs. ie the items are duplicated, and no longer separate according to categories. Instead of having 2 items, you now have 4.
2) If you have more than 1 item with the same name in the same category only one of them is imported.
The comparison tables seem to be working well other than that (when there are no duplicates), everything separates out like it should. I think the problem is only at the feed import stage. Also, I can't work out why when you make the change above, it imports more items, but not all... any ideas?
Kind Regards
Islam
Hi,
Since you are looking to work with multiple items of the same name from the same merchant (and then having them separated into separate comparison tables by category) perhaps it would best to remove the de-duplication altogether so that all products are imported.
This can be done by dropping the dupe_filter unique key on the pt_products table, and there are dbmod scripts for both removing (and re-enabling if required!) in this comment.
With all products being imported, then what I would suggest is temporarily displaying category and brand in the price comparison table to aid in making sure that everything is being categorised etc. and displayed in the correct tables. An easy way to add more information without adding columns is to display the additional fields below the original_name field (the version of the product name displayed in the price comparison table). To do this, edit html/prices.php and look for the following code at line 57:
<td class='hide-for-small-only'><?php print $product["original_name"]; ?></td>...and REPLACE with:
<td class='hide-for-small-only'>
<?php print $product["original_name"]; ?>
<br />
<?php print "Category: ".$product["category"]." Brand: ".$product["brand"]; ?>
</td>Hope this helps!
Cheers,
David.
--
PriceTapestry.com
Hi,
Re 3;
One option (if i've understood correctly!) would be to extend the method whereby pt_productsmap is queried at the top of the products.php to pull in the custom fields instead of having them imported, to your pt_acustomerfeed table, with the only difference being the fields pulled in would have to be keyed by merchant+product name+category - have a go with something like this;
$acustomerfeedsData = array();
$sql2 = "SELECT * FROM `".$config_databaseTablePrefix."acustomerfeeds` WHERE name = '".database_safe($rows[0]["name"])."'";
if (database_querySelect($sql2,$rows2))
{
foreach($rows2 as $row2)
{
$m = $row2["merchant"];
$n = $row2["name"];
$c = $row2["category"];
$acustomerfeedsData[$m][$n][$c] = $row2;
}
}
foreach($rows as $k => $row)
{
$m = $row["merchant"];
$n = $row["name"];
$c = $row["category"];
$rows[$k]["other_field_1"] = $acustomerfeedsData[$m][$n][$c]["other_field_1"];
$rows[$k]["other_field_2"] = $acustomerfeedsData[$m][$n][$c]["other_field_2"];
// etc..
}(this is in addition to the code suggested above to pull in fields from pt_productsmap)
Hope this points you in the right direction...
Cheers,
David.
--
PriceTapestry.com
Hi David,
That's great, thank you! So I've been working on it again today... The dbmod worked as expected and all the items in the feed were imported. The total number of items in the feed matched the number imported, but I was still getting multiples in the comparison table (ie 4 instead of 2)... I checked the phpMyAdmin tables and it turns out there were no duplicate data for those items. When the items are in the same category they show up fine (no duplicates. When they are in different categories, I get duplicates. I think the problem is due to how I'm using the array... What I did:
Within the initial php code of html/product.php I inserted:
$pricesByCategory = array();
foreach($prices["products"] as $p)
{
$pricesByCategory[$p["category"]]["products"][] = $p;
}
Within each tab I inserted the code below (changing the category to the values I'd defined for that category):
<?php
if (count($pricesByCategory["Category 1"]))
{
if (isset($prices)) require("html/prices.php");
}
?>
You'll notice that I removed the following (for some reason with it the comparison table comes back empty):
$prices["products"] = $pricesByCategory["Category 1"];
I've been trying to fix it by trial and error (I've been trying to edit the section I removed), but I think what is happening is that the array is checking to see if any items fall within that category; if so it displays them all. I'm not sure how to fix that...
Thanks again David!
Kind Regards
Islam
Hi,
Sorry about that - the code to overload $prices["products"] with the per-category sub-set, was incorrect, so where you have as your example;
<?php
if (count($pricesByCategory["Category 1"]))
{
if (isset($prices)) require("html/prices.php");
}
?>
...have a go with:
<?php
if (count($pricesByCategory["Category 1"]))
{
$prices["products"] = $pricesByCategory["Category 1"]["products"];
if (isset($prices)) require("html/prices.php");
}
?>
That should be all it is...
(corrected above also)
Cheers,
David.
--
PriceTapestry.com
Hi David,
I've made that change so you can see on my website. As soon as I include that line of code it removes all the data from the comparison tables. Without that line you get the data in the table, but a conflict when there are items from multiple categories.
Kind Regards
Islam
Hi,
I updated my last post just a few moments ago when I suddenly spotted the mistake! The original code to overload $price["products"] was incorrect so i've described the correct replacement and updated the original post above...
Cheers,
David.
--
PriceTapestry.com
Hi David,
Ahhh... perfect! Sorry, I was a bit too quick in applying the changes. Yes, that worked perfectly. Thank you so much! On to the next step!
Kind Regards
Islam
Hi David,
So, I made the changes which allows you to pull in data directly from pt_productsmap (rather than import them to pt_products). It worked really well on the products pages, except on the featured items / search results pages the images aren't showing (these are being pulled from pt_productsmap). Is there another section which needs to be editted to allow them to show?
ps. I haven't tried the second block of code where it pulls in from pt_acustomerfeed yet.
Kind Regards
Islam
Hi,
Sure - image_url can be re-queried from pt_productsmap for Featured Products and Search Results as follows. At the very top of html/featured.php insert the following PHP section:
<?php
$ins = array();
$ks = array();
foreach($featured["products"] as $k => $v)
{
$ins[] = "'".database_safe($v["name"])."'";
$ks[$v["name"]] = $k;
}
$in = implode(",",$ins);
$sql = "SELECT name,image_url FROM `".$config_databaseTablePrefix."productsmap` WHERE name IN (".$in.")";
database_querySelect($sql,$rows);
foreach($rows as $row)
{
$featured["products"][$ks[$row["name"]]]["image_url"] = $row["image_url"];
}
?>
And almost identically, at the top of html/searchresults.php:
<?php
$ins = array();
$ks = array();
foreach($searchresults["products"] as $k => $v)
{
$ins[] = "'".database_safe($v["name"])."'";
$ks[$v["name"]] = $k;
}
$in = implode(",",$ins);
$sql = "SELECT name,image_url FROM `".$config_databaseTablePrefix."productsmap` WHERE name IN (".$in.")";
database_querySelect($sql,$rows);
foreach($rows as $row)
{
$searchresults["products"][$ks[$row["name"]]]["image_url"] = $row["image_url"];
}
?>
Hope this helps!
Cheers,
David.
--
PriceTapestry.com
Perfect; thank you David!
I've been working on the acustomerfeed modification all morning, but it only pulls in the first row of records. Do I need to loop it? I've been trying to insert a "foreach" block of code, but I can't seem to get it to work... I'm not sure if I'm coding it incorrectly, or if my approach is totally wrong. Do you know what the problem might be?
Kind Regards
Islam
Hi,
The code from this comment should be performing the re-query for all results - the first section of code generates the cross-reference array ($acustomerfeedsData) keyed by merchant+name+category, and then the foreach loop beginning;
foreach($rows as $k => $row)...loops over all results for the product and pulls in the values loaded from the cross-reference array. If you're still not sure or if i've mis-understood of course, if you could post the code demonstrating how you have added a foreach loop I'll see if I can point you in the right direction of course...
Cheers,
David.
--
PriceTapestry.com
Hi David,
Yeah, that's what I thought it was doing, and then when it didn't work, I thought maybe there was a loop missing, but I couldn't really make sense of it. Everytime I'd add a foreach loop it would currupt the page and returned a blank page.... So I was just going back and forth to see the effects of different changes.
So, what I've done, rather than cross-reference the array with merchant+name+category, I'm doing it with the productID. These are all unique. Also because the names in the merchants feed are often replicated within the same category etc.. Below is the code I'm using; which returns the first value only:
$acustomerfeedsData = array();
$sql2 = "SELECT * FROM `".$config_databaseTablePrefix."acustomerfeed` WHERE ProductID = '".database_safe($rows[0]["ProductID"])."'";
if (database_querySelect($sql2,$rows2))
{
foreach($rows2 as $row2)
{
$m = $row2["ProductID"];
$acustomerfeedsData[$m] = $row2;
}
}
foreach($rows as $k => $row)
{
$m = $row["ProductID"];
$rows[$k]["price"] = $acustomerfeedsData[$m]["price"];
$rows[$k]["TableDescription"] = $acustomerfeedsData[$m]["TableDescription"];
}Kind Regards
Islam
Hi,
Very close I think - the reason it's only working for one row is the initial query is only SELECT'ing from the acustomerfeed table by the ProductID of the first (cheapest) product, so this is where another foreach() loop is required to construct an IN clause so that the initial query selects the corresponding acustomerfeed row for all products. Have a go with something like...
$acustomerfeedsData = array();
$ins = array();
foreach($rows as $row)
{
$ins[] = "'".database_safe($row["ProductID"])."'";
}
$in = implode(",",$ins);
$sql2 = "SELECT * FROM `".$config_databaseTablePrefix."acustomerfeed` WHERE ProductID IN (".$in.")";
if (database_querySelect($sql2,$rows2))
{
foreach($rows2 as $row2)
{
$m = $row2["ProductID"];
$acustomerfeedsData[$m] = $row2;
}
}
foreach($rows as $k => $row)
{
$m = $row["ProductID"];
$rows[$k]["price"] = $acustomerfeedsData[$m]["price"];
$rows[$k]["TableDescription"] = $acustomerfeedsData[$m]["TableDescription"];
}Cheers,
David.
--
PriceTapestry.com
Hi David,
Amazing! Thank you so much! I'm still working on my knowledge of coding and I don't think I would have got that! That has now taken me over the hump, 80-90% of the remaining work is now template modifications / coding based on knowledge gained, and I should be able to manage that ok. Thanks so much David; I'm not sure if I would have got so far, so quickly without you!
Kindest Regards
Islam
Hello Islam,
Re 1;
It looks like you're making multiple calls to require("html/prices.php"); so one thing to do would be to loop over the $prices["products"] array and create separate arrays by category.
Then, call each instance of html/prices.php with $prices["products"] loaded with the appropriate category subset. This also means that you can only display that tab if there are prices for that category, for example (not including tab code)...
$pricesByCategory = array();foreach($prices["products"] as $p)
{
$pricesByCategory[$p["category"]]["products"][] = $p;
}
if (count($pricesByCategory["Category 1"]))
{
$prices["products"] = $pricesByCategory["Category 1"]["products"];
require("html/prices.php");
}
if (count($pricesByCategory["Category 2"]))
{
$prices["products"] = $pricesByCategory["Category 2"]["products"];
require("html/prices.php");
}
On a side-note, although it looks like you have this in hand, bear in mind that the de-duplication of products per merchant is based on (merchant+product), so if you have the same merchants for the same product name but in different categories then you would need to bring category into the de-duplication index which is no problem - in includes/admin.php look for the following code at line 483:
$dupe_key .= tapestry_mb_strtolower($searchName);...and REPLACE with:
$dupe_key .= tapestry_mb_strtolower($searchName);$dupe_key .= tapestry_mb_strtolower($importRecord["category"]);
Re 2;
The product mapping configuration page includes an override field for `brand`, so to display separate price comparison tables by category depending on brand, extending the above example you could do something like;
if ($product_main["brand"]=="Brand 1"){
if (count($pricesByCategory["Category 1"]))
{
$prices["products"] = $pricesByCategory["Category 1"];
require("html/prices.php");
}
if (count($pricesByCategory["Category 2"]))
{
$prices["products"] = $pricesByCategory["Category 2"];
require("html/prices.php");
}
}
if ($product_main["brand"]=="Brand 2")
{
if (count($pricesByCategory["Category 3"]))
{
$prices["products"] = $pricesByCategory["Category 1"];
require("html/prices.php");
}
if (count($pricesByCategory["Category 4"]))
{
$prices["products"] = $pricesByCategory["Category 2"];
require("html/prices.php");
}
}
There is a guide to adding additional custom fields to Product Mapping here...
Re 3;
There is a block of code within the import record handler that sets pt_products field values for the product being imported to the custom values from Product Mapping, so to disable this, look for the opening IF condition at line 297 of includes/admin.php:
if (isset($admin_importProductMappingsOverrides[$importRecord["name"]]))...and REPLACE with;
if (FALSE)Then, in products.php the custom values can be loaded from pt_productsmap instead of being imported into the database. Look for the following code beginning at line 12:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE normalised_name = '".database_safe($q)."'";$numRows = database_querySelect($sql,$rows);
...and REPLACE with:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE normalised_name = '".database_safe($q)."'";$numRows = database_querySelect($sql,$rows);
$sql2 = "SELECT * FROM `".$config_databaseTablePrefix."productsmap` WHERE name = '".database_safe($rows[0]["name"])."'";
if (database_querySelect($sql2,$rows2))
{
$pm = $rows2[0];
foreach($rows as $k => $row)
{
$rows[$k]["custom_field_1"] = $pm["custom_field_1"];
$rows[$k]["custom_field_2"] = $pm["custom_field_2"];
// etc..
}
}
Hope this points you in the right direction!
Cheers,
David.
--
PriceTapestry.com