dongyan4157 2012-02-06 05:57
浏览 61
已采纳

SQL连接结果成为codeigniter中的对象

ok, a bit of background,

  • 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

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

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']
    }
]

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)

  • 写回答

1条回答 默认 最新

  • dongpo2458 2012-02-06 06:21
    关注
    // 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);
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 我这模型写的不对吗?为什么lingo解出来的下面影子价格这一溜少一个变量
  • ¥50 树莓派安卓APK系统签名
  • ¥15 maple软件,用solve求反函数出现rootof,怎么办?
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题
  • ¥20 求一个html代码,有偿
  • ¥100 关于使用MATLAB中copularnd函数的问题
  • ¥20 在虚拟机的pycharm上
  • ¥15 jupyterthemes 设置完毕后没有效果
  • ¥15 matlab图像高斯低通滤波