How to get another column values from MySQL Query in opencart3 admin panel?

Question

The code below uses 2 tables "category_path" and "category_description" to get id=>name of all categories and sub-categories. Im bad at mySql, so I would appreciate if you help me. In this function I need to also get values of 'cat_name' column FROM THE OTHER (third) table named 'category'

https://i.stack.imgur.com/1PC4A.jpg

public function getCategories($data = array()) {
    $sql = "SELECT cp.category_id AS category_id, 
                GROUP_CONCAT(cd1.name ORDER BY cp.level SEPARATOR '&nbsp;&nbsp;&gt;&nbsp;&nbsp;') AS name, 
                c1.parent_id, c1.sort_order 
            FROM " . DB_PREFIX . "category_path cp 
                LEFT JOIN " . DB_PREFIX . "category c1 ON (cp.category_id = c1.category_id) 
                LEFT JOIN " . DB_PREFIX . "category c2 ON (cp.path_id = c2.category_id) 
                LEFT JOIN " . DB_PREFIX . "category_description cd1 ON (cp.path_id = cd1.category_id) 
                LEFT JOIN " . DB_PREFIX . "category_description cd2 ON (cp.category_id = cd2.category_id)  
            WHERE cd1.language_id = '" . (int)$this->config->get('config_language_id') . "' 
            AND cd2.language_id = '" . (int)$this->config->get('config_language_id') . "'";

Did you try adding `c1.cat_name` into the column list of the select — RiggsFolly, Aug 10 '20 at 09:58
if i'd only knew where to add it in this mess)) Besides that, the 'cat_name' is in other table called 'category' — Andrew Nevskiy, Aug 10 '20 at 10:03

score 0 · Accepted Answer · answered Aug 10 '20 at 10:32

0

You are already using that category table in the first join and you gave it an alias of c1 so

SELECT cp.category_id AS category_id, c1.cat_name,
. . .

. . .

answered Aug 10 '20 at 10:32

RiggsFolly

93,638
21
103
149

oh thank you! I didn't know that `c1` is an alias of the exact table i need. First day with OpenCart. – Andrew Nevskiy Aug 10 '20 at 12:32

How to get another column values from MySQL Query in opencart3 admin panel?

1 Answers1