You are here:  » Indexed Fields


Indexed Fields

Submitted by support on Tue, 2009-11-10 11:51 in

Hi everyone,

This is an updated "How To" regarding adding new fields to a site running the latest distributions. 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 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 default ''"
;
  
database_queryModify($sql,$result);
  
$sql "ALTER TABLE `".$config_databaseTablePrefix."products`
            ADD `keywords` VARCHAR(255) NOT NULL default ''"
;
  
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 10 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 full product record is in context you can access the new field through the appropriate array variable for example within the loop within html/prices.php:

  $product["keywords"]

...or within html/product.php for the main product page:

  $product_main["keywords"]

Submitted by paddyman on Tue, 2009-11-10 13:40

Fantastic addition. A million times easier :)

Submitted by thelegacy on Tue, 2009-11-10 14:08

Excellent work David, this makes modifying fields so much easier, and quicker.

Submitted by philstone on Thu, 2009-11-26 00:20

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

Submitted by support on Thu, 2009-11-26 10:23

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'>&nbsp;</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.

Submitted by philstone on Thu, 2009-11-26 13:05

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

Submitted by support on Thu, 2009-11-26 15:03

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.

Submitted by alecs on Wed, 2009-12-30 22:08

mh...doesnt works for me. after mod. no import of any feed

Submitted by support on Thu, 2009-12-31 09:12

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.

Submitted by haggle on Wed, 2010-06-02 02:28

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?

Submitted by support on Fri, 2010-06-04 05:53

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.

Submitted by don_load on Sun, 2010-08-22 19:35

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

Submitted by support on Mon, 2010-08-23 08:20

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

Submitted by don_load on Mon, 2010-08-23 17:48

thanks, the data cleanse was just what it needed.

regards,
Jay

Submitted by popsantiago on Mon, 2010-11-15 16:59

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

Submitted by support on Mon, 2010-11-15 17:05

Hi Pops,

Sorry for the confusion, I just realised what you mean and will follow up by email...

Cheers,
David.
--
PriceTapestry.com

Submitted by clare on Wed, 2011-05-04 09:31

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.

Submitted by support on Wed, 2011-05-04 09:45

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

Submitted by clare on Wed, 2011-05-04 11:25

Yes it does, thanks very much, it is a really neat way to be able to add new fields to the tables, really useful.

Submitted by pixeldreher on Wed, 2012-05-23 21:36

hi David,

can i use this Code:

   <?php if ($mainProduct["keywords"]): ?>
          <p><?php print $mainProduct["keywords"]; ?></p>
   <?php endif; ?>

in my WordPress Backend "Shortcodes" á la:

%IF_DB_keywords%
      %DB_keywords%
%ELSE_DB_keywords%
     Sorry, this Product have no keywords
%ENDIF_DB_keywords%

Thanks for your great support!!!

Submitted by support on Thu, 2012-05-24 07:00

Hi,

It's not built-in by default but would be straight forward to add. In pto_product.php look for the following code at line 123:

  $each = str_replace("%DB_".$field."%",$product->$field,$each);

...and REPLACE with:

  if ($product->$field)
  {
    $each = preg_replace('/%ELSE_DB_'.$field.'%(.*)%ENDIF_DB_'.$field.'%/U','',$each);
    $each = str_replace("%IF_DB_".$field."%","",$each);
  }
  else
  {
    $each = preg_replace('/%IF_DB_'.$field.'%(.*)%ELSE_DB_'.$field.'%/U','',$each);
    $each = str_replace("%ENDIF_DB_".$field."%","",$each);
  }
  $each = str_replace("%DB_".$field."%",$product->$field,$each);

Cheers,
David.
--
PriceTapestry.com

Submitted by pixeldreher on Thu, 2012-05-24 10:47

Hi David,

thanks for your support, but they didn't work :(

I use this in my Plugin Backend:

<div class="pvg_infos">
<h2>%ORIGINAL_NAME%</h2>
<div class="pvg_deliverytime">Lieferzeit:
%IF_DB_deliverytime%
%DB_deliverytime%
%ENDIF_DB_deliverytime%
</div>
</div>

And become {link saved} as the result.

Submitted by support on Thu, 2012-05-24 10:59

Hi,

Ah - I'd described the modification for HTML > Product > Main, the position you're looking at is HTML > Prices > Each - no problem, exactly the same code will work there also!

Still in pto_product.php, this time look for the following code around line 130 (although it will be a few lines later with the above mod in place)

  $each = str_replace("%DB_".$field."%",$product->$field,$each);

...and REPLACE with:

  if ($product->$field)
  {
    $each = preg_replace('/%ELSE_DB_'.$field.'%(.*)%ENDIF_DB_'.$field.'%/U','',$each);
    $each = str_replace("%IF_DB_".$field."%","",$each);
  }
  else
  {
    $each = preg_replace('/%IF_DB_'.$field.'%(.*)%ELSE_DB_'.$field.'%/U','',$each);
    $each = str_replace("%ENDIF_DB_".$field."%","",$each);
  }
  $each = str_replace("%DB_".$field."%",$product->$field,$each);

Cheers,
David.
--
PriceTapestry.com

Submitted by pixeldreher on Wed, 2012-05-30 20:40

Sorry but your last code didn't work.. i become the same mistakes :(

Here is my complete pto_product.php: {code saved}

I hope you can help me! Many greatings from germany,

André

Submitted by support on Thu, 2012-05-31 07:45

Hello André,

Thanks - I did just try your code and it seemed to work OK, I'll follow up by email...

Cheers,
David.
--
PriceTapestry.com

Submitted by enjoymarcus on Mon, 2012-07-09 15:21

Hi David,

As mentioned by 'popsantiago' above, I would like the option to either choose a field or add my own text when registering a feed (like you can when choosing Merchant/Brand/Category)

Any help would be hugely appreciated.

Thanks,
Marc.

Submitted by support on Mon, 2012-07-09 15:38

Hello Marc,

Sure - first of all, here is an example dbmod.php continuing the "keywords" field example for having the option to enter a value manually:

<?php
  
require("includes/common.php");
  
$sql "ALTER TABLE `".$config_databaseTablePrefix."feeds`
            ADD `field_keywords` VARCHAR(255) NOT NULL,
            ADD `user_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.";
?>

If you have already followed the instructions above then the dbmod.php to just add the user_keywords field would be just:

<?php
  
require("includes/common.php");
  
$sql "ALTER TABLE `".$config_databaseTablePrefix."feeds`
            ADD `user_keywords` VARCHAR(255) NOT NULL
            "
;
  
database_queryModify($sql,$result);
  print 
"Done.";
?>

With that in place, and the new field added to $config_fieldSet as described above, additional modifications as follows, in each case replacing keywords with your new field name:

In admin/feeds_register_step2.php look for the following code at line 285:

  field_custom("Brand","brand",FALSE);
  $skipFields = array("category","brand","name","buy_url","price");

...and REPLACE with:

  field_custom("Brand","brand",FALSE);
  field_custom("Keywords","keywords",FALSE);
  $skipFields = array("category","brand","name","buy_url","price","keywords");

Next, look for the following code at line 62:

          (isset($_POST["brand"])?$_POST["brand"]:"")

...and REPLACE with:

          (isset($_POST["brand"])?$_POST["brand"]:""),
          (isset($_POST["keywords"])?$_POST["keywords"]:"")

Now in includes/admin.php look for the following code at line 2:

function admin_register($filename,$format,$merchant,$fieldMerchant,$registerFields,$userCategory,$userBrand)

...and REPLACE with:

function admin_register($filename,$format,$merchant,$fieldMerchant,$registerFields,$userCategory,$userBrand,$userKeywords)

...and then the following code at line 63:

                    user_brand='%s'

...and REPLACE with:

                    user_brand='%s',
                    user_keywords='%s'

...and then the following code at line 74:

                    database_safe(tapestry_normalise($userBrand))

...and REPLACE with:

                    database_safe(tapestry_normalise($userBrand)),
                    database_safe($userKeywords)

Finally, further down in the same file, look for the following code at line 178:

    if ($admin_importFeed["field_category"])

...and REPLACE with:

    if ($admin_importFeed["field_keywords"])
    {
      if (isset($record[$admin_importFeed["field_keywords"]]))
      {
        $importRecord["keywords"] = $record[$admin_importFeed["field_keywords"]];
      }
      else
      {
        $importRecord["keywords"] = "";
      }
    }
    elseif($admin_importFeed["user_keywords"])
    {
      $importRecord["keywords"] = $admin_importFeed["user_keywords"];
    }
    else
    {
      $importRecord["keywords"] = "";
    }
    if ($admin_importFeed["field_category"])

I appreciate that's quite a bit of change but essentially it's just replicating exactly what's there already for the category and brand fields, that's all - should make sense pretty quickly once studying the code...

Cheers,
David.
--
PriceTapestry.com

Submitted by enjoymarcus on Mon, 2012-07-09 15:50

Worked absolutely perfectly - David, you are a superstar.

Thank you !

Submitted by enjoymarcus on Wed, 2012-07-11 18:15

Hi David,

What sorts of limits would you recommend in terms of custom fields? I'm looking at adding quite a large amount of extra fields and am worried about potential slow down issues.

Thanks in advance.
Marc.

Submitted by support on Thu, 2012-07-12 08:41

Hi Marc,

In terms of adding fields for display purposes only there shouldn't be any performance impact at all as searches etc. are done using table indexes so as long as you're not close to your maximum database size there shouldn't be any problems.

Cheers,
David.
--
PriceTapestry.com

Submitted by enjoymarcus on Fri, 2012-07-13 15:31

Hi David,

I've added some extra fields as per above, and I can't register a feed anymore - I can go from the normal steps, but when I hit 'Register and Trial Import', it puts me back to the admin homepage and the feed hasn't been imported.

Any ideas? I added quite a lot of extra fields, so not sure if this is affecting the import process.

Thanks in advance,
Marc.

Submitted by support on Fri, 2012-07-13 18:20

Hi Marc,

That sounds like at least one of the new fields hasn't been set-up properly in the database - `field_fieldname` on the feeds table and `fieldname` on the products table.

Here's a script that will automatically run the ALTER TABLE SQL for every custom field in $config_fieldSet and bring your database up to date with config.advanced.php:

<?php
  set_time_limit
(0);
  require(
"includes/common.php");
  
$config_databaseDebugMode FALSE;
  
$ignore = array("name","description","image_url","buy_url","price","category","brand");
  foreach(
$config_fieldSet as $field => $v)
  {
    if (
in_array($field,$ignore)) continue;
    
$sql "ALTER TABLE `".$config_databaseTablePrefix."feeds`
            ADD `field_"
.$field."` VARCHAR(255) NOT NULL";
    
database_queryModify($sql,$result);
    
$sql "ALTER TABLE `".$config_databaseTablePrefix."products`
            ADD `"
.$field."` VARCHAR(255) NOT NULL";
    
database_queryModify($sql,$result);
  }
  print 
"Done.";
?>

Create a file containing the above code as dbfix.php, upload to the Price Tapestry installation folder and then browse to dbfix.php.

Cheers,
David.
--
PriceTapestry.com

Submitted by enjoymarcus on Sun, 2012-07-15 12:19

Hi David,

I have run the script successfully, but am unfortunately still having the same issue. I tried deleting the feed and starting over, but no luck.

Any ideas?

Submitted by support on Sun, 2012-07-15 14:08

Hi Marc,

OK, next stage would be to attempt registration and trial import with database debug mode enabled - edit config.advanced.php and change line 6 as follows:

  $config_databaseDebugMode = TRUE;

This should display extended MySQL error information which should indicate the problem - if you're not sure from the message displayed where the problem lies let me know the output and I'll check it out further for you...

Cheers,
David.
--
PriceTapestry.com

Submitted by enjoymarcus on Sun, 2012-07-15 15:09

Hi David,

I have this option enabled, in I've had it enabled since the beginning - however, I'm not seeing any messages anywhere? Where would I normally find the message? Displayed after registration/import?

Thanks,
Marc.

Submitted by support on Sun, 2012-07-15 15:16

Hi Marc,

Whilst not very common on certain PHP configurations (where caching is enabled) the page may still be redirecting back to the /admin/ home page as normal meaning that there is no chance to view the database error message.

To work around this, look for the following code at line 55 of includes/database.php:

  print "[".$sql."][".mysql_error()."]";

...and REPLACE with:

  print "[".$sql."][".mysql_error()."]";exit();

This will cause the script to exist at the first occurrence of a database update/insert query error. Bear in mind that intentional errors can occur during import as part of the duplicate prevention mechanism, so once up and running don't forget to disable database debug mode for normal operation...

Cheers,
David.
--
PriceTapestry.com

Submitted by enjoymarcus on Sun, 2012-07-15 15:50

Thanks David, here's the debug message;

[{code saved}][Unknown column 'user_publisherfriendlyurl' in 'field list']

Submitted by support on Sun, 2012-07-15 16:18

Hi Marc,

That indicates the problem - at least one of the user_[fieldname] fields on the `feeds` table has not yet been created. Here's an update to the universal dbmod posted above that will also create any missing user_fieldname fields on the `feeds` table for all custom fields. It doesn't matter if you don't require the user version for some fields (where they will always be mapped to a field)...

<?php
  set_time_limit
(0);
  require(
"includes/common.php");
  
$config_databaseDebugMode FALSE;
  
$ignore = array("name","description","image_url","buy_url","price","category","brand");
  foreach(
$config_fieldSet as $field => $v)
  {
    if (
in_array($field,$ignore)) continue;
    
$sql "ALTER TABLE `".$config_databaseTablePrefix."feeds`
            ADD `field_"
.$field."` VARCHAR(255) NOT NULL,
            ADD `user_"
.$field."` VARCHAR(255) NOT NULL";
    
database_queryModify($sql,$result);
    
$sql "ALTER TABLE `".$config_databaseTablePrefix."products`
            ADD `"
.$field."` VARCHAR(255) NOT NULL";
    
database_queryModify($sql,$result);
  }
  print 
"Done.";
?>

Cheers,
David.
--
PriceTapestry.com

Submitted by enjoymarcus on Sun, 2012-07-15 17:19

Thanks again David - I fired up the code above, but am still getting the same debug error;

[{code saved}][Unknown column 'user_publisherfriendlyurl' in 'field list']

Submitted by support on Sun, 2012-07-15 17:28

Sorry Marc, I should have spotted that - the SQL would fail since the field_[fieldname] fields already exist.

Have a go with the following, which will only add the user_[fieldname] fields...

<?php
  set_time_limit
(0);
  require(
"includes/common.php");
  
$config_databaseDebugMode FALSE;
  
$ignore = array("name","description","image_url","buy_url","price","category","brand");
  foreach(
$config_fieldSet as $field => $v)
  {
    if (
in_array($field,$ignore)) continue;
    
$sql "ALTER TABLE `".$config_databaseTablePrefix."feeds`
            ADD `user_"
.$field."` VARCHAR(255) NOT NULL";
    
database_queryModify($sql,$result);
  }
  print 
"Done.";
?>

Cheers,
David.
--
PriceTapestry.com

Submitted by enjoymarcus on Sun, 2012-07-15 18:18

Perfect. Thanks David.

Submitted by shaunmac on Mon, 2013-02-11 14:51

I am having an issue with the adding of new content fields. Everything works great but it only shows the content in the field and not the name of the new field. exp. {link saved} as you see next to the image it shows the field but not the content field it is associated with. This was fine before I simply added a static text next to the field and it worked. Now I have gotten to where not all fields will be used for all products and I only want the fields and field name to be shown when it has data. How can this be done?

Submitted by support on Mon, 2013-02-11 15:00

Hi Shaun,

I have implemented a work-around for this for a number of users and will be including in the next update for PriceTapestry.org for WordPress - the fix is to have a conditional placeholder %IFDB_field%...

I know you have made some changes so just to make sure that I am up to date, if you could please email me your latest pto_product.php I'll merge the update for you...

If you could also include in your email the code you currently have added to your Product / Main template (wp-admin > Settings > PriceTapestry.org) e.g. %DB_fieldname% and surrounding code I'll be able to describe the changes with the mod in place with reference to your own custom fields...

Cheers,
David.
--
PriceTapestry.com

Submitted by marco@flapper on Fri, 2013-11-08 09:59

Hi,
I added an additional price database field like described above but it isn't showing.

I can see that there is a value in the database. The field is added to config.advanced.php:
$config_fieldSet["stprijs"] = "stprijs";

And I put it Wordpress Product Main settings: %stprijs%

Did I forget a step?

Submitted by support on Fri, 2013-11-08 10:41

Hi Marco,

To use the new field in the templates it would be:

%DB_stprijs%

Cheers,
David.
--
PriceTapestry.com

Submitted by marco@flapper on Fri, 2013-11-08 11:07

ah, I should get some new glasses.

Thanks.

Submitted by Fuzzy on Fri, 2015-02-13 02:48

Hi David, followed all the instructions and it works perfectly during import.

I have created a custom field called model_number and are trying to include it the search results:

<?php print $product["model_number"]; ?>

doesn't return any results in searchresults.php

Is it also possible to also return this field with its url aka

<a href='<?php print $product["model_numberHREF"]; ?><?php print $product["model_number"]; ?></a>

Thanks Again.

Submitted by support on Fri, 2015-02-13 09:28

Hi,

All it is, is that the custom fields are not yet being included in the SELECT field list in html/search.php. The easiest way is simply to select all fields, so if you look for the following code at line 374:

$sql2 = "SELECT id,name,normalised_name,image_url,description,price,rating FROM `".$config_databaseTablePrefix."products` WHERE id IN (".$in.")";

...and REPLACE with:

$sql2 = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE id IN (".$in.")";

Then the code will work exactly as you have posted (I assume model_numberHREF is also a custom field that you have added)...

Cheers,
David.
--
PriceTapestry.com

Submitted by Fuzzy on Fri, 2015-02-13 11:06

Works Great, thanks David

model_numberHREF isn't a custom field, just an example of what I was trying to achieve - I was trying to capture the search result link for it if possible.

i.e In search results the Model number would also be a clickable hyperlink for the model numbers search term. I am assuming the custom field would need to be part of the search query in some way.

Submitted by support on Fri, 2015-02-13 11:42

Ah - in that case you'd need to add a new search handler model_number: for it, which is easy to do. Firstly, your code in html/searchresults.php would be:

<a href='<?php print $config_baseHREF?>search.php?q=model_number:<?php print urlencode($product["model_number"]); ?>'><?php print $product["model_number"]; ?></a>

Then in search.php, look for the following code at line 98:

      case "brand":
        $fields = array("merchant","category","brand");

...and REPLACE with;

      case "brand":
      case "model_number":
        $fields = array("merchant","category","brand","model_number");

If your database is quite large it would be worth putting an index on model_number to support this search operator, which the following dbmod.php script will do:

<?php
   set_time_limit
(0);
   require(
"includes/common.php");
   
$sql "CREATE INDEX model_number
             ON `"
.$config_databaseTablePrefix."products` (model_number)";
   
database_queryModify($sql,$result);
   print 
"Done.";
?>

Cheers,
David.
--
PriceTapestry.com

Submitted by Fuzzy on Fri, 2015-02-13 13:16

Almost there, now the model number url is returning:

search.php?q=model_number:%22.urlencode($product%5B%22model_number%22%5D).%22

Submitted by support on Fri, 2015-02-13 13:21

Ooops - PHP modes were slightly mixed up - corrected above...

Cheers,
David.
--
PriceTapestry.com

Submitted by bodybuildingcom... on Wed, 2017-09-20 21:24

Hi David,

I have added the code from the following page (https://www.pricetapestry.com/node/5891#comment-25755) regarding the discount & RRP price fields in includes/admin.php.

  /* decimalise price */
  $importRecord["price"] = tapestry_decimalise($importRecord["price"]);
  $importRecord["discount"] = tapestry_decimalise($importRecord["discount"]);
  $importRecord["rrp"] = tapestry_decimalise($importRecord["rrp"]);
  if (($importRecord["discount"]=="0.00") && ($importRecord["rrp"] != "0.00"))
  {
    $discount = ((1 - ($importRecord["price"]/$importRecord["rrp"])) * 100);
    $importRecord["discount"] = tapestry_decimalise($discount);
  }

I am wondering how I can also add the following code for the delivery in includes/admin.php

  /* decimalise price */
    $importRecord["price"] = tapestry_decimalise($importRecord["price"]);
    $importRecord["delivery"] = tapestry_decimalise($importRecord["delivery"]);
    $importRecord["price"] = $importRecord["price"] + $importRecord["delivery"];

I am wondering whether this is possible?

Thanks.

Norbert

Submitted by support on Thu, 2017-09-21 07:29

Hi Norbert,

Sure that will work fine - if you haven't yet added the custom field "delivery", instead of the default suggested VARCHAR(255) use DECIMAL(10,2) instead using the following dbmod.php script:

<?php
  
require("includes/common.php");
  
$sql "ALTER TABLE `".$config_databaseTablePrefix."feeds`
            ADD `field_delivery` VARCHAR(255) NOT NULL default ''"
;
  
database_queryModify($sql,$result);
  
$sql "ALTER TABLE `".$config_databaseTablePrefix."products`
            ADD `delivery` DECIMAL(10,2) NOT NULL"
;
  
database_queryModify($sql,$result);
  print 
"Done.";
?>

If you have already run the above, then the field type can be changed with the following dbmod.php script:

<?php
  
require("includes/common.php");
  
$sql "ALTER TABLE `".$config_databaseTablePrefix."products`
            CHANGE `delivery` `delivery` DECIMAL(10,2) NOT NULL"
;
  
database_queryModify($sql,$result);
  print 
"Done.";
?>

Hope this helps!

Cheers,
David.
--
PriceTapestry.com

Submitted by sirmanu on Wed, 2022-12-14 21:54

Hi David.

I would like to add to pt_feeds the website URL of the store. Is this a good table to store this information?

Notice that this website URL doesn't need to be in pt_products, is just info that I show in other places of installation.

Submitted by support on Thu, 2022-12-15 09:55

Yes, that would make sense.

There's instructions in this comment above for adding a field like category / brand where a user value can be entered instead of selecting a field from the feed; and then if you ignore the last modification to includes/admin.php that will stop the field from being populated in every product record.

Then to get the URL wherever a $product record is in scope, e.g.

  $sql = "SELECT user_url FROM `".$config_databaseTablePrefix."feeds` WHERE `filename`='".database_safe($product["filename"])."'";
  database_querySelect($sql,$rows);
  $url = $rows[0]["user_url"];

Cheers,
David.
--
PriceTapestry.com