Hi there David, hope you're doing well
I'm currently working on a new site and I'm trying to use an Affiliate Window datafeed as the basis.
As I have a few other sites planned in the future I wondered if there was an easy way to set up a database with all 46 of the AWin fields already prefilled and mapped?
I took a look at this thread:
Extending the database schema and adding additional product fields to your site
which guides you through adding 'field_keywords' to the feeds table, and 'keywords' to the products table
Rather than doing a dbmod.php fix after the database/tables have been set-up is there any easy way to create the db/tables before I begin work on the site?
eg. I was looking to edit the setup.sql file and also the $config_fieldSet array in config.advanced.php so registering the feed is quick and easy
I realise that not all the AWin fields are used, but as I will be hopefully re-using this method for a few sites they may be used by other shops on Awin(?)
Have you set this up for other PT users?
Is there a better way to tackle this?
Here is what I thought my setup.sql could look like (but the database+tables didn't set-up as I hoped):
I realise that not all of these fields should be varchar(255)
{code saved}
Thanks David
>> Sure you can do that - the only changes I would make in setup.php
>> would be to make sure that all your field names go in `back ticks`
>> - this will prevent any problems if there is a conflict between a
>> field name that you want to use and and a MySQL reserved word.
Did you mean setup.php or setup.sql ?
In my code for setup.sql - the first new field I was trying to add was field_merchant_id
CREATE TABLE feeds (
id int(11) NOT NULL auto_increment,
filename varchar(255) NOT NULL default '',
registered int(11) NOT NULL default '0',
imported int(11) NOT NULL default '0',
products int(11) NOT NULL default '0',
format varchar(255) NOT NULL default '',
merchant varchar(255) NOT NULL default '',
field_name varchar(255) NOT NULL default '',
field_description varchar(255) NOT NULL default '',
field_image_url varchar(255) NOT NULL default '',
field_buy_url varchar(255) NOT NULL default '',
field_price varchar(255) NOT NULL default '',
field_merchant_id varchar(255) NOT NULL default '',when you say put it in back ticks do you mean the line should look like:
`field_merchant_id` varchar(255) NOT NULL default '',and likewise for the products table - should this
CREATE TABLE products (
id int(11) NOT NULL auto_increment,
merchant varchar(255) NOT NULL default '',
filename varchar(255) NOT NULL default '',
name varchar(255) NOT NULL default '',
description text NOT NULL,
image_url varchar(255) NOT NULL default '',
buy_url text NOT NULL,
price decimal(10,2) NOT NULL default '0.00',
merchant_id varchar(255) NOT NULL default '',be written as:
`merchant_id` varchar(255) NOT NULL default '',Do you know why my setup.sql didn't work as hoped?
re: second paragraph:
I think in my code I already had the changes made in config.advanced.php that you mention for each of the 46 fields
Hi Jim,
That looks fine - can you let me know how the tables didn't end up setting up as you'd expected?
Cheers,
David.
--
PriceTapestry.com
I tried again using the back ticks - using phpMyAdmin and browsing it looks like the feeds table is correctly set up now
However when I try and register my demo CSV file (cropped to 200 lines) it wont import - just has 0 products
Would it be okay to email you my setup.sql & config.advanced.php and the example CSV file? so you'd be able to see what I'm seeing?
Hi Jim,
No problem at all - send them over (for the feed let me know the URL of your installation and filename of the feed and I'll download it directly from your /feeds/ folder to my test server...)
Cheers,
David.
--
PriceTapestry.com
Hi Jim,
Sure you can do that - the only changes I would make in setup.php would be to make sure that all your field names go in `back ticks` - this will prevent any problems if there is a conflict between a field name that you want to use and and a MySQL reserved word.
With that in place, and having add the new fields to the $config_fieldSet array in config.advanced.php, the next thing you probably want to do is set-up the default field mapping for those new fields to the names used in the Affiliate Window feeds. In your config.advanced.php look for where the $config_commonFields array is built up, beginning at line 21 (although it will be after that in the file after you've added your new fields to $config_fieldSet of course). At this point, you can add the default mapping options for each of the new fields, so let's say you have "valid_until", and this maps to the field "VALID_UNTIL" in your feeds, you could add:
$config_commonFields["valid_until"] =array("VALID_UNTIL");
That will save you having to register all fields on Feed Registration Step 2.
Hope this helps!
Cheers,
David.
--
PriceTapestry.com