sql How construct Subquery

[MenMarcellino]

Hi,

i have to make a query with gives me this structure.

P=Array ( 
    [0] => Array ( 
        [id__dic] => 79 
        [id__shop] => 1 
        [id__lang] => 2 
        [symbol] => uoi 
        [parent_symbol] => Mich 
        [root_symbol] => 0 
        [folder] => 1 
        [oorder] => 2 
        [active] => 1 
        [prefix] => dfield 
        [name] => array(
                    [1] => arrw
                    [2] => arw
                    ) 
        [description] => array(
                    [1] => arrw
                    [2] => arw
                    ) 
        )
)

I created two queries like in bottom code, but i thing there is a better solution. Could you help me and give some suggestion?

.....

$sql = "
        SELECT dic.*, ldic.*
		FROM "._DB_PREFIX_."dp__shop dic
		LEFT JOIN "._DB_PREFIX_."dp__lang ldic 
            ON (dic.id_dp = ldic.id_dp 
                AND dic.id__shop = ldic.id__shop)
		WHERE dic.id_dp__shop = '".$id_shop."' 
            AND dic.symbol = '".$parent_s."' 
            AND ldic.id_dp__lang = '".$id_lang."' 
        ORDER BY dic.oorder ASC, dic.id_dp ASC
        ";
        $dic_BySymbol = Db::getInstance()->executeS($sql);

        $sqls  = "SELECT name, description";
        $sqls .= "FROM "._DB_PREFIX_."dp__lang";
        $sqls .= "WHERE id_dp__shop = '".$id_shop."'";
        $sqls .= "AND id_dp = '".$dic_BySymbol['0']['id__dic']."'";

        $dic_BySymbolSubqueryLang = Db::getInstance()->executeS($sqls);
        
        $dic_BySymbol['name'] = array();
        $dic_BySymbol['description'] = array();

    		foreach ($languages as $language)
                {
                $dic_BySymbol['name'][$language['id_lang']] = $dic_BySymbolSubqueryLang->name[$language['id_lang']];
                $dic_BySymbol['description'][$language['id_lang']] =  $dic_BySymbolSubqueryLang->description[$language['id_lang']];
                }

       return $dic_BySymbol;
    }

 

[Rolige]

Hello:

A MySQL query always return a table, and a table can be "converted" into a single level array. If you want to get the structure you describe you must use some nested queries as you present in your code. There is no other way directly with only one query.

Regards