Retrieve Products By Category from OpenCart

Question

How do I write a PHP snippet to read an OpenCart database to pull products by category and display it outside of an OpenCart framework? So for instance, I build this nice frontend website for widgets A and B, but then have a subdir called /cart where OpenCart is installed, and in my frontend website I load some kind of OpenCart library file and then read the database to get products by category (A and B). Or, perhaps you know the technique with direct SQL itself?

This question is different than this one because I'm trying to not build a new template in OpenCart, but use an API (or go direct to the database) to show products outside of OpenCart's application framework and in my own application framework.

Answer 1

I tried asking this over at the OpenCart forums, but oddly the mods deleted that post without explanation, and never told me why. So, I had no choice but to go digging for an answer from the code since the developer docs were poor for this kind of question. What I found was that the product catalog for OpenCart can be accessed by either looking at the class methods in the following file, or by loading that class file within your other web application. (As for how to load that class file, that will take some work to figure out -- I don't know yet.)

In your OpenCart 2.x directory, look for this file:

catalog/model/catalog/product.php

You'll find out from the SQL code how OpenCart gets a list of products and then how to get the product detail on a single product.

Answer 2

If you want the fastest way, building links like

https://your-shop.com.au/index.php?route=product/product&product_id=1579

you can get the product_id for the category "230" like this:

SELECT `oc_product`.`product_id`,`name`,`description`,`image` 
FROM `oc_product`,`oc_product_description` 
JOIN `oc_product_to_category` 
    ON `oc_product_to_category`.`product_id` = `oc_product_to_category`.`product_id`  
WHERE
    `oc_product_to_category`.`category_id` = '230' 
    AND `oc_product`.`status` = '1'

If you want SEO links use an array of product ids and process this query:

SELECT * 
FROM `oc_url_alias` 
WHERE 
    `query` LIKE '%PRODUCT_ID%' 
ORDER BY `url_alias_id` DESC

Then you can do

https://your-shop.com.au/keyword

Answer 3

Here another query to get products from store 0, manufacturer 17, category 230 and posibility to build friendly SEO urls:

 SELECT `oc_product`.`product_id`,`name`,`description`,`image`,`keyword`FROM `oc_product`
LEFT JOIN `oc_product_to_category`
   ON `oc_product_to_category`.`product_id` = `oc_product`.`product_id`
LEFT JOIN `oc_product_description`
   ON `oc_product_description`.`product_id` = `oc_product`.`product_id`
LEFT JOIN `oc_product_to_store`
   ON `oc_product_to_store`.`product_id` = `oc_product`.`product_id`
LEFT JOIN `oc_url_alias`
   ON (`oc_url_alias`.`query` LIKE  CONCAT('%',`oc_product`.`product_id`,'%'))
WHERE `oc_product_to_category`.`category_id` = '230' AND `oc_product`.`status` = '1' AND `oc_product`.`date_available` <= NOW() AND `oc_product`.`manufacturer_id`='17' AND `oc_product_to_store`.`store_id` = '0'
ORDER BY `oc_product`.`product_id` DESC LIMIT 10