CODEWITHSUNDEEP
phpmysqlmysqli
Mohamed Elkashef
Mohamed Elkashef

Reputation: 178

Fetch data from MySql from table as main category and the other as subcategory

Information:

I have two tables 1. grading_main 2. grading_sub

grading_main

+-------------+-------------------+
|   g_main_id |    g_main_name    |
+-------------+-------------------+
|           1 | Responsibilities  |
|           2 | Skills            |
|           3 | Efforts           |
+-------------+-------------------+

grading_sub

+-------------+---------------+--------------------+
|   g_sub_id  |   g_sub_name  |  main_element_id   |
+-------------+---------------+--------------------+
|           1 | Quality       | 1                  |
|           2 | Treatment     | 1                  |
|           3 | Equipment     | 2                  |
+-------------+---------------+--------------------+

I use this code to join them together

$sql = "SELECT * FROM grading_sub s JOIN grading_main m ON s.main_element_id = m.g_main_id";

what I want to do like this:

Fetch: g_main_name (the name of element) as header

and fetch the Sub elements as check boxes

sample: output

+-------------+----------------+-----------------------+
|   g_main_id |   g_main_name  |  g_sub_name           |
+-------------+----------------+-----------------------+
|           1 |Responsibilities| checkbox() Quality    |
|             |                | checkbox() Treatment  |
+-------------+---------------+------------------------+

+-------------+---------------+------------------------+
|   g_main_id |   g_main_name |  g_sub_name            |
+-------------+---------------+------------------------+
|           1 |   Skills      | checkbox() Equipment   |
|             |               |                        |
+-------------+---------------+------------------------+

I used this code:

$sql = "SELECT * FROM grading_sub s JOIN grading_main m ON s.main_element_id = m.g_main_id";
$run = mysqli_query($DBcon,$sql);
while($rows = mysqli_fetch_assoc($run)){
    echo '
        <div class="row">
             <div class="col-md-12 col-sm-12 col-xs-12 with-margin">
               '.$rows['g_main_name'].'
             </div>                                             
        </div>
                <div class="col-md-3 col-sm-4 col-xs-6 with-margin"> 
                    <input type="checkbox" class="flat" name="sub_elements[]" value="'.$rows['g_sub_id'].'"> '.$rows['g_sub_name'].'
                </div>
            ' ;

}

The result like this:

+-------------+----------------+-----------------------+
|   g_main_id |   g_main_name  |  g_sub_name           |
+-------------+----------------+-----------------------+
|           1 |Responsibilities| checkbox() Quality    |
|           1 |Responsibilities| checkbox() Treatment  |
+-------------+----------------+-----------------------+

So the Question is I want to fetch g_main_name of table grading_main as header and all g_sub_name of grading_sub table that they joined through main_element_id with g_main_id

Thanks,

Upvotes: 1

Views: 40

Answers (1)

Jeffrey
Jeffrey

Reputation: 1804

First and foremost: +1 for improving the question by adding tables and samples!

If I understand correctly, you want to show one header, followed by X checkboxes belonging to that header.

$sql = "SELECT * FROM grading_sub s JOIN grading_main m ON s.main_element_id = m.g_main_id";
$run = mysqli_query( $DBcon, $sql );

// First obtain all data and store it in an array.
while( $row = mysqli_fetch_assoc( $run ) ) {
    $data[ $row['g_main_name'] ][] = $row;
}


// Now use two loops to loop over the array.
// The first one loops over the headers
foreach( $data AS $g_main_name => $subItems ) {
    echo '
        <div class="row">
             <div class="col-md-12 col-sm-12 col-xs-12 with-margin">
               ' . $g_main_name . '
             </div>                                             
        </div>';
    foreach( $subItems AS $item ) {
    echo '
            <div class="col-md-3 col-sm-4 col-xs-6 with-margin"> 
                <input type="checkbox" class="flat" name="sub_elements[]" value="' . $item['g_sub_id'] . '"> '. $item['g_sub_name'] . '
            </div>';
}   }

note: untested code. I hope you get the idea.

Upvotes: 1

Related Questions