Is this even possable?

[figment]

I dont even know if this is possable but here goes:

I have two tables for a project im working on...

Category
SubCategory

SubCategory is 'joined' to Category by the primary key of Category (Category_ID).

Here is what i want to do, I want to select ALL Categories.. AND ALL SubCategories, but grouped first by Category, and then by SubCategory.

Heres the catch... not all Categories have a SubCategory.

Here is the eventual output I want to generate:

Category 1
Category 2
Category 3
-- Subcategory 2
-- SubCategory 4
Category 4
-- SubCategory 1
Category 5
-- SubCategory 6
-- SubCategory 3
-- SubCategory 5

Here is how i would do it in PHP:

$rCategory = mysql_query("SELECT * FROM Category ORDER BY Category_Name");
while ($aCategory = mysql_fetch_array($rCategory)) {
    print $aCategory['Category_Name'],"<br />\n";
    $rSubCategory = mysql_query("SELECT * FROM SubCategory WHERE Category_ID='".$aCategory['Category_ID']."' ORDER BY SubCategory_Name");
    while ($aSubCategory = mysql_fetch_array($rSubCategory)) {
        print "-- ".$aSubCategory['SubCategoryName']."<br />\n";
    }
}

The goal is to optimize this into ONE mysql_query and work out the same result in PHP....

Any chance this is possable?

:Figment

[mykg4orce]

why didn't you? or couldn't you test it?

[MrAlaska]

I'd say it was pretty possible.

select c.*, s.*
from category c left join subcategory s
on c.category_id=s.category_id

Did you need help with the PHP also? This result set should be a bit easier to code than the one you already worked out.

[figment]

Sure if you want 🙂

I am imagining sme kind of loop that checks for differences in the Category name as the primary logic? yes?

thank you for the SQL by the way, it returned a bit different result set tham im used to working with, im kinda new to the more 'advanced' SQL queries...

[MrAlaska]

I suppose you could... OR, you could just set a flag.
I would optimize that sql statement too.
Select * just looks so tacky if don't need it.

$sql="select c.Category_ID, ".
	" c.Category_Name as name, ".
	" s.SubCategory_Name as sname ".
" from Category c left join SubCategory s ".
" on c.Category_ID=s.Category_ID";

$result=mysql_query($sql)
	or die ("Sql prob:".$sql."<p>".
	mysql_errno().": ".mysql_error());

$i=-1;
while ($row=mysql_fetch_array($result, MYSQL_ASSOC)){
	if ($row['Category_ID']<>$i){
		echo $row['name']."<br>";
		$i=$row['Category_ID'];
	}
	if ($row['sname']){
		echo "--".$row['sname']."<br>";
	}
}

[figment]

In the end this is going to be used for a quote-of-the-day management system.

I have noticed something odd... It seems like only the Categories with SubCategories return anything more than just the category name.... Is there a way to get all the Category information when there are no SubCats?
Here is the code im using..

    $sql = "SELECT_c.*,_s.* FROM_QOT_category_c_LEFT JOIN_QOT_subcategory_s ON c.qotc_id=s.qotc_id ORDER BY qotc_name, qots_name";
    $rCats = mysql_query($sql) or die (mysql_errno().": ".mysql_error());
    print "<table border>\n";
    print "<tr>\n";
    print "\t<td>".qotc_id."</td>\n";
    print "\t<td>".qotc_name."</td>\n";
    print "\t<td>".qotc_desc."</td>\n";
    print "\t<td>".qots_id."</td>\n";
    print "\t<td>".qots_name."</td>\n";
    print "\t<td>".qots_desc."</td>\n";
    print "</tr>\n";
    while ($aCats = mysql_fetch_array($rCats, MYSQL_ASSOC)) {
        print "<tr>\n";
        print "\t<td>".$aCats['qotc_id']."&nbsp;</td>\n";
        print "\t<td>".$aCats['qotc_name']."&nbsp;</td>\n";
        print "\t<td>".$aCats['qotc_desc']."&nbsp;</td>\n";
        print "\t<td>".$aCats['qots_id']."&nbsp;</td>\n";
        print "\t<td>".$aCats['qots_name']."&nbsp;</td>\n";
        print "\t<td>".$aCats['qots_desc']."&nbsp;</td>\n";
        print "</tr>\n";
    }
    print "</table>";

[MrAlaska]

Are you saying that qotc_name, qotc_id, and qotc_desc all return
values if QOT_Category_c has a subcategory, but if no
subcategories are present then only qotc_name is returned?

There is obviously something I am failing to understand, either in
the question or the behavior.

[figment]

"Are you saying that qotc_name, qotc_id, and qotc_desc all return
values if QOT_Category_c has a subcategory, but if no
subcategories are present then only qotc_name is returned?"

Yeah, thats exactly what is going on..
I wasnt able to print the qotc_id on Categories with no subcategories, so wrote a while loop printing out all the output from the query, and lo-and-behold, it seems as though no information other than qotc_name (Category name) is being returned by that query for categories with no subcategories...

I am wondering why though, if it was going to be selective about what it returns, why just not return anything? Why just the name?

(still looking for a modified version of your query to return more Category Info, b.t.w.)

[MrAlaska]

The only thing I can think of is something funky in your table
structure. Try addressing the columns explicitly, something like this:

$sql = "
SELECT c.qotc_id, c.qotc_name, c.qotc_desc, 
	s.qots_id, s.qots_name, s.qots_des 
FROM QOT_category c 
LEFT JOIN QOT_subcategory s 
ON c.qotc_id=s.qotc_id 
ORDER BY c.qotc_name, s.qots_name
";

It's worth a try, I guess, just to see what happens. Maybe
somebody will drop by with a clearer head and offer a theory.
What is the purpose of replacing spaces with underscores? I see
a few people do that, never figured out why. It looks like trouble
brewing to me.

Edit: Oops, I took out too many of the underscores l
All better now... I think...

[figment]

I've never used anything differemt.. I saw it used a few times, and thought it was a goot idea.. No real reason for it, AFAIK

I didnt even know that you could use spaces in field names... LOL

[MrAlaska]

NO! Spaces in the field names are a BAD IDEA lol

I don't know if you can get away with that or not, but underscores work there.

I was talking about the spaces in the SQL Query, replacing THOSE with underscores looks funky to me. I have never heard a reason for that practice.

[figment]

OH!!! LOL, now i see, i think those are typos...