Categorizing MySQL query

thisisafakeaccount12 · Jul 14, 2005

I have a page that categorizes information from a MySQL query

The ultimate result I'm trying to acheive is having a major category followed by a subcategory followed by the MySQL results, like this:

Environment (Major Category)
Agriculture (Sub category)
Results from query
Public Works (Major Category)
Transportation (sub Category)
Results from query

etc...

So far I have been able to acheive the sub categories based on the "category" column changing values like this

Code:

$query = 'SELECT * FROM `opportunities` ORDER BY `category` ASC, `dateadded` DESC;';
    $TempCategory="";
    $result = mysql_query($query);
    while($row = mysql_fetch_assoc($result)) {
        $program = $row['program'];
        $description = $row['description'];
        $funder = $row['funder'];
        $duedate = $row['duedate'];
        $nofa = $row['nofa'];
        $guide = $row['guide'];
        $application = $row['application'];
        $category = $row['category'];
        $categoryname = $row['categoryname'];
//Changes category on cat change
        if($category!=$TempCategory) {
            echo '<tr>
    <td colspan="4">$category</td>
<tr>';
            $TempCategory = $category;
        } 
//echo's sql results in a table
    echo "    
<tr>
    <td>$program</td>
    <td>$description</td>
    <td>$funder</td>
    <td>$duedate</td>
    <td>$nofa$guide$application</td>
</tr>\n";
    }

The major categories have been more of a problem because they appear spuradically. I was thinking something like echoing the major header when category = specified category, but am not sure how to go about it.

Any ideas would be greatly appreciated

sleipnir214 · Jul 14, 2005

Insufficient data for a meaningful answer.

How does your database designate a "major" category?
How does your database designate a "minor" category?

Typically, when dealing with hierarchical data, I use a "parent" column, as in the following data:

[tt]+-------+------------------+-----------+
| catid | catname | parentcat |
+-------+------------------+-----------+
| 1 | major category 1 | 0 |
| 2 | major category 2 | 0 |
| 3 | major category 3 | 0 |
| 4 | minor category 1 | 1 |
| 5 | minor category 2 | 1 |
| 6 | minor category 3 | 1 |
| 7 | minor category 4 | 2 |
| 8 | minor category 5 | 2 |
| 9 | minor category 6 | 3 |
+-------+------------------+-----------+[/tt]

All "major" cateogories have no parent category, so their "parentcat" columns are zero. The "parentcat" columns of minor categories are non-zero.

So to print out the heirarchy of my data, the easiest way is through recursion:

Code:

<?php
function fetch_children ($catid = 0, $indentlevel = 0)
{
	global $dbh; 
	
	$query = "SELECT * FROM foo WHERE parentcat = " . $catid;
	
	$rh = mysqli_query ($dbh, $query);

	while ($row = mysqli_fetch_assoc($rh))
	{
		print '<tr>';
		print str_repeat ('<td>&nbsp;', $indentlevel);
		print '<td>' . $row['catname'];
	
		fetch_children ($row['catid'], $indentlevel + 1);
	}
}

$dbh = mysqli_connect ('localhost', 'test', 'test', 'test');

print '<html><body><table>';
fetch_children();
print '</table></body></html>';
?>

Want the best answers? Ask the best questions!

TANSTAAFL!!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Categorizing MySQL query

thisisafakeaccount12

Technical User

sleipnir214

Programmer

Similar threads

Part and Inventory Search

Sponsor