Hi David,
I'm in the middle of huge PT customization stuff: {link saved}
I've implemented loads of tweaks from this forum, and did some custom coding..Almost there!!
Really like how much love and work you've put into this support forum, reading it saved me a massive amount of work.. :)
One question so far I can't find answer for on the forum.
I want to add extra value to my visitors by giving them advanced sort and query options.
I have some extra fields setup (style, color, material) and I can filter on their value with mods made to search.php, thanks to sidebar filters mod..
Problem is those fields are not populated from feeds, simply because they are not available.
I need a method to populate their values for each product once from admin. A hell of a job but with 10000 products max I just want to do this... You can call me mad :)
Tried to add them in phpmyadmin but they are ofcourse overwritten when updating the feed.
Has anything like this been done in the past and available already, if not, what are your thoughts on doing this the easiest way?
Thanks again!
Bas
I am in the middle of doing something similar and could use advice as well. I built a 2nd products table for my custom fields and that's where I'm putting all of my custom stuff. This keeps it separate so it won't get overwritten or deleted. I will have a script that runs after each feed import to insert new rows to my custom table for any new items. I'm planning to leave old dead items on there too - and possibly continue to show the pages for a time. To set the new column values, I have some SQL code that looks at the fields from the regular products table (name, etc.) and takes a guess at the values for the new columns. You could do something similar for material, color, etc. (example: Set material = 'Cotton' where name like '%cotton%'). The next step is to add a list page similar to searchresults, that will also allow me to update the values in my custom table.
That's the current plan. I'm about 1/3 into it I think. Any further advice would be great.
Thanks,
Gregor
Hello Gregor / Bas.
There is actually be a "half way house" solution whereby rather than duplicate the way Product Mapping loads the database with the custom values (for description, category etc.) at import time, instead still use the Product Mapping interface and add fields to store the additional attributes but rather than import them, simply query the `productsmap` table on the product page to display that additional info.
Working through an example for the field "megapixels" which might apply to a digital camera comparison site; the first step would be to add the field to the `productsmap` table. A dbmod.php script as follows would do the trick:
<?php
require("includes/common.php");
$sql = "ALTER TABLE `".$config_databaseTablePrefix."productsmap`
ADD `megapixels` VARCHAR(255) NOT NULL";
database_queryModify($sql,$result);
print "Done.";
?>
Next, a field needs to be added to admin/productsmap_configure.php. Look for the following code at line 70:
alternates = '".database_safe($alternates)."',...and REPLACE with:
alternates = '".database_safe($alternates)."',
megapixels = '".database_safe($megapixels)."',And finally, look for the following code at line 131:
print "<input type='submit' name='submit' value='Save' /> ";...and REPLACE with:
print "megapixels:<br />";
print "<input type='text' name='megapixels' value='".widget_safe($productmap["megapixels"])."' />";
print "<br /><br />";
print "<input type='submit' name='submit' value='Save' /> ";That's all as far as capturing and storing the info is concerned. Next, to use this info on the product pages; in products.php look for the following code at line 63:
$prices["products"] = $rows;...and REPLACE with:
$prices["products"] = $rows;
$sql = "SELECT * FROM `".$config_databaseTablePrefix."productsmap` WHERE name='".database_safe($rows[0]["name"])."'";
if (database_querySelect($sql,$productsmap))
{
$productsmap = $productsmap[0];
}With that in place; anywhere within html/product.php and html/prices.php the megapixels value will be in scope as $productsmap["megapixels"] and can be displayed, within an HTML section, using for example:
<?php if (isset($productsmap["megapixels"])): ?>
<p>Resolution: <?php print $productsmap["megapixels"]; ?></p>
<?php endif; ?>Cheers,
David.
--
PriceTapestry.com
Sorry..
Was mistake in code submitted..
Please replace code i submitted before with:
<?php
$colors = array(
"rood",
"geel"
);
$style = array(
"modern",
"klassiek"
);
$material = array(
"hout",
"glas"
);
require("../includes/common.php");
require("../includes/widget.php");
$admin_checkPassword = TRUE;
require("../includes/admin.php");
function radio_buttons($array,$name,$label,$selected){
$out = '<strong>'.$label.': </strong>';
$out .= '<select name="'.$name.'" size="1">';
foreach ($array as $i => $value) {
//echo $array[$i];
//$out .= $array[$i].' <input name="'.$name.'" type="radio" value="'.$array[$i].'">';
if($selected==$array[$i]){
$out .= '<option selected>'.$array[$i].'</option>';
}else{
$out .= '<option>'.$array[$i].'</option>';
}
}
$out .= '</select>';
return $out;
}
/* Get new records from products table
* and insert those in the product_extras table
*/
$sql = "INSERT INTO `".$config_databaseTablePrefix."product_extras` ( id, name, image_url, dupe_hash ) SELECT id, name, image_url, dupe_hash FROM ".$config_databaseTablePrefix."products WHERE ".$config_databaseTablePrefix."products.dupe_hash NOT IN ( SELECT ".$config_databaseTablePrefix."product_extras.dupe_hash FROM ".$config_databaseTablePrefix."product_extras WHERE ".$config_databaseTablePrefix."products.dupe_hash=".$config_databaseTablePrefix."product_extras.dupe_hash)";
if (database_queryModify($sql,$rows))
{
$db_message = 'New products retrieved!';
}
if (isset($_POST["extras"]))
{
$sql1 = "SELECT * FROM `".$config_databaseTablePrefix."product_extras` WHERE style = '' OR color = '' OR material = '' ORDER BY id ASC LIMIT 5";;
if (database_querySelect($sql1,$rows1))
{
$numb_rows = count($rows1);
}
$i = 0;
while ($i < $numb_rows) {
$id = $_POST['id'][$i];
$style = $_POST['style'][$i];
$color = $_POST['color'][$i];
$material = $_POST['material'][$i];
$sql2="UPDATE `".$config_databaseTablePrefix."product_extras` SET `style`='".$style."', `color`='".$color."', `material`='".$material."' WHERE `id`='".$id."'";
database_queryModify($sql2,$rows2);
echo $sql2."<br />";
$i++;
}
header("Location: extra_fields.php");
}
require("admin_header.php");
require("admin_menu.php");
$sql = "SELECT * FROM `".$config_databaseTablePrefix."product_extras` WHERE style = '' ORDER BY id ASC LIMIT 5";;
print "<h2>New products that need extra fields to be set</h2>\n";
if (database_querySelect($sql,$rows))
{
$numb_rows = count($rows);
print "<form method='post' action=''>\n";
print "<input type='hidden' name='extras' value='1' />";
print "<table border='0' width='100%' cellpadding='0' cellspacing='0'>\n";
print "<td>Product name</td><td>Image</td><td>Style</td><td>Color</td><td>Material</td>\n";
$i=0;
foreach($rows as $new)
{
$colors_radio = radio_buttons($colors, 'color[]', 'kleur', $new['color']);
echo "<tr><td><input type='hidden' name='id[]' value='".$new['id']."' /><!--".$new['id']."-->".$new["name"]."\n</td><td>\n<img src='".$new["image_url"]."' height='100' /></td><td><input type='text' size='40' name='style[]' value='".$row['style']."' /></td><td>".$colors_radio."</td><td><input type='text' size='40' name='material[]' value='".$row['material']."' /></td></tr>\n";
$i++;
}
print "</table>\n";
print "<input type='submit' value='submit' />";
print "</form>\n";
}
require("admin_footer.php");
?>Hi Bas,
Looks good - I wasn't sure from your earlier message if your code isn't
quite working as you intend yet; let me know if you're still not sure..
Cheers,
David.
--
PriceTapestry.com
Hi David,
Tested the script and works fine..
Two questions left:
-does the dupe_hash ever change, for example on feed update? Now i use it as product identifier between two tables.
-in what file and where inthe file should i call a custom function that needs to run after each feed update? I want to run a query that pulls extra fields values from my additional table to products table on feed update. I don't know where to include this.
Thanks a lot!
Bas
Hello Bas,
dupe_hash is consistent even across updates so it fine to use as the product identifier.
I would call a custom function to do your own post-import processing at the end of the admin_import() function in includes/admin.php, just before the following line:
return "";Here, $filename is the name of the feed being imported which you can pass to your function if required...
Cheers,
David.
--
PriceTapestry.com
Hi david,
Thanks again, that will do!
You're the best!
Hello Bas,
The best thing to do is probably an extension of the Product Mapping feature. Are you running 12/10B which has the description, category brand and Image URL overrides on the configuration page for a Product Mapping?
Cheers,
David.
--
PriceTapestry.com