[SOLVED] Two 'GROUP BY' Needed

[EternalSorrow]

The setup:  I'm in a bit of a straight with a search engine I use for an awarded story archive.  The database has several instances of the same story by the same author because of several years they were given awards.  I also have the problem that different authors used the same name for their stories.  My search engine currently uses the 'GROUP BY title' command to organize.

 

The catch:  The 'GROUP BY title' works only if all authors have different titles for their stories, which is not the case.  I can't use 'GROUP BY author' because there are several instances of the same author but with different stories.  I know I can't use two GROUP BY commands in the same SELECT, but I need the search results to filter so that both title and author are taken into account when sorting duplicate and different stories.

 

The problem: I have no idea how to create and integrate a statement which would give the effect of a second GROUP BY into my already complex search engine.  Any help in the right direction is very welcome.

 

Below is the code for the results of the search engine.  If the form is needed, you just need to ask.

 

<?php

if(isset($_POST[search])) {

$title = strtolower(strip_tags(mysql_escape_string($_POST['title'])));
$author = strtolower(strip_tags(mysql_escape_string($_POST['author'])));
$category = strip_tags(mysql_escape_string($_POST['category']));
$group = strip_tags(mysql_escape_string($_POST['group']));
$year = strip_tags(mysql_escape_string($_POST['year']));

$termsArray = array();

if(!empty($author))
{
$termsArray[] = "author LIKE '%$author%'";
}

if(!empty($title))
{
$termsArray[] = "title LIKE '%$title%'";
}

if (count($termsArray) > 0){
   $terms = implode(" AND ", $termsArray);
   $terms = " WHERE ".$terms;
   unset($termsArray); //clear memory, cause we're finished using this.
}

$join = (empty($title) && empty($author)) ? "WHERE" : "AND";

$sql_category = ($category == all) ? "" : "$join category='$category'";

if ($sql_category != ""){
   $join = "AND";
}

$sql_group = ($group == all) ? "" : "$join `group`='$group'";

if ($sql_group != ""){
   $join = "AND";
}

$sql_year = ($year == all) ? "" : "$join year='$year'";

$qSearch = "SELECT *
FROM archives 
$terms $sql_category $sql_group $sql_year
GROUP BY title ORDER BY `title` asc";

$rsSearch = mysql_query($qSearch) or die(mysql_error());

if (mysql_num_rows($rsSearch) == 0)
{
echo '<p>Sorry, there was no results returned for your search. Please try again.</p>';
}
else
{
echo '<center><p><b>'.mysql_num_rows($rsSearch).'</b> story(s) found.</p></center>';

$i=2;
echo '<ol>';

while ($row = mysql_fetch_array($rsSearch))
{
extract($row);

$contents_here = '<li><a href="'.$url.'" target="_blank">'.$title.'</A> by <i><a href="author.php?author='.$author.'">'.$author.'</A></i></li>';

if ($i==0)
{
echo ''.$contents_here.'';
}

else if ($i==2)
{
echo ''.$contents_here.'';
}

else
{
echo ''.$contents_here.'';
}

$i++; $i=$i%2;

}
echo '</ol>'; 

}

}
?>

[Philip]

oops - ignore me, misread post.

 

sorries. 

[PFMaBiSmAd]

Well yes you can use two GROUP BY terms (works very similar to two ORDER BY terms). I cannot tell what you are trying to accomplish (an example of your data and what the results should be would help) but one of the following will probably do what you need -

 

GROUP BY title, author

 

GROUP BY author, title

[EternalSorrow]

You've helped me a bunch with the realization that there can be two GROUP BYs in the same SELECT (I had read it was impossible, and foolishly believed it).  Thank you very much for the quick reply and solution to my problem.