Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL join results into an object in 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)

like image 775
Joseph Avatar asked Feb 06 '12 05:02

Joseph


1 Answers

// 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);
like image 97
J. Bruni Avatar answered Sep 28 '22 00:09

J. Bruni