Number of Merchants for SKU or Name grouping
Hi David
By default the system groups products under the categories by 'name' which gives us the amount of merchants selling the product.
I have done a SKU hack.
The problem is it is only grouping by 'name' and therefore not showing the real amount of merchants selling the product.
Is there a way to count both Name matching and SKU matching??
Hi David,
Have done that already..
What I'm referring to is in the category view.
Basically if the system matches more than 1 product name from different merchants it displays the COMPARE button.
So compare from 3 merchants.
Problem arises with 3 different named products but same SKU.
They will show as 3 separate products with a view details button. Then clicking through to the products page with the SKU matching added, shows the 3 merchant offers..
understand?
Hi Andrew,
Yes - that makes sense; it is actually quite a complex operation to do this - as you start with only keywords; then mapping products found matching the keywords to SKU and then complimenting the results with SKU versions...
What about a post-import process to match up product names with SKUs? I think that would be a far more efficient solution. It's a four step process following importing of all products;
1) SELECT the name and sku fields from all products
2) For every result; UPDATE products and SET sku=sku WHERE name=name
3) Select distinct(sku),name from all products
4) For every result, SET name=name WHERE sku=sku
After which, every product that has an SKU will have the same name; and will then be compared properly on the search results page and product page without further modification. If you'd like to give this a go; drop me an email confirming your products table structure and details of how you currently run the import process - and also the size / predicted size of your database as this will have an impact on the most efficient solution...
Cheers,
David.
Thanks for the advice..
I coded a small update query to basically update Name WHERE sku=sku
..its still running.. taking way too long (1 hour ++)
I have almost 200K products.. Planning to have millions ;-)
So I'm going to have to find a more efficient way..
An idea would be to keep a table of known SKU's and their product names.
Then use this just like the product+category mapping arrays.
But if there were like 50K sku's in this table, storing all these in an array would chow up the servers memory?
Hi Andrew,
Have you made sure that `sku` is an indexed field - that should speed things up significantly...!
Cheers,
David.
Hi David
Thanks for the tip, SKU was indeed not indexed.
I added sku to the indexes..
When running my script it gave me the Memory Exhausted error..
Limiting it to 20000 rows per run worked..
Is it limitation on my hardware resources that is causing this memory error? I am on a VPS.
So having more fields indexed requires more memory.. I am learning :-)
Hi Andrew,
I can do depending on MySQL server configuration. If you've not already, have a look at the following thread which has some discussion on MySQL configuration for very large sites...
http://www.pricetapestry.com/node/732
Cheers,
David.
I am a noob!
That memory exhausted error came from PHP memory limit setting.
When running the script from the console it said 8388608 bytes exhausted (8MB) but my htaccess had the limit set to 64MB.
Adding this line to the script file worked:
ini_set('memory_limit','64M');
Hello Andrew,
This can be done relatively easily within products.php, although it does rely on at least one of the items with the exact product name requested having an SKU field. To try this method (assuming that you have added a new "sku" field to the products table), in products.php look for the following code beginning at line 12:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE normalised_name = '".database_safe($q)."' ORDER BY price";$numRows = database_querySelect($sql,$rows);
...and REPLACE with:
$sql = "SELECT sku FROM `".$config_databaseTablePrefix."products` WHERE normalised_name = '".database_safe($q)."' AND sku <> '' LIMIT 1";if (database_querySelect($sql,$rows))
{
$sku = $rows[0]["sku"];
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE normalised_name = '".database_safe($q)."' OR sku='".database_safe($sku)."' ORDER BY price";
}
else
{
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE normalised_name = '".database_safe($q)."' ORDER BY price";
}
$numRows = database_querySelect($sql,$rows);
Hope this helps!
Cheers,
David.