Complicated MySQL statement and show

[jazztie]

well, I have a new problem...
I have two tables. 'Category' (items are 'ID' and 'title') and 'Subcategory' (items are 'ID', 'Cat_ID' and 'title')
... I want to make a 'sitemap' (kind of) of the categories and the subcategories... which looks something like this:
----
Category 1 (title)
..Subcat 1.1 (title)
..Subcat 1.2 (title)
..Subcat 1.3 (title)

Category 2 (title)
..Subcat 2.1 (title)
..Subcat 2.2 (title)
..Subcat 2.3 (title)
et cetera....
----

I already have a statement for the retrieval of the subcaterogies:

----

<?
$sql = "SELECT * FROM subcats order by cat";
$result = mysql_query($sql);

while ( $row = mysql_fetch_array($result)) {
echo '<tr bgcolor="#FFFFCC">' . '
<td width=60 align="center">' . $row["id"] . '</TD>' .
'<td width=60 align="center">' . $row["cat_ID"] . '</td>' .
'<td valign="top">' . $row["title"] . ' ' . '</td>' .
'</tr>';
}
exit;
?>
---

This shows a table with a column of subcat ID's, a column of cat_ID's and a column of subcat titles.

Now the true problem lies in the combining of the two tables. Now I could probably link the cat_ID from the Subcategory table with the Category table. But how do I order the list in the way I showed at the beginning of this message.

Could someone help me with the code to solve this problem?

[BlackCatt]

Okay this might work. I don't know if MySQL supports the UNION opperator or if it will allow you to alias a UNION like this but give it a shot.

For your SELECT statement try:

SELECT a.*
FROM (SELECT null as sub_cat_id, cat_id, title FROM category UNION SELECT sub_cat_id, cat_id, title FROM subcategory) a
ORDER BY cat_id, sub_cat_id

This should return results similar to:

SubCatID CatID Title
NULL 1 Test1
1 1 Sub1.1
2 1 Sub1.2
3 1 Sub1.3
NULL 2 Test2
1 2 Sub2.1
2 2 Sub2.2
3 2 Sub2.3
4 2 Sub2.4

Where SubCatID is null is a "Category", when it has a value it is a "Sub Category".

Hope this helps.

BlackCatt

[jazztie]

Unfortunately, that doesn't work... I get the following error-message:

Warning: Supplied argument is not a valid MySQL result resource in C:\Inetpub\wwwroot\tiemersma\showsubcats1.php on line 15

---
<?php
include("db_password_ICLON.inc");
mysql_pconnect("$hostname","$user","$dbpassword");

mysql_select_db("$user")
or die ("unable to select database");

$sql = "SELECT a.*
FROM (SELECT null as subcats.ID, cats.ID, cats.title FROM cats, subcats
UNION SELECT subcats.ID, subcats.cat, subcats.title FROM subcats)a
ORDER BY cats.ID, subcats.ID ";

$result = mysql_query($sql);

while ( $row = mysql_fetch_array($result)) {
echo '<tr bgcolor="#FFFFCC"><td width=60 align="center">' . $row["subcats.ID"] . '</TD>' . '<td width=60 align="center">' . $row["subcats.cat_ID"] . '</td>' . '<td valign="top">' . $row["subcats.title"] . ' ' . '</td>' .
'</tr>';
}
exit;

?>
----
Line 15 is the while-statement

[freddydoesphp]

Okay so I went about it a bit different, I opted to use two queies for this one. Here is my code, it basically uses arrays to store the categories and their subcategories, then it loops though the final array to create the effect you want.

//Grab all the main cats and store them in an array
$result = mysql_query("select * from cats order by title");
while($row = mysql_fetch_array($result)) {
    extract($row);
    $cats[$id] = array("title" => $title,
                       "subcats" => array()
                       );
    }

//Get all the subcats and put them into the array at the corrsponding main cat    
$result = mysql_query("select * from subcats order by title");
while($row = mysql_fetch_array($result)) {
    extract($row);
    $cats[$catID]["subcats"][$id] = $title;
    }    
    
    
foreach($cats as $key => $val) {
    print "Category $key ".$cats[$key]["title"]."<br>";
    $i = 1;
    foreach($cats[$key]["subcats"] as $jey => $jal) {
        print "&nbsp;&nbsp;&nbsp;Subcat $key.$i $jal<br>";
        $i++;
        }
    unset($i);    
    print "<br><br>";
    } 

[BlackCatt]

jazztie,

I see something wrong with your SELECT statement. I don't know if that is the problem but you may want to try to fix it and try again.

Your code reads:
$sql = "SELECT a.*
FROM (SELECT null as subcats.ID, cats.ID, cats.title FROM cats, subcats
UNION SELECT subcats.ID, subcats.cat, subcats.title FROM subcats)a
ORDER BY cats.ID, subcats.ID ";

(the bold is what I changed)

Try this:
$sql = "SELECT a.*
FROM (SELECT null as ID, cats.ID as cat, cats.title FROM cats
UNION SELECT subcats.ID, subcats.cat, subcats.title FROM subcats)a
ORDER BY cats.ID, subcats.ID ";

I think that might work a little better.
<fingers crossed>

BlackCatt

[freakysid]

BlackCatt, I think you may be confusing mySQL for a *real* RDMS AFAIK, mySQL does not yet support UNION nor sub-queries (nested queries).

[BlackCatt]

FreakySid,

You are correct. Those were TWO MAJOR assumptions I was making. Well I guess that shows my experience level with MySQL. Thanks for pointing these things out. Now I don't feel so silly.

Well Jazztie, I guess its back to the drawing board.

BlackCatt

[freddydoesphp]

Back to what drawing board?, your solution is sitting right above this post in my reply, its works perfect I just tested it.

[BlackCatt]

Freddy,

I meant back to the drawing board for a pure query solution. You solution should work fine. I was just trying to do it all in the database. I say if it works use it, so in this case use your solution.

BlackCatt

[jazztie]

Thanx guys, both of you... I'll try it immediately!

[jazztie]

I seem to have a problem:

Using the code from freddy I get the following result: (slighty moderated, the original code had a different problem)

//Grab all the main cats and store them in an array
$result = mysql_query("select * from cats order by id");
while($row = mysql_fetch_array($result)) {
    extract($row);
    $cats[$id] = array("titel" => $titel,
                       "subcats" => array()
                       );
    }

//Get all the subcats and put them into the array at the corresponding main cat    
$result = mysql_query("select * from subcats order by cat");
while($row = mysql_fetch_array($result)) {
    extract($row);
    $cats[$cat.ID]["subcats"][$id] = $titel;
    }    
    
    
foreach($cats as $key => $val) {
    print "Cat $key ".$cats[$key]["titel"]."<br>";
    $i = 1;
    foreach($cats[$key]["subcats"] as $jey => $jal) {
        print "&nbsp;&nbsp;&nbsp;Subcat $key.$i $jal<br>";
        $i++;
        }
    unset($i);    
    print "<br><br>";
    } 

----------
Category 1 University

Category 2 Education development

Category 3 Professionalising Education

Category 4 Organisation

Category 1ID
...Subcat 1ID.1 university of leiden
...Subcat 1ID.2 blablablablabla

Category 2ID
...Subcat 2ID.1 education blablabla
...Subcat 2ID.2 education blabladidid
...Subcat 2ID.3 education blakjlsdl

Category 3ID

et cetera, et cetera.
-----------
(subcats without the dots at the beginning)

The tables I have are:
cats
- id
- titel

subcats
- id
- cat
- titel

Somehow it does find the names (titel) for the categories, prints them and then it uses the id's for the categories and prints the subcats... we're almost there.

PS: this is part of a final year project I have to do for my study... (internship at the University)

[freakysid]

This thread has been continued here:
http://www.sitepointforums.com/showt...threadid=22297

jazztie, there was no need to start a new thread. It only leads to confusion and frustration. You can always edit your posts if you want to go back and explain things clearer.

[firepages]

<?
mysql_select_db($database);

$SQL="select subcats.*,cats.titel AS yaks from subcats  LEFT JOIN cats ON subcats.cats=cats.id ORDER BY yaks,subcats.id";
$int=mysql_query($SQL);//echo mysql_num_rows($int);


while($yak=mysql_fetch_array($int)){	$temp=$yak[yaks];
	if($temp!=$oldtemp){echo "<b>$yak[yaks]<br></b>";}
    $oldtemp=$temp;
	echo " $yak[id]: $yak[titel]<br>";
	}
?>

Not sure how efficient a query like this is , 2 normal ones may be faster ? & to be honest I would have gone down the freddydoesphp route myself , but the above should work ie

CATNAME
subcatid:subcat_title
subcatid:subcat_title
subcatid:subcat_title
CATNAME
etc etc

ANd while I am here... how do I get the syntax coloured code happening?

[jazztie]

Dear firepages and freakysid... thanks a lot for your help. It finally works!!!

Don't know what I should do without the help of PHP gurus like yourself...

I really appreciate it!

[BlackCatt]

Firepages,

While that query will work and is fairly efficient, it doesn't return the exact result set that he is looking for. I did think of a very similar query earlier but opted for the query in my original reply above. While this SELECT statement doesn't return the result set he was originally looking for, the information he is looking for could very easily be extracted from it.

The query you suggested actually returns the result set in the format of:

subcatid, catid, title, cattitle
1, 1, Sub1.1, Category 1
2, 1, Sub1.2, Category 1
1, 2, Sub2.1, Category 2
2, 2, Sub2.2, Category 2
3, 2, Sub2.3, Category 2

Something else that may be a problem is that if any Category doesn't have any sub Categories then it won't appear in the result set. To get it to return you would need to modify the query you provided just slightly. Instead of "Left Join" you would actually want "Right Outer Join". With this change if there is a Category that has no sub categories then it would display with Null's like this...

subcatid, catid, title, cattitle
1, 1, Sub1.1, Category 1
2, 1, Sub1.2, Category 1
1, 2, Sub2.1, Category 2
2, 2, Sub2.2, Category 2
3, 2, Sub2.3, Category 2
Null, Null, Null, Category 3

Hope this all makes sense to everyone.

BlackCatt

[firepages]

On your second point, you are right, in that if there are no sub-categories the main category will not get listed, how important this is depends on the script, in saying that ideally you would return the empty categories instead, RIGHT OUTER JOIN sounds a bit painful to me - i.e never done one yet

As for the result set, well yes and no, I am not sure how (or if) with MySQL you could return the exact result set required.

So the for loop formats the result to print as requested by checking if the category name has changed or not, this can be easily changed if necc'y , I would also normally use mysql_fetch_row but thought fetch_array would be easier (on the eye) to play with.

In any case you would have to manipulate the result set to display as needed, and this is why I prefer the 2 queries freddy suggested, which the more I think of it is probably faster than the JOIN and easier to manipulate.

[freddydoesphp]

jazztie, my code worked perfectly for me and gave me the rsults you were looking for, I know you already have it fixed in another fashion but your culprit was on this line

    $cats[$cat.ID]["subcats"][$id] = $titel; 

and it should read

    $cats[$catID]["subcats"][$id] = $titel; 

There are a few other things, that I imagine happened when porting my code to fit your db setup and such. But none the less my methodd does work. To prove it, I'll show you, here is the working code and the source code

http://www.irq11.com/freddy/d.php
http://www.irq11.com/freddy/d.phps

[jazztie]

Thanks Freddy,

It does work... even better than the code the others provided. (This overview has <br> between the categories)

Thanks a lot... I really appreciate it!