How can i get two result set array in single array without calling function again?

Question

Here I fire two queries by calling single function..

$category  = [];
$sub_category = [];

$query_parent_category = "select name from category where parent_id = 0";
$query_child_category="select name from category where parent_id = 1";
$category = $this->runSelectQuery($query_parent_categoty);
$sub_category = $this->runSelectQuery($query_child_categoty);

return array($category,$sub_category);

function runSelectQuery is like...

 public function runSelectQuery($sql) {
        $result = mysqli_query($this->con, $sql);
        $listinfdata = array();

        while ($row = mysqli_fetch_assoc($result)) {
            $listinfdata[] = $row;
        }

        return $listinfdata;
    }

but it is not lool optimize.. i need that two array in single function call .. which means $category[] and $sub_category[] in single array .. result like to be

Array
(
    [0] => Array
        (
            [name] => MEN
            [subcat] => Array
                (
                    [0] => Jeans
                    [1] => Shirts
                    [2] => T-shirts
                    [3] => Chinos
                    [4] => Blazers
                    [5] => Night Wear
                )

        )
)

and so on ..

wait are you merging them? if yes select name from category where parent_id in (0, 1) — Beginner
– Beginner, Commented Feb 20, 2017 at 6:40
table name category with id as primary key ,name as varchar(255) and parent_id as int (11)..it contains some data based on that i get "name" — Kishan Oza
– Kishan Oza, Commented Feb 20, 2017 at 6:42
(id name parent_id) (1 MEN 0), (2 jeans 1), (3 Shirts 1), (4 T-shirts 1), (5 Chinos 1), (6 Blazers 1), (7 Night Wear 1) 8 Women 0 9 KIDS 0 10 ACCESSORIES 0 — Kishan Oza
– Kishan Oza, Commented Feb 20, 2017 at 6:44
no i want that one array from which i can access the both like MEN has sub category of ->> jeans,shirts and so on — Kishan Oza
– Kishan Oza, Commented Feb 20, 2017 at 6:47
could you make it more clear like by making a php array result for example — Beginner
– Beginner, Commented Feb 20, 2017 at 6:52

Beginner · Accepted Answer · 2017-02-20 07:56:39Z

More Answer here...

How about if you have more than 1 parent category

like this

Use this query

SELECT `parent_category`.`id`, `parent_category`.`name` as `parent_category`, `child_category`.`name` as `child_category` FROM `category` `parent_category` join `category` `child_category` on `parent_category`.`id` = `child_category`.`parent_id`

This query produce result like this

To group your category first

declare a variable which contains the parent_id of your $row

$prev_parent = null;

let it be as null

then

in while loop

Create 3 condition(s)

First Condition: check if $prev_parent is null then create your first category

if ($prev_parent == null) {
    $prev_parent = $row['id'];
    $listinfdata[] = array(
        'name' => $row['parent_category'],
        'subcat' => array($row['child_category'])
    );
}

This condition would create something like this

Array
(
    [0] => Array
        (
            [name] => 'Parent Category 1'
            [subcat] => Array
                (
                    [0] => 'Child Category 1'
                )
        )
);

Second Condition: check if $row['id'] or parent_id is same as $prev_parent which your your previous parent id category

If that match to this condition just insert the subcategory to the latest subcategories of the latest parent category

`sizeof($listinfdata)-1` since array always started at index 0

Do this

$prev_parent = $row['id'];
$listinfdata[sizeof($listinfdata)-1]['subcat'][] = $row['child_category'];

This condition would create something like this

Array
(
    [0] => Array
        (
            [name] => 'Parent Category 1'
            [subcat] => Array
                (
                    [0] => 'Child Category 1',
                    [1] => 'Child Category 2'
                )
        )
);

Last Condition else means if $prev_parent is not empty but the current parent_category is not same to the $prev_parent insert another parent category with it's first sub category

$prev_parent = $row['id'];
$listinfdata[] = array(
    'name' => $row['parent_category'],
    'subcat' => array($row['child_category'])
);

This condition would create something like this

Array
(
    [0] => Array
        (
            [name] => 'Parent Category 1'
            [subcat] => Array
                (
                    [0] => 'Child Category 1',
                    [1] => 'Child Category 2'
                )
        ),
    [1] => Array
        (
            [name] => 'Parent Category 2'
            [subcat] => Array
                (
                    [0] => 'Child Category 1',
                )
        )
);

So your code would be like this

<?php

    $category  = [];
    $sub_category = [];
    $query_category = "SELECT `parent_category`.`id`, `parent_category`.`name` as `parent_category`, `child_category`.`name` as `child_category` FROM `category` `parent_category` join `category` `child_category` on `parent_category`.`id` = `child_category`.`parent_id`";

    $sub_category = $this->runSelectQuery($query_category);

    return array($sub_category);


function `runSelectQuery` is like...

public function runSelectQuery($sql) {
    $result = mysqli_query($this->con, $sql);
    $listinfdata = array();

    $prev_parent = null;
    while ($row = mysqli_fetch_assoc($result)) {
        $category = null;
        if ($prev_parent == null) {
            $prev_parent = $row['id'];
            $listinfdata[] = array(
                'name' => $row['parent_category'],
                'subcat' => array($row['child_category'])
            );
        } else if ($row['id'] == $prev_parent) {
            $prev_parent = $row['id'];
            $listinfdata[sizeof($listinfdata)-1]['subcat'][] = $row['child_category'];
        } else {
            $prev_parent = $row['id'];
            $listinfdata[] = array(
                'name' => $row['parent_category'],
                'subcat' => array($row['child_category'])
            );
        }
    }

    return $listinfdata;
}

And the result is like this

arjun045 · Accepted Answer · 2017-02-20 06:43:56Z

0

For this simply you could modify your query by using "OR" operator.

$category  = [];
$query_parent_categoty = "select name from category where parent_id = 0 OR parent_id = 1";
$category = $this->runSelectQuery($query_parent_categoty);
 return array($category);

answered Feb 20, 2017 at 6:43

arjun045

10312 bronze badges

2 Comments

Beginner Over a year ago

better use parent_id IN(0, 1) use it's much faster than OR

Kishan Oza Over a year ago

you are right but it does not match with my requirement.. i want menu and submenu implementation by using my two query

Saravanan Sampathkumar · Accepted Answer · 2017-02-20 06:47:56Z

0

Just like @beginner commented, using IN is better than OR

$query = "SELECT name FROM category WHERE parent_id IN (0,1)";
$category = $this->runSelectQuery($query);

answered Feb 20, 2017 at 6:47

Saravanan Sampathkumar

3,2711 gold badge22 silver badges46 bronze badges

1 Comment

Kishan Oza Over a year ago

i explain you by example $menu = [ ["name" => "home", "link" => "home.php", "sub_menu" => [ ["name" => "home1", "link" => "home.php", ], ["name" => "home2", "link" => "home.php", ] ] ] ]; ** in this menu variable "sub-menu" is there in wich i want to store the result of my second query **$query_child_categoty="select name from category where parent_id = 1";

Collectives™ on Stack Overflow

How can i get two result set array in single array without calling function again?

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related