[SOLVED] grouping Names under Locations?

[liquid79]

Hi Guys,

 

I’m having problems grouping data (names) under Places for an example:

 

In a table I have:

 

Name | Location

 

Bob  | London

John | Wales

Mark | London

Tom | Scotland

Fran | Wales

 

I’m trying to get it to display like:

 

 

--------------------------

 

London

Bob

Mark

 

Scotland

 

Tom

 

Wales

 

John

Fran

 

--------------------------------------

 

I can display it like:

 

London

 

Bob

 

London

 

Mark

 

But I can’t work out how to not have duplicating place names and just to group them under one place name rather than one for each? It be great if anyone can point me into the right direction, Thanks (btw i tryed the Group by but that only displayed half of the data)

 

My code:

 

$ordering = mysql_query("SELECT * FROM test ORDER BY location DESC ");

if (mysql_num_rows($ordering) > 0) {
while ($row = mysql_fetch_object($ordering)) {

echo '<table width="520" border="0" cellspacing="2" cellpadding="2">
  <tr>
    <td><strong>'.$row->location.'</strong></td>
  </tr>
  <tr>
    <td>'.$row->name.'</td>
  </tr>
</table>';
}
}

 

[p2grace]

Use a GROUP BY in the sql statement:

 

$ordering = mysql_query("SELECT * FROM `test` ORDER BY `location` DESC GROUP BY `location` ");

[chigley]

<?php

$query = mysql_query("SELECT location FROM test ORDER BY location DESC") or die(mysql_error());

while(list($loc) = mysql_fetch_row($query)) {
echo "<strong>{$loc}</strong><br /><br />\n\n";
$query2 = mysql_query("SELECT name FROM table WHERE location = '{$loc}'") or die(mysql_error());
while(list($name) = mysql_fetch_row($query2)) {
  echo "{$name}<br />\n";
}
echo "<br />\n";
}

?>

 

Try that

 

EDIT: Darn it, p2grace's looks much prettier!

[BenInBlack]

something like this

 

$ordering = mysql_query("SELECT * FROM test ORDER BY location DESC ");

if (mysql_num_rows($ordering) > 0) {

$last_location = '';
while ($row = mysql_fetch_object($ordering)) {

echo '<table width="520" border="0" cellspacing="2" cellpadding="2">';
if ($last_location != $row->location) {
echo '<tr>
    <td><strong>'.$row->location.'</strong></td>
  </tr>
';
$last_location = $row->location;
}
echo '  
  <tr>
    <td>'.$row->name.'</td>
  </tr>
</table>';
}
}

[liquid79]

wow thanks for the quick replys .. works great!