PHP/MySQL data extraction problems

[bubbapuck]

I am a newbie and ave been working on a problem for several hours now and am frustrated! I have 2 mysql tables we'll call main_category and sub_category.The sub_category table has a field that maps to the main_category primary key giving a 1 to many relationship between the 2 tables.

I am trying to read both tables, than echo the main category in h2 tags, then below it list out all of the sub categories. I am trying to write the best optimized code so I am not sure if I should read the first table, write the main category then read the second table, if some sort of join will work better, or anything really. I cant get it to work any way and am looking for help.

Thanks... Bubba

[travelbuff]

You definitely want to use a join. Try something like this:
"SELECT * FROM main_cat, sub_category WHERE sub_category.main_category_id = main_category.category_id ORDER BY main_category.description"

Of course, you will need to adjust table and field names appropriately.

What that should do is return all of your rows in both main and sub where the main ID's match, and group them by main ID.

Remember: table.column

[devinemke]

just guessing here at your actual data structure:

$result = mysql_query('SELECT * FROM main_category');
while ($row = mysql_fetch_assoc($result)) {$main_categories[$row['main_cat_id']] = $row;}

$result = mysql_query('SELECT * FROM sub_category');
while ($row = mysql_fetch_assoc($result)) {$sub_categories[$row['sub_cat_id']] = $row;}

foreach ($sub_categories as $key => $value)
{
	if (isset($main_categories[$value['main_cat_id']]))
	{
		$main_categories[$value['main_cat_id']]['sub_categories'] = $value;
	}
}

foreach ($main_categories as $key => $value)
{
	echo '<b>' . $value['name'] . '</b><br>';
	if (isset($value['sub_categories']))
	{
		foreach ($value['sub_categories'] as $sub_category)
		{
			echo '-' . $sub_category['name'] . '<br>';
		}
	}	
}

[bubbapuck]

Thank you both for your help. I will take your advise. IF anyone else has any opinion on this, please post. I want to learn the best way to do this.

Thanks... Bubba

[bubbapuck]

Thanks for all your help everyone! This is what I have come up with but it still is not working correctly:

$result = mysql_query("SELECT mcat .mcat_name AS Main,
scat .scat_name AS Sub
FROM mcat INNER JOIN scat
ON mcat.mcatID = scat .maincat");

if (!$result) {
echo "Error in the database!!!";
exit;
}

$previous_main = "";
while ($record = mysql_fetch_assoc($result)) {
if (!$record["Main"] == $previous_main) {
echo "<h2>".$record["Main"]."</h2>";
}
echo " <font color='red'>".$record["Sub"]."</font> |";
$previous_main = $record["Main"];
}

For some reason, when the main category changes, the if statement isnt detecting it so it doesnt write the next categories <h2> statement. If this a valie if statement?

if (!$record["Main"] == $previous_main)