CODEWITHSUNDEEP
phpmysqljoin
Bundy
Bundy

Reputation: 311

Subcategories MySql and php

I'm having troubles with subcategories.

I want to display this on my page with 1 sql query:

Table schema:

Currently I'm using a query for the categories and in the PHP while loop another query for subcategories, but this gives me A LOT of queries on one page

Upvotes: 4

Views: 8694

Answers (6)

Ben Bohman
Ben Bohman

Reputation: 11

 <? include('qry/viewJobs_qry.php'); ?> 
 <table width="900" class="tableFontSize">
    <TR>
        <TD>

             </a>Category
        </TD>
    </TR>
  </table>
    <?php
    if(!empty($viewJobs))    {

    while($dataJobs=mysql_fetch_array($viewJobs)) {

     $category = $dataJobs[jobNumber];

     ?>
  <script language="javascript"> 
   function toggle<? echo $category; ?>() {
   var category = "<? echo $category; ?>";
   var ele = document.getElementById(category);
   var text = document.getElementById("displayText");
 if(ele.style.display == "block") {
        ele.style.display = "none";

}
else {
    ele.style.display = "block";

}
    } 
      </script>
    <table width="900" class="tableFontSize">
    <TR>
        <TD>

            <a id="displayText" href=javascript:toggle<? echo $category; ?>();>+ </a><? echo $category; ?>  
        </TD>
    </TR>
    <TR>
        <TD>
            <span id="<? echo $jobNumber; ?>" style="display: none;"><div id="width900"><? include('subCategoryArray.php'); ?> </div></span>
        </TD>
    </TR>
            </table>

            <? }} ?>

          </table>

Upvotes: 1

Residuum
Residuum

Reputation: 12064

Similar to Zed's answer, but selecting from table subcategory and then joining categories:

SELECT categories.name, subcategory.name
FROM subcategory
JOIN categories
  ON category.id = subcategory.category_id
ORDER BY category.id, subcategory.id

This will return pairs of values like (category 1, sub cat 1), (category 1, sub cat 2) ... For further columns add them to the statement and make aliases when appropriate like

SELECT categories.name AS maincat_name, 
    subcategory.name AS subcat_name, subcategory.id AS subcat_id

Upvotes: 0

bisko
bisko

Reputation: 4078

You should get all the categories and all the subcategories. Something like this:

$sql = '
    SELECT * FROM categories ORDER BY id ASC
';
$result = mysql_query($sql);

$categories = array();

while ($row = mysql_fetch_array($result)) {
    $categories[$row['id']] = $row;
}

// get the subcategories
$sql = '
    SELECT * FROM subcategories ORDER BY id ASC
';
$result = mysql_query($sql);

while ($row = mysql_fetch_array($result)) {
    $categories[$row['parent_id']]['subcategories'][] = $row;
}

print_r($categories);

This would put them in a nice array structure you can do whatever you want in PHP. And it's only two queries.

Upvotes: 1

Question Mark
Question Mark

Reputation: 3606

There is never any need for two tables for tree structures, a standard tree-ing table schema could be:

categories
id | catname | description | parent | lineage

Lineage would contain the path back to the root category e.g. '0,2,5,6' parent would contain 6

Then for your question you can SELECT all from that table ORDER BY LENGTH(lineage) ASC

after you have your resultset from the db you index them by their id

$categories = array();
while($row = mysql_fetch_assoc($result)){
   $categories[$row['id']] = $row;
}

now when you loop through them in your html you can check to see if the current category has any child categories.

Lineage is used to find all categories that are descendants of a certain category:

SELECT * FROM categories WHERE lineage REGEXP '[[:<:]]5[[:>:]]'

This'll get any categories with 5 in their lineage.

Upvotes: 1

Zed
Zed

Reputation: 57648

Without seeing the tables, you probably need something like:

SELECT category.name, subcategory.name
FROM categories
LEFT JOIN subcategory
  ON subcategory.category_id = category.id
ORDER BY category.name, subcategory.name

Upvotes: 0

Paul Dixon
Paul Dixon

Reputation: 300825

We'd need to see your table schema to offer a proper critique, but what you could do is join the subcategories to the categories in one query, sorted by category, subcategory. Whenever you see a new category, you know it's time to finish the current subcategory list and start a new category.

Upvotes: 0

Related Questions