Manipulate ResultSet Array and make it Multidimensional

Question

I am working on a function which fetch data from three table based on Left outer join, which is as follows:

public function get_full_category_history($id) {
        $id = mysqli_real_escape_string($this->_con,$id);
        if (isset($id) && $id != "") {
            $query = "SELECT qz_categories.id as category_id, qz_categories.name as category_name, qz_quizzes.id as quiz_id, qz_quizzes.name as quiz_name, qz_questions.id as question_id, qz_questions.question as question FROM qz_categories LEFT OUTER JOIN qz_quizzes ON qz_categories.id = qz_quizzes.category_id LEFT OUTER JOIN qz_questions ON qz_quizzes.id = qz_questions.quiz_id WHERE ( qz_categories.id = '".$id."')";
        }
        $categoriesList = mysqli_query($this->_con, $query) or die(mysqli_error($this->_con));

        $count = $categoriesList->num_rows;
        $result = '';
        $i = 0;
        if ($count > 0) {
            while($row = mysqli_fetch_array($categoriesList)){
                $result[$i]['category_id'] = $row['category_id'];
                $result[$i]['category_name'] = $row['category_name'];
                $result[$i]['quiz_id'] = $row['quiz_id'];
                $result[$i]['quiz_name'] = $row['quiz_name'];
                $result[$i]['question_id'] = $row['question_id'];
                $result[$i]['question'] = $row['question'];
                $i++;
            }
            return $result;
        }else{
                return FALSE;
            }
    }

After doing this I got this array:

Array
(
    [0] => Array
        (
            [category_id] => 1
            [category_name] => Preschool
            [quiz_id] => 1
            [quiz_name] => What is Forex?
            [question_id] => 1
            [question] => In forex, what are the three main types of analysis?
        )

    [1] => Array
        (
            [category_id] => 1
            [category_name] => Preschool
            [quiz_id] => 1
            [quiz_name] => What is Forex?
            [question_id] => 2
            [question] => Which type of analysis looks at historical price movements to determine the current trading conditions?
        )

    [2] => Array
        (
            [category_id] => 1
            [category_name] => Preschool
            [quiz_id] => 2
            [quiz_name] => Why Trade Forex?
            [question_id] => 
            [question] => 
        )

)

But what I want is:

Array
(
    [0] => Array
        (
            [category_id] => 1
            [category_name] => Preschool
            ['Preschool']=>Array(
                0=> array(
                            [quiz_id] => 1
                            [quiz_name] => What is Forex?
                            [What is Forex?]=>Array(
                                    0=>array(
                                                [question_id] => 1
                                                [question] => In forex, what are the three main types of analysis?
                                    ),
                                    1=>array(
                                                [question_id] => 2
                                                [question] => Which type of analysis looks at historical price movements to determine the current trading conditions?
                                    )
                                )
                ),
                1=>array(
                            [quiz_id] => 2
                            [quiz_name] => Why Trade Forex?
                            [Why Trade Forex?]=>Array(

                                )
                )
        )
    )
);

How to do that?

array without index ??? just look after What is Forex?

Akshay Hegde
– Akshay Hegde

2015-06-12 08:37:28 +00:00
Commented Jun 12, 2015 at 8:37 — Akshay Hegde
– Akshay Hegde, Commented Jun 12, 2015 at 8:37
sorry let me edit it. thanks for telling.

Death-is-the-real-truth
– Death-is-the-real-truth

2015-06-12 08:44:39 +00:00
Commented Jun 12, 2015 at 8:44 — Death-is-the-real-truth
– Death-is-the-real-truth, Commented Jun 12, 2015 at 8:44
upvoted to cancel out the drive-by downvote

Akshay Hegde
– Akshay Hegde

2015-06-12 11:50:06 +00:00
Commented Jun 12, 2015 at 11:50 — Akshay Hegde
– Akshay Hegde, Commented Jun 12, 2015 at 11:50

PHP Worm... · Accepted Answer · 2015-06-12 11:37:21Z

Check this:

It will give you the same output as you require: and it will not add empty question array

I have also added another "category_id" => 2 and it will be added to the next index of main array.

$array = Array
(
    0 => Array
        (
            "category_id" => 1,
            "category_name" => 'Preschool',
            "quiz_id" => 1,
            "quiz_name" => 'What is Forex?',
            "question_id" => 1,
            "question" => 'In forex, what are the three main types of analysis?',
        ),
    3 => Array
        (
            "category_id" => 2,
            "category_name" => 'Preschool2',
            "quiz_id" => 1,
            "quiz_name" => 'What is Forex?',
            "question_id" => 1,
            "question" => 'In forex, what are the three main types of analysis?',
        ),

    1 => Array
        (
            "category_id" => 1,
            "category_name" => 'Preschool',
            "quiz_id" => 1,
            "quiz_name" => 'What is Forex?',
            "question_id" => 2,
            "question" => 'Which type of analysis looks at historical price movements to determine the current trading conditions?',
        ),

    2 => Array
        (
            "category_id" => 1,
            "category_name" => 'Preschool',
            "quiz_id" => 2,
            "quiz_name" => 'Why Trade Forex?',
            "question_id" => '',
            "question" => ''
        )

);  




  function myfun($array){
$result= array();   
$insertTo = 0;
foreach($array as $key=>$value){
    $qArray = array('question_id' => $value['question_id'],'question' => $value['question']);
    if(!empty($result)){
        $check=false; 
        foreach($result as $k=>$v){
            if($value['category_id'] == $v['category_id']){
                if($value['question_id'] != '' && $value['question_id'] != ''){
                    $result[$k][$v['category_name']][$value['quiz_name']][]=$qArray;                
                }
                $check=true; 
            }
        }   
    if(!$check){
        $insertTo = count($result);
        $result[$insertTo]['category_id'] = $value['category_id'];
        $result[$insertTo]['category_name'] = $value['category_name'];
        $result[$insertTo][$value['category_name']]['quiz_id'] = $value['quiz_id'];
        $result[$insertTo][$value['category_name']]['quiz_name'] = $value['quiz_name'];
        if($value['question_id'] != '' && $value['question_id'] != ''){
            $result[$insertTo][$value['category_name']][$value['quiz_name']][] = $qArray;
        }
    }       
    }else{
        $result[$insertTo]['category_id'] = $value['category_id'];
        $result[$insertTo]['category_name'] = $value['category_name'];
        $result[$insertTo][$value['category_name']]['quiz_id'] = $value['quiz_id'];
        $result[$insertTo][$value['category_name']]['quiz_name'] = $value['quiz_name'];
        if($value['question_id'] != '' && $value['question_id'] != ''){
            $result[$insertTo][$value['category_name']][$value['quiz_name']][] = $qArray;
        }
    }

}
return $result;
}
    $result = myfun($array);
    echo '<pre>';
    print_r($result);

Output

enter image description here

Akshay Hegde · Accepted Answer · 2015-06-12 11:05:11Z

2

This may help you, I am using your $result array as input, I know you can easily play with this.

 [akshay@localhost tmp]$ cat test.php
 <?php

 $result = array (
   0 => 
   array (
     'category_id' => '1',
     'category_name' => 'Preschool',
     'quiz_id' => '1',
     'quiz_name' => 'What is Forex?',
     'question_id' => '1',
     'question' => 'In forex, what are the three main types of analysis?',
   ),
   1 => 
   array (
     'category_id' => '1',
     'category_name' => 'Preschool',
     'quiz_id' => '1',
     'quiz_name' => 'What is Forex?',
     'question_id' => '2',
     'question' => 'Which type of analysis looks at historical price movements to determine the current trading conditions?',
   ),
   2 => 
   array (
     'category_id' => '1',
     'category_name' => 'Preschool',
     'quiz_id' => '2',
     'quiz_name' => 'Why Trade Forex?',
     'question_id' => '',
     'question' => false,
   ),
 );

 $output = array();

 foreach( $result as $index => $row)
 {
    $inner_2 = array('question_id'=>$row['question_id'],'question'=>$row['question']);
    $inner_1 = array('quiz_id'=>$row['quiz_id'],'quiz_name'=>$row['quiz_name'], $row['quiz_name']=> array($inner_2));

    if(isset($output[$row['category_name']]))
    {
        $key = array_search($row['quiz_id'],array_column($output[$row['category_name']][$row['category_name']],"quiz_id"));

        if( $key !== false )
        {
            $output[$row['category_name']][$row['category_name']][$key][$row['quiz_name']][] = $inner_2; 
        }else
        {
            $output[$row['category_name']][$row['category_name']][] = $inner_1 ;
        }

    }else
    {
       $output[$row['category_name']] = array('category_id'=>$row['category_id'],'category_name'=> $row['category_name'],$row['category_name']=>array($inner_1));
    }
 }

 // Input 
 print_r($result);

 // Output
 print_r(array_values($output));

 ?>

Output

-- For comments (since may be older version of php)--

if(!function_exists("array_column"))
{
    function array_column($array,$column_name)
    {
        return array_map(function($element) use($column_name){return $element[$column_name];}, $array);
    }
}

Demo

edited Jun 12, 2015 at 11:05

answered Jun 12, 2015 at 9:26

Akshay Hegde

17.1k2 gold badges25 silver badges39 bronze badges

6 Comments

Death-is-the-real-truth Over a year ago

Fatal error: Call to undefined function array_column() in D:\xampp\htdocs\quiz\lib\quiz.class.php on line 1322

Akshay Hegde Over a year ago

Mostly you are using old php please paste this function and try

Death-is-the-real-truth Over a year ago

Can you tell me any modification ,because it's running on live server and upgradation is quite lengthy.

Death-is-the-real-truth Over a year ago

I up voted because it will work for latest version, but not in my case.thanks.

Death-is-the-real-truth Over a year ago

As you said i already did that before modification (create a function just like you), but weared condition that it still shows same fetal error. i tried your code on higher version and since it works that why i up-voted you,but it's not the ultimate solution for me. thanks again for your effort.

|

Ajeet Kumar · Accepted Answer · 2015-09-29 06:48:02Z

Can do like this shortest method

Input & Code

 $array = Array
(
    0 => Array
        (
            "category_id" => 1,
            "category_name" => 'Preschool',
            "quiz_id" => 1,
            "quiz_name" => 'What is Forex?',
            "question_id" => 1,
            "question" => 'In forex, what are the three main types of analysis?',
        ),
    3 => Array
        (
            "category_id" => 2,
            "category_name" => 'Preschool2',
            "quiz_id" => 1,
            "quiz_name" => 'What is Forex?',
            "question_id" => 1,
            "question" => 'In forex, what are the three main types of analysis?',
        ),

    1 => Array
        (
            "category_id" => 1,
            "category_name" => 'Preschool',
            "quiz_id" => 1,
            "quiz_name" => 'What is Forex?',
            "question_id" => 2,
            "question" => 'Which type of analysis looks at historical price movements to determine the current trading conditions?',
        ),

    2 => Array
        (
            "category_id" => 1,
            "category_name" => 'Preschool',
            "quiz_id" => 2,
            "quiz_name" => 'Why Trade Forex?',
            "question_id" => '',
            "question" => ''
        )

);  

$modifiedArray = array();
    foreach($array as $key=>$value){
        if(!isset($modifiedArray[$value['category_id']])){
            $modifiedArray[$value['category_id']] = array("category_id"=>$value['category_id'],"category_name"=>$value['category_name']);
        }
        if(!isset($modifiedArray[$value['category_id']][$value['category_name']][$value['quiz_id']])){
            $modifiedArray[$value['category_id']][$value['category_name']][$value['quiz_id']] = array('quiz_id'=>$value['quiz_id'],'quiz_name'=>$value['quiz_name']);
        }
        if(!isset($modifiedArray[$value['category_id']][$value['category_name']][$value['quiz_id']][$value['quiz_name']][$value['question_id']])){
            $modifiedArray[$value['category_id']][$value['category_name']][$value['quiz_id']][$value['quiz_name']][$value['question_id']] = array('question_id'=>$value['question_id'],'question'=>$value['question']);
        }
    }
    print_r($modifiedArray);die;

OutPUT

Collectives™ on Stack Overflow

Manipulate ResultSet Array and make it Multidimensional

3 Answers 3

Comments

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related