[RESOLVED] Displaying several data from DB via a loop

onairric

Hi Folks,

How would I be able to echo Data from the database considering the situation:

There is a List of MAIN Categories in one DB table
There is another DB table that contains the SUB CATEGORIES

I want to be able to echo a div that displays each MAIN CATEGORY only once and display all the appropriate SUB CATEGORIES below it.... this must be done in a loop (while or for or whatever) because I have a lot of MAIN CATEGORIES. Below is a sample HTML of what I want to achieve:

<div id="1">
<p>ICE CREAM FLAVORS</p>
<p>
1. Chocolate
2. Strawberry
3. Vanilla
</p>
</div>

<div id="2">
<p>PIZZA FLAVORS</p>
<p>
1. All Veggies
2. Hawaiian
3. Meat Lovers
</p>
</div>

<div id="3">
<p>MAIN CATEGORY #3</p>
<p>
1. Sub Category #1
2. Sub Category #2
3. Sub Category #3
</p>
</div>

How do I do this without using IF and ELSE?

Below is a sample of the code I usually use to display multiple items using while()

$query = "SELECT * FROM main_category, sub_category ORDER BY main_category.category_name  ASC";
	$result = mysql_query ($query);
	while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) {

	echo '<div><a href="next.php">' . $row['category_name'] . '</a></div>';


} // Close While

By the way, I also want to follow the following order/sort:
1. DIVs for the MAIN CATEGORIES will be arranged by name of the MAIN CATEGORY
2. Inside each DIV, I want to sort the SUB CATEGORIES again by name

I think this is simple enough but I just dunno how to do this. Hope anybody there can help asap.

Thanks in Advance!!!

reddrum

I dont know how your tables structured but your SQL may go somthing like this:

SELECT Main.Food, Sub.FoodType, Sub.Flavors
FROM Main INNER JOIN Sub ON Main.Food = Sub.FoodType;

onairric

Hi,

Thanks for the reply. I think my problem isn't the way I queried... I think I really need to use for() within my while() loop. I just dunno exactly how to do it.

Help Please! =]

reddrum

Maybe try somthing like this, you could use for each loop but the while should work fine. That should only echo each category_name once.


$i=0; 
$result = mysql_query ($query); 
    while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) { 
        $name[$i] = $row['category_name']
        $flavor = $row['flavor']
        if ($name[$i] != ($name[$i] - 1)){
            echo '<div><a href="next.php">' .$name[$i] . '</a></div>';
            echo '<div>' .$flavor . '</div>';
        }
        else{       

            echo '<div>' .$flavor . '</div>';
        }
    ++i
}

rulian

it's all in how you organize the information that comes back
first, I have to assume your subcatagory is organized in some form of parent-child relationship, like
SUBCAT ID | PARENT ID | SUBCAT TITLE

if You do this:

$query=mysql_query(
"SELECT 
`main`.`cat_id`,`main`.`catagory_name`,`subs`.`subcat_id`, `subs`.`subcat_title`
FROM `main_category` AS `main`
LEFT JOIN  `sub_category` AS `subs` ON `subs`.`parent_id` = `main`.`cat_id`
ORDER BY `main`.category_name , `subs`.`subcat_title` ASC
"); 

if ($query)
{
	while($row=mysql_fetch_assoc($query))
	{
  	$Catagories[$row['catagory_name']][$row['subcat_id']=$row['subcat_title']
	}
}

This will create a $Catagories variable organized in a tree like formation of
Main Catagory - Subcat Id - Title

so when you :

while(list($MainCats) = each($Catagories)
{
$num=1; 
echo "<div id='".$MainCats."'>
<p>".$MainCats."</p>
<p>"; 


while(list($subcat) = each($Catagories[$MainCats]))
{
echo $num." ".$Catagories[$MainCats][$subcat]."<br>";
$num++; 
}
echo "
</p>
</div>
";
}

of course this is just an example, you can copy paste thsi but i'm sure you will have to fix some errros.

onairric

tnx for all the help! you guys are the best!

i actually solved the problem on my own. i kinda did the same thing the last few replies suggested. =p

you guys rock!