What are people using for their affiliate datafeeds?

[pbryd]

Can someone give me some advice on setting up a database.

What should I do when I have three merchants?

Use a separate datafeed for each merchant and use 3 tables in the database

or

group them all together in a larger datafeed?

 

[nick-harper]

You can put them all in onle table, it just depends how you plan to do it in the future!

By spam filter i mean what if somebody searches for "nick is the best" or www.viagra.com. It then displays on your homepage!

 

[pbryd]

You can put them all in onle table, it just depends how you plan to do it in the future!

By spam filter i mean what if somebody searches for "nick is the best" or www.viagra.com. It then displays on your homepage!

Ah I see

The popular searches on the homepage is fake.

I just picked a few terms and put them in a list

I would like to incorporate a real one, but I'd need to spend some time looking for the code, I expect it would be some sort of poll.

For ease of use, I'll leave it as is for now.

Thanks

 

[pbryd]

If I use feeds from a few merchants, the how do I stop the same item appearing multiple times from different merchants?

eg.

Merchants: Evans Cycles, Halfords, Chainreaction

Product: Dahon Speed D7 Folding Bike

All three merchants may stock the same product.

Should I just let it happen and give the customer a choice?

 

[retired_member21]

Well you have two choices - either build something which deselects/stops products displaying manually (e.g. filter them out) or build a system 'maps' the product to others so you can do price comparison.

Welcome to the conundrum which is setting up your own price comparison system. You can sometime select on a feed field 'mpn' which is universal - if they match you know they're the same product. You can match exact product name strings in the database, or you can slowly manually map products together (classifying them as one) which you'll need another system for altogether. Whatever you do - if you want to build your own comparison engine you are well on the way - it looks great and offers customers choice.

 

[pbryd]

Sounds a bit too advanced for me code wise.

Price comparison stuff would probably be worth it for sites with very high visitors numbers I suppose.

I think I'll stick to one merchant for the main catalogue for now, until my skills in coding improve.


Thanks

 

[awgthomas]

Try and think about seperating out the product info from the merchant. This way, a product dosen't need to know what a merchant is and it shouldn't need to know. It's the merchant that needs to know which product it has a relation to.

Example schema

product
productId
productName
productDescription

productMerchants
id
merchantId
productId(foreign key to product.productId)
merchantProductLink
merchantProductId (unique to to merchant/product (mpn))

productMerchantPrice
id
linkedId(foreign key to productsMerchants.id)
price
date

With this approach, you will have 1 product in your product table, you can have multiple merchants that link to the product(in productMerchants ) and you can also have multiple prices that link to a merchant in the productMerchantPrice table.


So a example with data in would be like the following
product
(1, 'Product A', 'Product A is the best quality......')


productMerchants
(1,10,1,'link for product A for merchant id of 10',1234312542)
(2,15,1,'link for product A for merchant id of 15',5646745766)


productMerchantPrice
(1,1,9.99,12/11/2011)
(1,1,8.99,13/11/2011)
Here we can see 2 prices for product A(id 1) and merchant 10

Hope this helps a little

 

[pbryd]

er thanks awgthomas

That's going to take some effort getting my head around lol.

Is that three table in the database then, product, merchants and price?

Might be a good idea if I manually enter some dummy products into some test tables and work something from there.

Thanks

Phil

 

[awgthomas]

Is that three table in the database then, product, merchants and price?
Phil

Yes, It can take some time to get your head around, but when you break it down, it will be a great deal easier for you if you are going into the world of price comparisons.

You don't really have to have the price table, its just there for me to track changes etc. You could simply add the price into the productMerchants table and then update it in there!!

Although the merchant table that I mentioned only stores the merchant ID which would link to another table with just merchants in.

 

[pbryd]

I think the best thing to do is decide what I want to display on the shopping site first.

Should I show a Dahon Speed D7 Folding Bike with three different prices.

It's going to take a bit of thought over the weekend.

 

[pbryd]

If anyone is following how i'm getting on I have some new php code to display the results in pages rather than one big list.

The new code is largely based on this from php freaks.

I've also broke the php into blocks and use the 'include' command to make it easier when altering numerous pages.

It's still very basic but is starting to look the part.

I've added some washing machines, dishwashers and tumble dryers to my washing machine cleaner page.

Next task is to get a working 'sort by price' option. Tried one this morning but it refused to play along.

Phil

 

[pbryd]

Try and think about seperating out the product info from the merchant. This way, a product dosen't need to know what a merchant is and it shouldn't need to know. It's the merchant that needs to know which product it has a relation to.

<-----------snip------------>

Hope this helps a little

Thanks for this.

I've set a couple of tables up and popped in some test data. I've linked the merchant productid to the product productid and it seems to be working when I run a query.

Here's what I have for testing purposes.

tables

-> product

productid
productname
productdescription

-> merchants

merchantid
productid
productlink

I'm using this sql query

SELECT productlink FROM merchants
JOIN product ON product.productid=merchants.productid
WHERE product.productname='dahon 2012'

which outputs the merchants product links of those merchants who have product dahon 2012.

actual output of my test data

evansdh002.com
halfordsdh002.com

This seems to be the basic set up to compare prices.

Am I on the right track?

 

[retired_member21]

Looks good - just order it by price (lowest first - ascending) and you're away. As I said in a slightly overcomplicated post earlier - you are matching product names (string comparison) It just gets a bit more complicated when the strings don't match otherwise it would be a doddle (e.g. Modern Warfare 3 vs MW3.) Great work tho am loving the thread - let us know when you make a sale !

 

[pbryd]

I'll assume I have a search box on the site which searches the 'productname' field in the product table.

Results are displayed, then the visitor selects a product.

I then run the query similar to the one above which displays the merchant names and prices who have the product.

 

[pbryd]

Just looking at a datafeed I downloaded.

It's 18mb and holds all the cycling stuff from Evans and Wiggle bike shops.

I only want to insert the folding bikes, there's a category for it in the xml file <mCat>Bikes|Folding Bikes</mCat>

Can I alter the sql insert query to only insert items from this category?

$xml = simplexml_load_file("foldingdatafeed_79329.xml");

foreach($xml->merchant->prod as $product){
$productid = $product->pId;
$name = $product->text->name;
$descrip = $product->text->desc;

$sql = "INSERT INTO product (productid, productname, productdescription)
VALUES ('$productid', '$name', '$descrip')"

 

[pbryd]

Well I've sorted adding only dahon branded products but I can't insert the merchant name and product id because these are nested outside the individual products in the xml table.

I'm going to have to give it some thought how I set the tables up.

 

[pbryd]

I've gave up with this price comparison for foldable bikes.

Merchants seem to just carry one brand or the other.

I've downloaded feeds from 4 merchants, and uploaded the folding bikes from each merchant to 4 separate tables (wiggle, halfords, evans, rutland)

I can use the UNION ALL statement in the query to select all 'dahon' models from the tables and display them in one html table sorted by price.

 

[pbryd]

This week I've been working on my foldable bikes site.

It's not live yet but I've uploaded some pages to test the php/mysql

It's not a true comparison site, on looking at the merchants I found they didn't have enough cross over products to compare.

I'm quite please that I worked out how to get a dynamic page for each product tho, thus cutting down the number of affiliate links from the main products tables.

Sample of my progress so far

http://foldablebikes.co.uk/shop/ternfoldablebikes.php

 

[retired_member21]

Great news on your site - I'll take a look now

I'm quite please that I worked out how to get a dynamic page for each product tho, thus cutting down the number of affiliate links from the main products tables.

Please be careful with this...make sure you have <30-50 products on a site unless you plan to write unique information on every product any more on a new domain will just get it penalised for 1-2 months it's incredibly frustrating.

 

[pbryd]

Thanks Nick

Just checked the database, there's 77 bikes in there.

The domain has been reg'd since 2008, it recently disappeared from google when I put a store burst site there.

I'll get some fresh content up and possible trim the bike numbers down a bit