CODEWITHSUNDEEP
phpmysqlsqlmysql-error-1062
Asparagirl
Asparagirl

Reputation: 256

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

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:

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:

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!

Upvotes: 1

Views: 241

Answers (3)

gerard
gerard

Reputation: 

$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 
*/

Upvotes: 0

Chris Gutierrez
Chris Gutierrez

Reputation: 4755

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 '-')

Upvotes: 1

cletus
cletus

Reputation: 625485

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
}

Upvotes: 2

Related Questions