How to let affiliates query a database of prices?

...without giving them access to the database itself

[MichaelBluejay]

There's a bookstore I like (not Amazon) which I'd like to promote as an affiliate, but I want to list individual books and their prices, and the prices of course are subject to change. I'd like to help this bookseller implement some kind of technology to let its affiliates query their database to get current prices to list on the affiliates' websites. The question is how to do this....

In general, my idea is that the affiliate's page code would look something like this:

<P>Buy this book for $<some script looking up the price for item=238749288></P>

One idea is that each affiliate could install a PHP or Perl script that queried the database, but I think for that to happen the script would need the login for the database which is an obvious security problem. So unless I'm missing something that wouldn't work.

Another idea is to have a Javascript get the data, but I thought Javascript couldn't access databases. Then again, Google delivers its Adsense content with Javascript somehow, so maybe this is the path to take?

[Dijkgraaf]

I'd suggest creating a XML web service which they can query which only exposes the data that you want them to have.

I haven't looked at Google adsense, but probably the JavaScript is being generated and served up by another scripting language which does have DB access.

[MichaelBluejay]

I'd never heard of web services before this. I looked it up and all the sites I found about it are huge and confusing -- definitely nothing that has a simple example of how a website can query simple data from another server. Any suggestions for sites or books that would make this less painful?

[dmorison]

A remote JavaScript that works in a similar way to AdSense would be the easiest way to let non-technical affiliates (who would have trouble using a full on web-service aproach) incorporate live data into their pages.

It is straight forward to implement. Your affiliates, provided that they know the product ID simply create HTML almost exactly as you suggest:

<p>Our Price: <script src='http://www.example.com/getprice.php?item=238749288' /></p>

That's all the affiliate needs to do. Now, on the merchant's server (www.example.com), the script getprice.php simply returns the header "Content-Type: text/javascript", performs a database query to get the price for item requested and then outputs something like:

document.write("USD 34.99");

That's basically it! I'm just using PHP as an example of course, it would be straight forward using any scripting language or server side dynamic page technology that you or your developers has available.

[MichaelBluejay]

Thanks, that's exactly what I was looking for! What was missing from my understanding is that I didn't know that <script src> could call a PHP or Perl file. I just tried it and it works perfectly.

Incidentally, while poking around I discovered that MySQL users can be set up to have browse access to a database but not write access. So allowing affiliates to access the database directly would also have been an option, had <script src> not been even easier.