• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2560
  • Last Modified:

MYSQL extracting categories and subcategories

I have one table that contains various main categories and sub categories and  I'm having trouble extracting a list of the categories and subcategories that belong together.  The main categories and sub categories are all referenced by a main_num and sub_num.

MYSQL TABLE
user_id  main_cat  sub_cat  main_num  sub_num
1              CAT 1                         1            
2                           SUB 1                              1
3                           SUB 2                              1
4              CAT 2                         2              
5                           SUB 3                              2
6                           SUB 4                              2

I need a MySQL command that will select the Main Category and its Sub Categories, so I get the following output that I can parse with Perl or PHP:

CAT 1
   SUB 1
   SUB 2
CAT 2
  SUB 3
  SUB 4

I've tried grouping but I can't seem to figure it out.

Thanks!
$marc
0
marcparillo
Asked:
marcparillo
  • 3
  • 2
1 Solution
 
nizsmoDeveloperCommented:
for($i=0;$i<MAXCATNUM;$i++)
{
    $querySubCategory = "SELECT sub_cat FROM table_name WHERE sub_num=$i";
    $queryMainCategory = "SELECT main_cat FROM table_name WHERE main_num=$i";
    // then query your database using mysql_query() and retrieve results using mysql_fetch_row() or similar

    $resultSub = mysql_query($querySubCategory);
    $reslutMain = mysql_query($queryMainCategory);

    $rowMain = mysql_fetch_row($resultMain);
    echo $rowMain[0];

    echo "<blockquote>";

    while($rowSub = mysql_fetch_row($resultSub))
    {
        echo $rowSub[0];
    }

    echo "</blockquote>";
}

UIse the above for loop, combined with the queries and you should get your desired results.
Note you must connect to your database first.
0
 
marcparilloAuthor Commented:
Wow!  I didn't know you could perform two simultaneous queries with MySQL and PHP in a loop format.  I always relied on just one query.  Thanks for the help!  Since I posted that question, however, I came up with a workable solution using just one query:

$get_info = mysql_query("SELECT parent.user_id as Umain, child.user_id as Usub, parent.main_cat as main, child.sub_cat as sub from Table as parent, Table as child where parent.main_num = child.sub_num group by parent.main_cat, child.sub_cat");
$numRows = mysql_num_rows($get_info);

The query gives me one table containing all main categories matched with their sub categories.  Then I use a simple little PHP script to parse it all.  It's a little convoluted, I know, but hey, it works :)  ---

while($row = mysql_fetch_array ($get_info, MYSQL_ASSOC)) {
      
              $category = $row['main'];
             
              // IF THE CURENT CATEGORY AND PREVIOUS
              // CATEGORIES DON'T MATCH, THEN IT'S A NEW
              // CATEGORY
              if ($category != $oldCategory) {$newCategory = 1;}
             
              // IF IT'S THE END OF THE MYSQL LOOP
              if ($rowCount == $numRows) {$newCategory = 0;}

                                 // OTHERWISE, IT'S A SUBCATEGORY
                                // AND $newCategory = 2;
                           
              $subcategory = $row['sub'];
              $Umain = $row['Umain'];
              $Usub = $row['Usub'];
            
              // FOR CSS STYLES                          
if ($subCount % 2 == 0) {$css="class='r1'";}else{$css="class='r2'";}
             
              // IF IT'S A NEW CATEGORY
              // WRITE COLLECT THE $main AND $sub VARIABLES
              // AND RESET THEM
              if ($newCategory == 1) {
             
            $allRows .= "$main $sub <tr><td colspan='2'>&nbsp;</td></tr>";
                  $main ='';
                  $sub = '';
                  
                  // CREATE A NEW CATEGORY
                  // AND RESET newCategory = 2
                  // IN ORDER TO GRAB THE FIRST
                  // SUBCATEGORY                  
      $main = "  <tr >
      <td class='banner1' >$category</td>
      <td align='center' class='banner2'>
      <a href='page.php?id=$Umain&action=new&type=main'>Add New
      </td>
      </tr>";

                  $newCategory = 2;
                  
                  
             
              }
             
              // IF IT'S AN OLD CATEGORY
              // JUST WRITE THE $sub
              if ($newCategory == 2) {
                                
            $sub .= "<tr $css>
            <td width='400'>$subcategory</td>
            <td width='93' align='center'>
            <a href='page.php?id=$Usub&action=open'>Edit</a>&nbsp;
            <a href='page.php?id=$Usub&action=delete'>Delete</a>
            </td>
             </tr>";
                              
                    $subCount++;

             
              }

              // IF IT'S THE END OF THE LOOP
              // WRITE THE LAST $sub AND COLLECT
              // THE VARIABLES
              if ($newCategory == 0) {
                  
             $sub .= "<tr $css>
            <td width='400'>$subcategory</td>
            <td width='93' align='center'>
            <a href='page.php?id=$Usub&action=open'>Edit</a>&nbsp;
            <a href='page.php?id=$Usub&action=delete'>Delete</a>
            </td>
                 </tr>";


          $allRows .= "$main $sub <tr><td colspan='2'>&nbsp;</td></tr>";
             
              }
             
  $rowCount++;       
  $oldCategory = $row['main'];
  $newCategory = 2;
 
}
0
 
nizsmoDeveloperCommented:
Well done on writing that code. You always learn more when you write yourself ;-)
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
marcparilloAuthor Commented:

After trying out your code, and comparing it to mine, I've discovered yours has some major benefits over mine:  it's easlier to manage, it makes more sense at first glance, and it handles the tabular data better.  I was running into a problem where my code would not read the last Main Category if that Main Category had no subcategories.  Your code is more flexible in that sense -- so I'm using it.

Thank you so much for helping!!!
0
 
nizsmoDeveloperCommented:
No problem anytime! :)
0
 
fandingCommented:
Hi nizsmo,

I am trying to make a connection to my DB so I can pull the data using your solution but I can't seem to establish the connection properly.

Please help, am a php, mysql learner/newbie


<?php 
 
$connection = mysql_connect("localhost", "root", "khalilnjie") or die("Could not connect.");
 
for($i=0;$i<MAXCATNUM;$i++)
{
 
    $querySubCategory = "SELECT title FROM file WHERE title=$i";
    $queryMainCategory = "SELECT name FROM folder WHERE name=$i";
    // then query your database using mysql_query() and retrieve results using mysql_fetch_row() or similar
 
    $resultSub = mysql_query($querySubCategory);
    $reslutMain = mysql_query($queryMainCategory);
 
    $rowMain = mysql_fetch_row($resultMain);
    echo $rowMain[0];
 
    echo "<blockquote>";
 
    while($rowSub = mysql_fetch_row($resultSub))
    {
        echo $rowSub[0];
    }
 
    echo "</blockquote>";
}
 
?>

Open in new window

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now