Problems using GROUP BY in MySQL in a query that does a JOIN on two tables

Question

I have two MySQL tables, $database1 and $database2. Both have a field in them called ID. I am passing the name of a town to the file using GET (i.e. it's in the URL of the PHP file that holds this code).

I can run this query...

$PlaceName = $_GET['townName'];
$PlaceName = mysql_real_escape_string($PlaceName);

$sql="SELECT * from $database1 LEFT JOIN $database2 on $database1.ID = $database2.ID WHERE PlaceName='$PlaceName'";
$query = mysql_query($sql);

echo '<h1>People who are searching for '.$PlaceName.':</h1>';
echo '<ul>';
while ($row = mysql_fetch_array($query)) { 
   echo "<li>ID #",$row['ID'],": ",$row['MemberPersonalName']," ",$row['MemberSurname']," -- searching for ",$row['SurnameBeingSearched'],"</li>";
   }
echo '</ul>';

...and it works and all is well. Right now the output looks like this...

People who are searching for Hogwarts:

• ID #137: Hermione Granger -- searching for Stern
• ID #137: Hermione Granger -- searching for Engelberg
• ID #503: Harry Potter -- searching for Kreindler
• ID #549: Ron Weasley -- searching for Kreindler
• ID #1062: Draco Malfoy -- searching for Engelberg
• ID #1155: Ginny Weasley -- searching for Kreindler
• ID #1155: Ginny Weasley -- searching for Streisand

But the output needs tweaking, and I'm having trouble writing my SQL query statement to reflect the changes. What I really want is for the output to look like this...

People who are searching for Hogwarts:

• Engelberg is being searched by Hermione Granger (id #137) and Draco Malfoy (id #1062)
• Kreindler is being searched by Harry Potter (id #503), Ron Weasley (id #549), and Ginny Weasley (id #1155)
• Stern is being searched by Hermione Granger (id #137)
• Streisand is being searched by Ginny Weasley (id #1155)

In other words, I need to group the output together by the field 'SurnameBeingSearched', I need to list the names of the people doing the searching in an "X, Y, and Z" output format (where it knows where to add a comma, if necessary, depending on the number of results), and I need to order the results by the 'SurnameBeingSearched' field.

Help? Thanks!

Answer 1

You need to list the names so this isn't an aggregation (in the SQL sense) problem. Keep your current query. You're going to have to do the grouping in code.

So something like:

$rows = array();
$last = '';
while ($row = mysql_fetch_array($query)) {
  $surname = $row['SurnameBeingSearched'];
  $id = $row['ID'];
  $name = $row['MemberPersonalName'];
  if ($last != $surname) {
    $last = $surname;
    $rows[] = array();
  }
  $rows[count($rows)-1][$id] = $name;
}
foreach ($rows as $row) {
  // now display each group of names
}

Answer 2

You might also be able to use the MySQL GROUP_CONCAT() function.

It would look something like this...

SELECT places_tbl.name, GROUP_CONCAT(people_tbl.name) 
FROM places_tbl 
LEFT JOIN people_tbl ON (places_tbl.id = people_tbl.id) 
GROUP BY places_tbl.id

GROUP_CONCAT() by default returns the values as comma delimited. You can probably split them up to get the formatting as you need it or use the SEPARATOR keyword. GROUP_CONCAT(fieldname SEPARATOR '-')

Answer 3

$PlaceName = $_GET['townName']; $PlaceName = mysql_real_escape_string($PlaceName);

// note - added order to the query
$sql="SELECT * from $database1 LEFT JOIN $database2 on $database1.ID = $database2.ID WHERE PlaceName='$PlaceName' 
      ORDER BY SurnameBeingSearched, MemberSurname, MemberPersonalName";
$query = mysql_query($sql);

echo '<h1>People who are searching for '.$PlaceName.':</h1>';
echo '<ul>';
  $cntr = mysql_num_rows($query);
  if ($cntr > 0) {
    $i = 0;
    $srchd = mysql_result($query, $i, 'SurnameBeingSearched');     
    $mbr = mysql_result($query, $i, 'MemberPersonalName');
    $mbr = $mbr . " " . mysql_result($query, $i, 'MemberSurname');     
    $mbr = $mbr . " (id #" . mysql_result($query, $i, 'ID') . ")";      
    $lin = $srchd . " is being searched by " . $mbr;
    $prev = $srchd;
    if ($cntr == 1) { 
      echo "<li>" . $lin . "</li>";            
    } else {
      for ($i = 1; $i< $cntr; $i++) {        
        $srchd = mysql_result($query, $i, 'SurnameBeingSearched');     
        $mbr = mysql_result($query, $i, 'MemberPersonalName');
        $mbr = $mbr . " " . mysql_result($query, $i, 'MemberSurname');     
        $mbr = $mbr . " (id #" . mysql_result($query, $i, 'ID') . ")";      
        if ($srchd == $prev) { // common search
          $j = $i + 1;  
          if ($j < $cntr) { // still have data
            $nxt = mysql_result($query, $j, 'SurnameBeingSearched');     
            if ($prev == $nxt) {  // another one coming -- use the comma
              $lin = $lin . ", " . $mbr;  
            } else {
              $lin = $lin . ", and " . $mbr;  // last member add the 'and' - line is done
              echo "<li>" . $lin . "</li>";
            }           
            $prev = $srchd; 
          } else { // ran out of data - need to finish the line
            $lin = $lin . ", and " . $mbr;  // last member add the 'and' - line is done
            echo "<li>" . $lin . "</li>";
        } else { // new search - need to print this line and start a new one
          echo "<li>" . $lin . "</li>";            
          $lin = $srchd . " is being searched by " . $mbr;
          $prev = $srchd;
        }  // test searched = previous
      }  // next i
    }  // only one row
  }  // cntr > 0 
echo '</ul>';

/* note: this is not tested 
   I would recommend using table1 and table2 instead of database1 and database2
   or better give the tables meaningful names
   I would use active voice instead of passive voice 
*/