Guideline new coding built up

Temporary forum to obtain support for MODs/Styles while phpbb.com is down
Locked
parasolx
Registered User
Posts: 10
Joined: Mon Feb 02, 2009 3:07 am

Guideline new coding built up

Post by parasolx » Mon Feb 02, 2009 3:57 am

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

User avatar
A_Jelly_Doughnut
Registered User
Posts: 1780
Joined: Wed Jun 04, 2003 4:23 pm

Re: Guideline new coding built up

Post by A_Jelly_Doughnut » Mon Feb 02, 2009 4:01 am

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
A_Jelly_Doughnut

parasolx
Registered User
Posts: 10
Joined: Mon Feb 02, 2009 3:07 am

Re: Guideline new coding built up

Post by parasolx » Mon Feb 02, 2009 4:06 am

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
Registered User
Posts: 26
Joined: Mon Feb 02, 2009 8:19 pm
Location: phpbb_
Contact:

Re: Guideline new coding built up

Post by a_o_c » Mon Feb 02, 2009 8:40 pm

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");

User avatar
Highway of Life
Registered User
Posts: 1399
Joined: Tue Feb 08, 2005 10:18 pm
Location: I'd love to change the World, but they won't give me the Source Code
Contact:

Re: Guideline new coding built up

Post by Highway of Life » Sat Feb 07, 2009 1:09 am

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); 
Image

a_o_c
Registered User
Posts: 26
Joined: Mon Feb 02, 2009 8:19 pm
Location: phpbb_
Contact:

Re: Guideline new coding built up

Post by a_o_c » Sat Feb 07, 2009 1:58 am

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

Locked