Member Avatar for amith_ami

I have affiliate api of amazon,ebay,flipkart and snapdeal. I want to combine all api results to my web page with pagination. Like this website If possible please suggest any ideas. I am using opencart for development.

  • 2 Contributors
  • 3 Replies
  • 486 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by cereal

Dani AI

Generated

As wants to merge multiple affiliate feeds into one paginated listing (and already hinted at a product-feed model), treat this as a small ETL pipeline: fetch each API, normalize into a canonical schema, deduplicate/match products, index aggregated data for fast queries, then serve paginated results. Building it yourself (instead of an extension) is fine—just isolate ingestion, matching, and presentation so each part can scale independently.

Start with a canonical schema you control. Example simplified table for raw feed rows:

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  canonical_id INT NULL,
  source VARCHAR(32) NOT NULL,
  source_id VARCHAR(128) NOT NULL,
  title VARCHAR(512),
  brand VARCHAR(128),
  model VARCHAR(128),
  gtin VARCHAR(32),
  price DECIMAL(10,2),
  currency CHAR(3),
  affiliate_url TEXT,
  image_url TEXT,
  last_updated TIMESTAMP,
  UNIQUE KEY ux_source_sourceid (source, source_id)
);

Keep a separate canonical_products table that groups duplicates and stores aggregated fields (best_price, cheapest_source, canonical_title). Ingestion should be a cron/queue job per source that respects rate limits, normalizes fields, and does an upsert on (source, source_id).

For pagination and sorting use a precomputed aggregate (avoid heavy GROUP BY on every request). Example join to show canonical rows with best price, and keyset pagination for performance:

SELECT p.*, s.best_price
FROM canonical_products p
JOIN (
  SELECT canonical_id, MIN(price) AS best_price
  FROM products
  GROUP BY canonical_id
) s ON s.canonical_id = p.id
WHERE s.best_price > :last_price
   OR (s.best_price = :last_price AND p.id > :last_id)
ORDER BY s.best_price, p.id
LIMIT 25;

Practical cautions: prefer keyset over OFFSET for large sets; handle currency conversion and stale-price TTLs; build robust fuzzy-matching fallback (ASIN/GTIN first, then normalized title+brand with trigram/Levenshtein); cache rendered pages or results in Redis; bulk upsert in batches to reduce DB churn. Finally, verify each affiliate program’s caching/display rules and always preserve affiliate tracking parameters when rewriting links.

Recommended Answers

All 3 Replies

Member Avatar for amith_ami

cereal thanks for your reply. Yeah like this, but I don't want an extension. Is there any other method ?

Member Avatar for cereal

You're welcome. I'm not really into OpenCart, but reading the documentation, it seems you need to develop a Product Feed with a model, so you can define your database schema and associate the results of the affiliate programs. See if this helps:

Or wait for other suggestions. Bye!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.