Amazon and MySQL Mashup

Wed, 12/23/2009 - 15:50 — crounauer

Hi David,

It's been a while, hope you are well?

I am creating a micro niche affiliate site and get some results from amazon and some from a mySQL database.

In order to display the results for a particular product according to price I have created arrays for both sets of results (amazon and mysql) and then will try to merge them together before sorting and displaying them.

I have got as far as creating the arrays but I am having difficulty in merging the results together. I think I need to create a multi-dimensional array for amazon results first?

This is what I have so far, could you please give me a nudge in the right direction!

<?php
// Amazon
require("includes/MagicParser.php");
$amazonItems = array();
function myRecordHandler($item) {
    global $amazonItems;
    $amazonItems[] = $item;
  }

  MagicParser_parse($awsUrl,"myRecordHandler","xml|ITEMLOOKUPRESPONSE/ITEMS/ITEM/OFFERS/OFFER/");

// mySQL
$retailerItems = array();
$sql = "SELECT * FROM `".DB_PREFIX."prices` WHERE prod_id='".$prodId."' ORDER BY price ASC";
//echo "<p>SQL:".$sql."</p>";
if (database_querySelect($sql,$rows)) {
    foreach($rows as $products) {
        $retailerItems[] = $products;
    }
}

// Combine arrays
array_push($amazonItems,$retailerItems);
$finalarray = array_combine($amazonItems, $retailerItems);
print_r($finalarray);
?>

Thanks,
Simon

Wed, 12/23/2009 - 16:07 — dmorison

Hi Simon, What I would do is

Hi Simon,

What I would do is make sure that everything is in the same format at the time your create and add items to the array. As the field names in each Amazon $item are rather verbose; I would recommend making each Amazon $item identical to each $row from your `prices` table. Then the entire array can be sorted by price easily using using usort(). For example:

<?php
// Amazon
require("includes/MagicParser.php");
$products = array();
function myRecordHandler($item) {
    global $products;
    // make an array that will be identical to each row returned from your database products.
    $amazonItem = array();
    $price = $item["OFFERSUMMARY/LOWESTNEWPRICE/FORMATTEDPRICE"];
    $price = str_replace(",",".",$price);
    $price = preg_replace('/[^0-9\.]/e','',$price);
    $price = sprintf("%.2f",$price);
    $amazonItem["price"] = $price;
    $amazonItem["name"] = $item["ITEMATTRIBUTES/TITLE"];
    $products[] = $amazonItem;
  }
MagicParser_parse($awsUrl,"myRecordHandler","xml|ITEMLOOKUPRESPONSE/ITEMS/ITEM/OFFERS/OFFER/");

// mySQL
$retailerItems = array();
$sql = "SELECT * FROM `".DB_PREFIX."prices` WHERE prod_id='".$prodId."' ORDER BY price ASC";
//echo "<p>SQL:".$sql."</p>";
if (database_querySelect($sql,$rows)) {
    foreach($rows as $product) {
        $products[] = $product;
    }
}
// Sort array by price
function cmp($a, $b)
{
    if ($a["price"] == $b["price"]) {
        return 0;
    }
    return ($a["price"] < $b["price"]) ? -1 : 1;
}
usort($products,"cmp");
print_r($products);
?>

Hope this helps!
Cheers,
David.

Wed, 12/23/2009 - 16:59 — crounauer

Thanks David, Hope you have

Thanks David,

Hope you have a great Christmas and New Year. All the very best for 2010!!!

French Language Courses