选择并列出孩子和父母 - 替代 [英] SELECT and list children and parent - alternative

问题描述

可能的重复:
选择并列出子级和父级

我从 xception 那里收到了这个答复:

I received this answer from xception:

https://stackoverflow.com/a/12770593/445820

不幸的是，GROUP_CONCAT 不是我的解决方案，另一个问题太长了，因此我重新提出这个问题.

Unfortunately, GROUP_CONCAT isn't the solution for me and the other question is too extended, therefore I am re-asking this question.

我需要一个带有子查询的查询的替代方法.由于我不太擅长查询，因此这项任务也超出了我的能力范围.

I need an alternative to that query with subqueries. Since I'm not good enough with queries, this task is beyond my abilities too.

请帮忙.

代码如下:

// Prepare query
$columns = "c.rule_id, c.rule_title, GROUP_CONCAT(r.rule_description ORDER BY r.rule_position ASC SEPARATOR '~sep~') AS rule_desc, ";
$columns .= "GROUP_CONCAT(r.parse_bbcode ORDER BY r.rule_position ASC SEPARATOR '~sep~') AS bbcode, ";
$columns .= "GROUP_CONCAT(r.parse_links ORDER BY r.rule_position ASC SEPARATOR '~sep~') AS links, ";
$columns .= "GROUP_CONCAT(r.parse_smilies ORDER BY r.rule_position ASC SEPARATOR '~sep~') AS smilies";

$sql_array = array(
    'SELECT'    => $columns,
    'FROM'      => array(RULES_TABLE => 'c'),
    'LEFT_JOIN' => array(
        array(
            'FROM'  => array(RULES_TABLE => 'r'),
            'ON'    => 'r.parent_id = c.rule_id',
        ),
    ),
    'WHERE'     => 'c.parent_id = 0 AND r.public = 1',
    'GROUP_BY'  => 'c.rule_id',
    'ORDER_BY'  => 'c.cat_position',
);

$sql = $db->sql_build_query('SELECT', $sql_array);
$result = $db->sql_query($sql);

$cat_count      = 1;
$alpha_count    = 'abcdefghijklmnopqrstuvwxyz';

while ($row = $db->sql_fetchrow($result))
{
    $template->assign_block_vars('rules', array(
        'RULE_CATEGORY' => $row['rule_title'],
        'ROW_COUNT'     => $cat_count,
    ));

    $rules_ary      = explode('~sep~', $row['rule_desc']);
    $parse_bbcode   = explode('~sep~', $row['bbcode']);
    $parse_links    = explode('~sep~', $row['links']);
    $parse_smilies  = explode('~sep~', $row['smilies']);
    $counter        = 0;

    foreach ($rules_ary as $key => $rule)
    {
        $uid = $bitfield = $options = '';
        generate_text_for_storage($rule, $uid, $bitfield, $options, $parse_bbcode[$key], $parse_links[$key], $parse_smilies[$key]);

        $template->assign_block_vars('rules.rule', array(
            'RULE_DESC'     => generate_text_for_display($rule, $uid, $bitfield, $options),
            'ALPHA_COUNT'   => $alpha_count{$counter},
        )); 

        $counter++;
    }

    $cat_count++;
}
$db->sql_freeresult($result);

您可能不知道某些功能.仅供参考，这是与 phpBB 相关的代码.

Some of the functions might be unknown to you. FYI this is phpBB related code.

抱歉没有在第一时间添加代码.

Sorry for not adding the code in the first time.

推荐答案

// Prepare query
$columns = "c.rule_id, c.rule_title, r.rule_description AS rule_desc, ";
$columns .= "r.parse_bbcode AS bbcode, ";
$columns .= "r.parse_links AS links, ";
$columns .= "r.parse_smilies AS smilies";

$sql_array = array(
    'SELECT'    => $columns,
    'FROM'      => array(RULES_TABLE => 'c'),
    'LEFT_JOIN' => array(
        array(
            'FROM'  => array(RULES_TABLE => 'r'),
            'ON'    => 'r.parent_id = c.rule_id',
        ),
    ),
    'WHERE'     => 'c.parent_id = 0 AND r.public = 1',
    'ORDER_BY'  => 'c.cat_position',
);

$sql = $db->sql_build_query('SELECT', $sql_array);
$result = $db->sql_query($sql);

$cat_count      = 0;
$alpha_count    = 'abcdefghijklmnopqrstuvwxyz';

$prev_rule_id   = 0;
$r_rule_titles  = array();

$rule_id = null;

while ($row = $db->sql_fetchrow($result))
{
    if( $rule_id != $row['rule_id'] ) {
            $rule_id = $row['rule_id'];
        $cat_count++;
        $counter = 0;
        $template->assign_block_vars('rules', array(
            'RULE_CATEGORY' => $row['rule_title'],
            'ROW_COUNT'     => $cat_count,
        ));
    }
    $uid = $bitfield = $options = '';
    generate_text_for_storage($row['rule_desc'], $uid, $bitfield, $options, $row['bbcode'], $row['links'], $row['smilies']);

    $template->assign_block_vars('rules.rule', array(
        'RULE_DESC'     => generate_text_for_display($row['rule_desc'], $uid, $bitfield, $options),
        'ALPHA_COUNT'   => $alpha_count{$counter},
    ));

    $counter++;    
}
$db->sql_freeresult($result);

这篇关于选择并列出孩子和父母 - 替代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！