Indexed Fields
Hi everyone,
This is an updated "How To" regarding adding new fields to a site running the latest distribution of Price Tapestry (11/09A). The process is now much more straight forward thanks to a new configuration variable $config_fieldSet within config.advanced.php.
In this example; let's say you want to add a new field "keywords" that you might have available in a particular feed, and then you want to display that information on the product page.
Firstly, 2 new fields must be added to the database as follows:
Table: feeds
Field Name: field_keywords
Field Type (size): VARCHAR (255)Table: products
Field Name: keywords
Field Type (size): VARCHAR (255)The easiest way to add new fields is using a MySQL administration tool such as phpMyAdmin. However, it is also straight forward to create a database modification script as follows:
dbmod.php
<?php
require("includes/common.php");
$sql = "ALTER TABLE `".$config_databaseTablePrefix."feeds`
ADD `field_keywords` VARCHAR(255) NOT NULL";
database_queryModify($sql,$result);
$sql = "ALTER TABLE `".$config_databaseTablePrefix."products`
ADD `keywords` VARCHAR(255) NOT NULL";
database_queryModify($sql,$result);
print "Done.";
?>
(run the above script in the top level directory of your Price Tapestry installation)
With the new fields in place, all that remains is to add the new field name to the $config_fieldSet array. Continuing the above example, look for the following code beginning at line 12 of config.advanced.php:
$config_fieldSet = array();
$config_fieldSet["name"] = "Product Name";
$config_fieldSet["description"] = "Product Description";
$config_fieldSet["image_url"] = "Image URL";
$config_fieldSet["buy_url"] = "Buy URL";
$config_fieldSet["price"] = "Price";
$config_fieldSet["category"] = "Category";
$config_fieldSet["brand"] = "Brand";...and REPLACE with:
$config_fieldSet = array();
$config_fieldSet["name"] = "Product Name";
$config_fieldSet["description"] = "Product Description";
$config_fieldSet["image_url"] = "Image URL";
$config_fieldSet["buy_url"] = "Buy URL";
$config_fieldSet["price"] = "Price";
$config_fieldSet["category"] = "Category";
$config_fieldSet["brand"] = "Brand";
$config_fieldSet["keywords"] = "Keywords";With that in place, the field will appear on Feed Registration Step 2 and can be mapped to a field. Finally, to make use of the new field, wherever a product record is in context within the $product array you can access the new field through the variable:
$product["keywords"]...or on the main product page itself:
$mainProduct["keywords"]So to display the keywords after the description field on the product page, look for the following code on line 13 of html/product.php:
<p><?php print $mainProduct["description"]; ?></p>...and REPLACE with:
<p><?php print $mainProduct["description"]; ?></p>
<p><?php print $mainProduct["keywords"]; ?></p>Hope this helps!
Excellent work David, this makes modifying fields so much easier, and quicker.
Hi David
I'm wanting to add a delivery field and a total price field, would this be possible using the technique above?
i tried this script
<?php
require("includes/common.php");
$sql = "ALTER TABLE `".$config_databaseTablePrefix."feeds`
ADD `field_Delivery` VARCHAR(255) NOT NULL";
database_queryModify($sql,$result);
$sql = "ALTER TABLE `".$config_databaseTablePrefix."products`
ADD `delivery` decimal(10,2) NOT NULL default '0.00'";
database_queryModify($sql,$result);
print "Done.";
?>it has created the table, then i made the other changes, but it is just showing 0.00, so not working, so im assuming i've make a mistake, when you have a minute please could you educate me on this, no rush by the way!
also to create a total price field, is this possible?
thanks
regards
Phil Stone
www.buy24-7.net
Hello Phil,
Depending upon the format of the delivery field in your feeds, it may need to be "decimalised" in the same way as the price field during import. To do this; in your includes/admin.php, look for the following code beginning at line 274:
/* decimalise price */
$importRecord["price"] = tapestry_decimalise($importRecord["price"]);...and REPLACE that with:
/* decimalise price */
$importRecord["price"] = tapestry_decimalise($importRecord["price"]);
$importRecord["delivery"] = tapestry_decimalise($importRecord["delivery"]);Now, regarding total price etc; the best approach; which enables comparison to include the delivery cost so that all prices are accurate throughout the site; is to look at the method described in this thread...
http://www.pricetapestry.com/node/3138
To do this based on the above code (the modifications in the above thread refer to the previous version of Price Tapestrt); replace all of the above with (after modification) with:
/* decimalise price */
$importRecord["price"] = tapestry_decimalise($importRecord["price"]);
$importRecord["delivery"] = tapestry_decimalise($importRecord["delivery"]);
$importRecord["price"] = $importRecord["price"] + $importRecord["delivery"];With that in place, here's a complete replacement for html/prices.php to show Price / Delivery / Total Price:
<div class='prices'>
<table width='100%' cellpadding='4'>
<tr bgcolor='#eeeeee'>
<th width='200' align='left'><?php print translate("Stockist"); ?></th>
<th align='left'><?php print translate("Catalogue Product Name"); ?></th>
<th align='left'><?php print translate("Price"); ?></th>
<th align='left'><?php print translate("Delivery"); ?></th>
<th align='left'><?php print translate("Total Price"); ?></th>
<th align='left'> </th>
</tr>
<?php foreach($prices["products"] as $product): ?>
<tr bgcolor='#ffffcc'>
<td><a href='<?php print $product["merchantHREF"]; ?>'><?php print $product["merchant"]; ?></a></td>
<td><?php print $product["name"]; ?></td>
<td><strong><?php print $config_currencyHTML.($product["price"]-$product["delivery"]); ?></strong></td>
<td><strong><?php print $config_currencyHTML.$product["delivery"]; ?></strong></td>
<td><strong><?php print $config_currencyHTML.$product["price"]; ?></strong></td>
<td align='center'><a href='<?php print tapestry_buyURL($product); ?>' <?php print javascript_statusBar("go to ".$product["merchant"]); ?>><?php print translate("Visit Store"); ?></a></td>
</tr>
<?php endforeach; ?>
</table>
</div>Of course if you're not sure about any of the above changes, email me the files and I'll check it out for you...
Cheers,
David.
Hi David
tried those mods, have imported asda & Direct tvs feed using the new fields and its still not registering the delivery amounts,
i've left the site with the columns displayed at the minute, so got that all implemented, just cant get the database to import the deliver price quantities?
regards
Phil Stone
www.buy24-7.net
Hi Phil,
Could you email me your modified includes/admin.php and also include a link to the site, URL showing the price comparison table with the 3 price fields and any username / password required for /admin/ and I'll check it out for you...
Cheers,
David.
mh...doesnt works for me. after mod. no import of any feed
Hello Alecs,
Double check the new fields added to the database - `field_newfield` in the feeds table and `newfield` in the products table.
If you're not sure; first thing to do is enable database debug mode in config.advanced.php. Change line 6 as follows:
$config_databaseDebugMode = TRUE;...and then attempt to register a feed containing your new field and any database problems should be revealed...
Cheers,
David.
I was able to add a new field easily thanku... now...
(1)
How would you include the "keyword" field to be part of the main search.
So it would ONLY search the "Product Name" field and the "Keywords" field. Only the two fields so it doesn't slow down.
(2)
I like how “Keywords” prints in description but would like to be able to define paragraphs from the single description field.
What I mean is... is it possible to define a new line from the description field without creating a new field to do this?
Any magical character which identifies a new line for a field? ?????
i.e. I would like my description to have two paragraphs.
(3) What is the difference in the way pricetapestry functions between the standard field and optional field?
Hi Peter,
Re: (1)
To include your new keywords field in search, firstly a FULL TEXT index would need to be created on your products table on the combination of name and keywords field. To do this, create the following dbmod.php script, upload to the main folder of your Price Tapestry site, and browse to the script once - then delete the file:
dbmod.php
<?php
set_time_limit(0);
require("includes/common.php");
$sql = "CREATE FULLTEXT INDEX name_keywords
ON `".$config_databaseTablePrefix."products` (name,keywords)";
database_queryModify($sql,$result);
print "Done.";
?>
(if your products table is large I would recommend doing this on an empty table - the quickest way would be to use the "Empty" operation in phpMyAdmin)
Then look for the following code at line 152 of search.php:
$matchFields = "name";...and REPLACE with:
$matchFields = "name,keywords";...look for the following code at line 171 :
$where .= "search_name LIKE '%".database_safe($word)."%'";...and REPLACE with:
$where .= "search_name LIKE '%".database_safe($word)."%'";
$where .= " OR keywords LIKE '%".database_safe($word)."%'";Re: (2)
You can simply use tags to define paragraphs within the description. The description is already printed within <p> tags by the following code in html/product.php starting at line 12:
<?php if ($mainProduct["description"]): ?>
<p><?php print $mainProduct["description"]; ?></p>
<?php endif; ?>...so what you could do is REPLACE that with:
<?php if ($mainProduct["description"]): ?>
<p><?php print $mainProduct["description"]; ?></p>
<?php endif; ?>
<?php if ($mainProduct["keywords"]): ?>
<p><?php print $mainProduct["keywords"]; ?></p>
<?php endif; ?>Re: (3)
When you add a new field (keywords in this case) it's not treated any differently to the standard field, except for where indexes are involved. For example, the name, category and brand fields are indexed to increase search speed, however there would not be any performance benefit by indexing keywords on its own as for a basic search using more than one field does invoke a full table scan (so keep an eye on performance where queries involved keywords of less than 4 characters)...
Cheers,
David.
Is there a way of adding a new field that imports numbers as numbers in to an INT() field type rather than varchar().
regards,
Jay
Hello Jay,
Sure - a dbmod.php script for, say, a `insock` field of type INT would be as follows:
<?php
require("includes/common.php");
$sql = "ALTER TABLE `".$config_databaseTablePrefix."feeds`
ADD `field_instock` VARCHAR(255) NOT NULL";
database_queryModify($sql,$result);
$sql = "ALTER TABLE `".$config_databaseTablePrefix."products`
ADD `instock` INT(11) NOT NULL";
database_queryModify($sql,$result);
print "Done.";
?>
(note that `field_instock` in the feeds table remains as VARCHAR(255) of course)
That is essentially all there is to it, however you might wish to cleanse the data during import by applying intval() to the field. To do this, in includes/admin.php, look for the following comment at line 294:
/* create dupe_hash value */(this is the point at where the code is just about ready to INSERT the product into the database)
...and immediately BEFORE that line, add the following code:
$importRecord["instock"] = intval($importRecord["instock"]);Cheers,
David.
--
PriceTapestry.com
thanks, the data cleanse was just what it needed.
regards,
Jay
Hi,
I would like to add a field but i want to be able to choose between "XML Balise" or "Text type" like the Brand or the categorie.
I used this node to create the sql and the display in admin but i can't add the type=text area...
Thank you
Hi Pops,
Sorry for the confusion, I just realised what you mean and will follow up by email...
Cheers,
David.
--
PriceTapestry.com
I am trying to add a merchantid field to the relevant tables.
I have followed the instructions above and added fields to the DB tables feeds and products. And altered config.advanced.php.
So now using the admin interface I can add a new field.
But if I do a mass registration of feeds I get an error, so I am wondering how to go about mass registering files with an extra field.
Currently I use your script for mass reg, so i tried just added the new field at the end ("FIELD3")
php -f /path/to/register.php $FILENAME "csv|124|0|0" "" "FIELD4" "FIELD2" "FIELD12" "FIELD7" "FIELD5" "FIELD8" "FIELD11" "" "FIELD20" "" "FIELD3"
I am thinking I may need to alter register.php and admin.php, or would it be possible to build a command using the step2 url
http://www.site.com/admin/feeds_register_step2.php?filename=cj_1_800_FLORALS-Product_Catalog_1.txt&formatDetected=csv|124|1|0&useFormat=formatSelected&formatSelected=csv|124|1|34
If the step2 url way is not possible, could you tell me is it just register.php and admin.php that I would need to alter, as I will give it a try.
Hi Clare,
To modify scripts/register.php to support new fields, first look for the following code at line 17:
if ($argc <> 14)...and REPLACE with:
if ($argc < 14)Finally, look for the following code at line 55:
$registerFields["brand"] = $fieldBrand;...and REPLACE with:
$registerFields["brand"] = $fieldBrand;
$i = 14;
foreach($config_fieldSet as $field => $v)
{
if (!isset($registerFields[$field]))
{
$registerFields[$field] = $argv[$i++]
}
}You will then be able to do bulk registrations with your new field names on the end of the command line, in the same order that they appear in $config_fieldset in config.advanced.php
Hope this helps!
Cheers,
David.
--
PriceTapestry.com
Yes it does, thanks very much, it is a really neat way to be able to add new fields to the tables, really useful.
Fantastic addition. A million times easier :)