CODEWITHSUNDEEP
mysqlselectjoinopencart
msmafra
msmafra

Reputation: 1714

How to make a SELECT with joins between tables in Opencart's database?

I'm playing with Opencart's 1.4.9.4 database. And as I'm not experienced with database I find the structure strange (hard,crazy). For example: if I need to show the model and the name of a product I need to join 2 tables product and product_description, and the same approach for category, manufacturers etc. There is a table is only two fields, product_to_category, fields: category_id and product_id. How many JOINs show I use, if I really should use JOINs, to make it work? But one really odd thing for is to make a query where I get the product ID, NAME and MODEL with CATEGORY NAME and MANUFACTURER NAME, I have no knowledge to achieve that. It would involve the table, at least I think: product, product_description, product_to_category, category_description and manufacturer. Sorry for the mess, but my mind is a mess now!

Upvotes: 1

Views: 2926

Answers (2)

msmafra
msmafra

Reputation: 1714

Sorry people I totally forgot about this: MySQL SELECT Duplicated rows from OpenCarts DataBase I had already asked that.

Upvotes: -2

Jay Gilford
Jay Gilford

Reputation: 15151

In opencart you join the product_id values together to get the full row of data (and use the where clause to set the correct language ID of the description, so

SELECT * FROM product p LEFT JOIN product_description pd ON p.product_id = pd.product_id WHERE pd.language_id = 1 LIMIT 20

That would grab all the data from both tables and join them correctly using a limit of 20 products and the language 1 (default of english). Of course, when writing it properly in php, you will need to call some data dynamically (such as the table prefix and the language id) so it will work should you have multiple languages or database tables with prefixes. Something like

$query = "SELECT * FROM `" . DB_PREFIX . "product` p LEFT JOIN `" . DB_PREFIX . "product_description` pd ON p.product_id = pd.product_id WHERE pd.language_id = " . (int)$this->config->get('config_language_id') . " LIMIT 20";

Upvotes: 3

Related Questions