Guideline new coding built up

[parasolx]

Hello,

I want to develop new MODX but first want to test it on my forum first. I need some guideline to built up those coding.

What i want to do is, i want to make query from table A (grouping) and list out all rows in table A into template. Then i have table B, which have related with table A. In table B it contain all data about my product and have one field which group using table A.

So, lets say in table A contain 3 groups, which are solid1, solid2 and solid3. How to make in the template it would produce like this:

Solid1: list out all product in table B belong to solid1

Solid2: list out all product in table B belong to solid2

Solid3: list out all product in table B belong to solid3

[A_Jelly_Doughnut]

I believe the GROUP BY keyword is what you're looking for.

Since I don't know what your table looks like, this is very generic:

Code: Select all

SELECT * FROM b
GROUP BY solid_type

[parasolx]

both group and products is in separate tables..

which are GROUPING in table A

and PRODUCT in table B.. does i need to make some LEFT JOIN??

[a_o_c]

correct. this is not your exact query, but should give you a head start (this is from one of my MODs).

Code: Select all

      $sql = 'SELECT u.*, b.*
         FROM ' . USERS_TABLE . ' u
         LEFT JOIN ' . BANLIST_TABLE . ' b ON (u.user_id = b.ban_userid)
         WHERE ' . (($username) ? "u.username_clean = '" . $db->sql_escape(utf8_clean_string($username)) . "'" : "u.user_id = $user_id");

[Highway of Life]

A_O_C, you should be using the sql_build_query for that type of query when using LEFT-JOIN's
e.g.:

Code: Select all

$sql_ary = array(
    'SELECT'    => 'u.*, b.*',
    'FROM'      => array(USERS_TABLE => 'u'),
    'LEFT_JOIN' => array(
        array(
            'FROM'  => array(BANLIST_TABLE => 'b'),
            'ON'    => 'u.user_id = b.ban_userid',
        ),
    ),
    'WHERE'     => ($username) ? "u.username_clean = '" . $db->sql_escape(utf8_clean_string($username)) . "'" : 'u.user_id = ' (int) $user_id,
);
$sql = $db->sql_build_query('SELECT', $sql_ary);
$result = $db->sql_query($sql); 

[a_o_c]

bah, i must learn to read up on that (keep forgetting to do it). thanks for the reminder HoL.