PDA

View Full Version : MySQL driven AWS site



Best.Flash
03-24-2004, 03:56 PM
Here goes.....

http://www.coffee-maker-store.com/

The idea behind the site is its using PHP to populate MySQL with the AWS feed once every 7 hours. All the items and images are stored locally which should improve the load times also Amazon can spit out its dummy and the site still functions.

It uses LinksSQL (http://www.gossamer-threads.com/scripts/links-sql/index.htm) to organise the data into (what im hoping will be as far as the SE's are concerned) unique content by customising the reviews Checking the latest web services TOS I see it is not allowed to change reviews - luckily I didn't get round to that so no harm done and allowing visitors to add new reviews and shortening the anchor text for items. Another benefit of using this system is it only lists items that are shipping and visitors can search on any MySQL field listed.

Would be very interested in hearing your views and suggestions on the functionality and design.

Kyle
03-24-2004, 04:21 PM
Wow, I really like the site. Great layout.
I think you need to include the words CHECKOUT or a nice big image that says this.

Maybe put it in the header navigation, as well as when the shopper view's their Shopping Cart.

IMO, it's better to have a few different links that all mean the same thing when it comes to checking out. This especially applies to affiliate sites like yours.

GCT13
03-24-2004, 04:36 PM
A coffee AWS site. That's quite nice.

Why are you updating every 7 hours? Seems quite frequent.

I bet if the "Post a review on this product" form was on the bottom of the product page itself (instead of a link) you'd get a lot more reviews posted.

Good luck,

incka
03-25-2004, 12:31 AM
Very nice site... Are we going to be seeing a tutorial on how to do a MySQL one? That would really be good for the AWS site I'm currently designing...

chromate
03-25-2004, 05:35 AM
Yeah. Cool idea. Coffee stuff seems to sell quite well too in my experience.

Best.Flash
03-25-2004, 07:36 AM
I like the checkout button on each page and review form ideas, will look into adding these on the next update.


Originally posted by GCT13
Why are you updating every 7 hours? Seems quite frequent.

It is, out of about 1500 items only a handful change price / availability each day. Its down to Amazon's TOS, they request affiliates storing product info locally to update every 7 hours. :eek:

Sean, doubt ill have time to write a tutorial for a good while - if you've got any questions tho ill be happy to help.

Thanks guys!

incka
03-25-2004, 09:43 AM
Um, well my site is based on reviews, it's a review site with an amazon button, not a store with reviews if you get what I mean. So I would like to do the add review thing, but if it is too complicated I will stick with the amazon reviews, some of the products have over 1000 reviews already...

Best.Flash
03-25-2004, 02:23 PM
It depends how complicated you want to make it. The PHP that populates MySQL it very similar to AWS tutorial Dan wrote.

For the products table here's some columns you'll need:

ID (auto_increment)
Title
ASIN (set to unique to avoids duplicate listings)

and for the Reviews:

ID (auto_increment)
For_ASIN (which product is this review for)
Subject
Rating
Review

One thing to look out for is some of the Product Names use comma's and other characters that cause MySQL to return an error. To avoid this you can use addslashes() function in PHP, example:


if (empty($i['ProductName'])) {
$Title = "null";
} else {
$Title = addslashes($i['ProductName']);
}

Here's how i setup the page that adds the items automatically.

Near the top of your script set or retrieve the page set in the usual way:
if (!$_GET['page']) { $page = 1; } else { $page = $_GET['page']; }

Set the total items variable:
$total = $result['TotalResults'];

and the total pages:
$maxPages = ceil($total/10);

To avoid querying AWS more than once a second use a Javascript function to delay loading the next page set you want to load into MySQL:
if ($page >= $maxPages)
{
echo "Finished loading";
}
else
{

echo "Loaded page " .$page. " of " .$maxPages;

$page++; ?>

<SCRIPT LANGUAGE = "JavaScript">

function nextPage() {
window.location = "AWSfeed.php?page=" + <? echo $page ?>;
}

setTimeout("nextPage()", 1000);

</SCRIPT>

Aside from how you Insert into and structure your Tables thats about it.

sitepointRefugee
03-27-2004, 04:12 AM
The idea behind the site is its using PHP to populate MySQL with the AWS feed once every 7 hours. All the items and images are stored locally which should improve the load times also Amazon can spit out its dummy and the site still functions.
This is very well done. I love how each page responds and how quickly it loads.

A few comments:

1. Are there bigger pictures? Pictures look good, but personally I'm interested in seeing bigger pictures. It would be nice if JavaScript popup is available and it can bring up bigger pictures.

2. Contact Us. Wouldn't it make your site more credible if you add your contact information like street address and phone number besides email? Not everyone knows how AWS works, so many people might be thinking that they will be buying a coffee make from you directly. If that's the case, they might want to see street address and phone number.

3. Although the site is really nicely designed AND works really really well, there's really nothing besides the list of coffee makers. Wouldn't it be nice if you add some article or something? You don't have to write any article or take any picture; there might be some content related to coffee and/or coffee makers that you can take without paying for it.

Best.Flash
03-27-2004, 11:04 AM
Originally posted by sitepointRefugee
This is very well done. I love how each page responds and how quickly it loads.
Thanks. getting the pages to load faster for visitors and being able to customise the content excluding changing any imported Amazon content as inline with the latest web services TOS was the main incentive for running the site from MySQL.


1. Are there bigger pictures? Pictures look good, but personally I'm interested in seeing bigger pictures. It would be nice if JavaScript popup is available and it can bring up bigger pictures.

They are there, Im being lazy really should get them visable later this weekend.


2. Contact Us. Wouldn't it make your site more credible if you add your contact information like street address and phone number besides email? Not everyone knows how AWS works, so many people might be thinking that they will be buying a coffee make from you directly. If that's the case, they might want to see street address and phone number.

I agree, the trouble is im based in the UK. I get the feeling listing my companies address will cause more harm than good as visitors may presume we're shipping from the UK.

It may help to check Amazon TOS to see if they have any objection to using their street address on the site with details of our association.


3. Although the site is really nicely designed AND works really well, there's really nothing besides the list of coffee makers. Wouldn't it be nice if you add some article or something?
There is a list of coffee recipes (http://www.coffee-maker-store.com/recipes/) ill be looking to add to each month.


You don't have to write any article or take any picture; there might be some content related to coffee and/or coffee makers that you can take without paying for it. [/B]
Adding some articles is a good idea. I see there's several threads here and on SPF about finding free content.

cheers,

Charlie