i'll be shocked if this can be done

Mega

ive been trying for ages to do this query, but i cant figure out how

i have 3 tables.

one is a main category table:

cats:

cid | name

1 Main

pages:

pid | name | cid

1 alink 1
2 anotherlink 1

subcats:

scid | name |cid

1 asubcat 1
2 anothersubcat 1

i need to query the database and have them listed like:

Main
asubcat
anothersubcat
alink
anotherlink

can anyone do this?

mrmufin

I haven't built the tables, but is it safe to assume that:

"SELECT
cats.name,
subcats.name,
pages.name
FROM cats, subcats, pages
WHERE cats.cid=1
AND subcats.cid=cats.cid
AND pages.cid=cats.cid
ORDER BY cats.cid, subcats.scid, pages.pid"

isn't doing the job?

wiley

Here's a shot in the dark, maybe intensive on the db hits, but I have used similar loops that seem to work so far --

$cat_query = mysql_query("SELECT FROM cats");
WHILE ($cat_results = mysql_fetch_array($cat_query)) {
$x = 0;
$subcat_query = mysql_query("SELECT FROM subcats WHERE cid ='$cat_results[cid]');
WHILE ($subcat_results = mysql_fetch_array($subcat_query)) {
IF ($x > 1) echo "$cat_results[name] \n"; $x++;
echo "$subcat_results[name] \n";
}
$page_query = mysql_query("SELECT * FROM pages WHERE cid ='$cat_results[cid]');
WHILE ($page_results = mysql_fetch_array($page_query)) {
echo "$page_results[name] \n";
}
}

Cheers,
Dan

Mega

Originally posted by wiley
Here's a shot in the dark, maybe intensive on the db hits, but I have used similar loops that seem to work so far --

$cat_query = mysql_query("SELECT FROM cats");
WHILE ($cat_results = mysql_fetch_array($cat_query)) {
$x = 0;
$subcat_query = mysql_query("SELECT FROM subcats WHERE cid ='$cat_results[cid]');
WHILE ($subcat_results = mysql_fetch_array($subcat_query)) {
IF ($x > 1) echo "$cat_results[name] \n"; $x++;
echo "$subcat_results[name] \n";
}
$page_query = mysql_query("SELECT * FROM pages WHERE cid ='$cat_results[cid]');
WHILE ($page_results = mysql_fetch_array($page_query)) {
echo "$page_results[name] \n";
}
}

Cheers,
Dan

i've got it down to less queries than that, im trying to do it within 2, i doubt it can be done in one.

i think ivr found a way to do it by comparing the first value of the array, but im not sure 🙁

Mega

Originally posted by mrmufin
I haven't built the tables, but is it safe to assume that:

"SELECT
cats.name,
subcats.name,
pages.name
FROM cats, subcats, pages
WHERE cats.cid=1
AND subcats.cid=cats.cid
AND pages.cid=cats.cid
ORDER BY cats.cid, subcats.scid, pages.pid"

isn't doing the job?

nope, becoz that wud put the sub categories in the same line as the page 😉

i thought that at first too. i might upgrade to mysql 4 and do it in 1 🙂 i wudnt be able to find a host then tho 🙁