How can I group arrays in an array in PHP and encode to JSON?

Question

I have 2 tables in MySql

Section

section_id       name
=====================
1              Section1
2              Section2

Category

    category_id        section_id     name
    =========================================
    1                  1           Category1
    2                  1           Category2
    3                  2           Category3
    4                  2           Category3

This is what I have now:

$sections = mysql_query($sql_section) or die("Could not execute query.");
$categories = mysql_query($sql_category) or die("Count not execute query");

$result = array('sectionlist' => array());
    for ($i=0; $i < mysql_num_rows($sections); $i++ ){
        for ($j=0; $j < mysql_num_rows($categories); $j++){
            if (mysql_result($sections,$i,"section_id") == mysql_result($categories,$j,"section_id")){
                $result['sectionlist'][] = array('sectionName' => mysql_result($sections,$i,"name"), 'categorylist' => array(array('category' => mysql_result($categories,$j,"name"))));
            }
        }
    }
echo json_encode($result);

The result has came out is like this:

  {sectionlist:[
    {sectionName: "Section1", categoryList: [{categoryName: "category1"}]},
    {sectionName: "Section1", categoryList: [{categoryName: "category2"}]},
    {sectionName: "Section2", categoryList: [{categoryName: "category3"}]},
    {sectionName: "Section2", categoryList: [{categoryName: "category4"}]},
  ]}

This wasn't the expected result from me, I would like to have an object that look like this:

{sectionlist:[
    {sectionName: "Section1", categoryList: [{categoryName: "category1"}, {categoryName: "category2"}]},
    {sectionName: "Section2", categoryList: [{categoryName: "category3"}, {categoryName: "category4"}]}
]}

Therefore, how can I group the section 1 with section 1 and section with section 2?

Please post the query. Also, avoid using the dated mysql_* functions. Using them for new code is highly discouraged. More modern alternatives are available and better maintained. Consider learning about prepared statements instead and use either PDO or MySQLi. When used strictly they avoid the tedious and manual escaping part, making them both easier and safer. See a PDO tutorial for starting. — gcochard
– gcochard, Commented Aug 17, 2012 at 0:09

Community · Accepted Answer · 2023-11-17 20:38:18Z

2

To answer your question, you want to create the array of categories first.

for ($i=0; $i < mysql_num_rows($sections); $i++ ){
    $categorylist = array();
    for ($j=0; $j < mysql_num_rows($categories); $j++){
        if (mysql_result($sections,$i,"section_id") == mysql_result($categories,$j,"section_id")){
            $categorylist[] = array('category' => mysql_result($categories,$j,"name");
        }
    }
    $result['sectionlist'][] = array('sectionName' => mysql_result($sections,$i,"name"), 'categorylist' => $categorylist));
}

As a sidenote, PHP encourages use of the other database extensions.

edited Nov 17, 2023 at 20:38

CommunityBot

11 silver badge

answered Aug 17, 2012 at 0:26

Phil

7,1562 gold badges21 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

brandwaffle · Accepted Answer · 2012-08-17 00:29:30Z

1

@Greg's comment is really important to take note of above. Also, I would recommend re-writing your query as a single query with a join in order to retrieve just the data you want. I'm not 100% sure exactly what you'll need, but it will probably be something like this:

SELECT section.section_id, category.name, section.name FROM category 
  LEFT JOIN section ON section.section_id = category.section_id;

That will return an array of category names keyed to the section_id and section name that they're associated with (you can also select the category_id if you'd like).

answered Aug 17, 2012 at 0:29

brandwaffle

1,2629 silver badges11 bronze badges

Collectives™ on Stack Overflow

How can I group arrays in an array in PHP and encode to JSON?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related