Grouping Results Question

allgood2

I can\'t seem to figure this out. I have a query that groups data then displays by subcategory. I want the subcategory display like so...

subcategory
item one
item two
subcategory2
item one
etc.

What I get is just the first value under each subcategory or...

sucategory
item one
subcategory2
item one

Here\'s my code, what am I doing wrong....

$category=$category;
$results=mysql_query (\"SELECT * FROM library WHERE category=\\"$category\\" GROUP BY subcategory ORDER BY title\") or die(\"No return for $category\");
while ($row=mysql_fetch_array($results)){
$author = $row[\"author\"];
$title = $row[\"title\"];
$date = $row[\"date\"];
$category = $row[\"category\"];
$subcategory = $row[\"subcategory\"];
$url = $row[\"url\"];
$id = $row[\"id\"];

if($title==\"\")
{
	$display_doc .=\"
	<div class=\\\"xtrasmallVerticalSpacer\\\">&nbsp</div>
	\";
}

else
{
$display_doc .=\"
$subcategory
<a href=\\"../documents/library/$url\\">$title</a>
 $author
 <a href=\\"http://www.wfnet.org/documents/library/$url\\">http://www.wfnet.org/documents/library/$url</a>
\";
};
}
?>

<? echo\"$display_doc\n\"; ?>

Thanks
Alnisa

Anon

Thats a kinda sub-select which I don't think is supported by Mysql. If you havn't got too many rows just do 2 queries instead.

$sql=mysql_query("selecty * from blah blah");
while($row=mysql_fetch_array($sql)){

echo "category here";

$sql2=mysql_query("SELECT * FROM table where subcategory='$row[category]'");
while($row2=mysql_fetch_array($sql2)){
echo "subcategory here";
}

}

This isn't the most efficient way to do it but it's okay if you have a small db.

cheers

allgood2 wrote:

I can\'t seem to figure this out. I have a query that groups data then displays by subcategory. I want the subcategory display like so...

subcategory
item one
item two
subcategory2
item one
etc.

What I get is just the first value under each subcategory or...

sucategory
item one
subcategory2
item one

Here\'s my code, what am I doing wrong....

if($title==\"\")
{
	$display_doc .=\"
	<div class=\\\"xtrasmallVerticalSpacer\\\">&nbsp</div>
	\";
}

else
{
$display_doc .=\"
$....

Anon

have you tried this:

$results=mysql_query (\"SELECT * FROM library WHERE category=\\"$category\\" ORDER BY title, subcategory\") or die(\"No return for $category\");

powerlord

Maybe my understanding of GROUP BY is off, but isn't GROUP BY only used when you use sql functions such as SUM on a column?

Something like this might help:

$category=$category;
$results=mysql_query ("SELECT * FROM library WHERE category='$category' ORDER BY subcategory, title") or die("No return for $category");
while ($row=mysql_fetch_array($results)){
$author = $row["author"];
$title = $row["title"];
$date = $row["date"];
$category = $row["category"];
$subcategory = $row["subcategory"];
$url = $row["url"];
$id = $row["id"];

if ($subcategory != $lastcategory) {
$display_doc .="
$subcategory
";
$lastcategory = $subcategory;
}

if($title=="")
{
$display_doc .="
<div class=\"xtrasmallVerticalSpacer\"> </div>
";
}
else
{
$display_doc .="
<a href=\"../documents/library/$url\">$title</a>
 $author
 <a href=\"http://www.wfnet.org/documents/library/$url\">http://www.wfnet.org/documents/library/$url</a>
\";
};
}
?>

<? echo "$display_doc\n"; ?>

kparker

Exactly: GROUP BY is not needed (or useful) here.

allgood2

You rock!

That so works. Let me see if I understand what it's doing properly. Basically, you've re-worded my query so that it returns all results, un-grouped, sorted by the sub-category first, then the title (yeah).

Then you loop through the results checking to see if the subcategory matches. If so it displays the results. Then it goes through my little display format if elseif statement, because I'm a control freak, and like to remove empty variables, etc.

What I'm not certain I understand is why the variable $lastcategory is defined after it's called. Would the script still work if it was used like...

$lastcategory = $subcategory;
if ($subcategory != $lastcategory) {
$display_doc .="
$subcategory
";
}

or does the variable need to be part of the "if" statement. I can see what you did, and it's beautiful, I'm just not certain I totally understand it.

Thanks much

Alnisa

powerlord

The variable needs to be inside the if statement or after it. If you put it before it, then you the if will always end up being false and not execute the code inside it.

If you like having variables defined, put
$lastcategory = "";
at the top of the code (anywhere before the first while will work)

allgood2

Now I understand. Thanks Much.

Alnisa