SQL将结果转换为codeigniter中的对象 [英] SQL join results into an object in codeigniter

问题描述

确定，有点背景，

• 只是进入codeigniter


• sql和服务器端脚本


• 我知道什么是连接


• 我第一次有一个多对多数据库

• just into codeigniter
• not a fan of sql and server-side scripts
• i know what joins are
• i have a many-to-many database for the first time

这是因为联接通常具有以下示例作为结果。但我想解析这个而不必构建代码来忽略重复。它是一个3表连接样本。重复值的问题随着我加入更多表而增加：

it's because joins typically have the following example as a result. but i wanted to parse this without having to build code to ignore repetitions. it's a 3-table join sample. the issue of repeating values increases as i join more tables:

table1.authorid    table1.authorname    table2.books     table3.favorited
       1                 john           john's book 1        jean
       1                 john           john's book 1        joe
       1                 john           john's book 2        ken
       1                 john           john's book 2        mark
       2                 mark           mark's book 1        alice
       2                 mark           mark's book 1        ted
       2                 mark           mark's book 2        sarah
       2                 mark           mark's book 2        denise

有一种方式在codeigniter（或纯PHP），我可以得到这个数组形式，并把它变成像json（和解析它像json）

is there a way in codeigniter (or plain PHP) that i can get this array form and turn it into something like json (and parse it like json)

$result = [
    {
        'authorid':1,
        'authorname':'john',
        'books':['john's book1','john's book2'],
        'favorited':['jean','joe','ken','mark']
    },
    {
        'authorid':2,
        'authorname':'mark',
        'books':['mark's book1','mark's book2'],
        'favorited':['alice','ted','sarah','denise']
    }
]

b $ b

更新：这不限于此深度的对象/数组（如示例中所示）。它可以更深入（数组中的数组，对象中的数组，数组中的对象，对象中的对象）

Update: this is not limited to this depth of objects/arrays (like in the example). it can go deeper (arrays in arrays, arrays in objects, objects in arrays, objects in objects)

推荐答案

// first, we need the SQL results in the $result_array variable
$sql = 'SELECT ...';  // your SQL command
$result_array = $this->db->query($sql)->result_array();  // codeigniter code

// here the real answer begins
$result = array();

foreach ($result_array as $row)
{
    if (!isset($result[$row['authorid']])
    {
        $author = new StdClass();
        $author->authorid = $row['authorid'];
        $author->authorname = $row['authorname'];
        $author->books = array($row['books']);
        $author->favorited = array($row['favorited']);
        $result[$row['authorid']] = $author;
    }
    else
    {
        if (!in_array($row['books'], $result[$row['authorid']]->books))
        {
            $result[$row['authorid']]->books[] = $row['books'];
        }
        if (!in_array($row['favorited'], $result[$row['authorid']]->favorited))
        {
            $result[$row['authorid']]->favorited[] = $row['favorited'];
        }
    }
}

$result = array_values($result);
echo json_encode($result);

这篇关于SQL将结果转换为codeigniter中的对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！