Non-repeating items in select script?

Anon

Hello,
I'm attempting to format information from a database into forms that I can print into a directory.
I have the output looking like I want except for one item.
With my USA directory all states and cities and names, etc. are listed, but the State names repeat prior to each city name. I'd like the state name to be listed just once with the cities under it.
Is there an expression that I can use so this part of the output does not repeat itself?

I know this may be a simple issue, but I'm pretty new to php and mysql. Learning fast and furious, but this one has me stumped. Any help would be fantastic!! Thanks.

toma42

sounds like you need to modify your sql to do a group by then add sanity checks to not show the same state more than once.

Anon

Thanks Tom, I wasn't even aware of the "group by" option.
I've added GROUP BY so my sql now looks like:

$query =
"SELECT * FROM referral WHERE referral_country ='USA' GROUP BY referral_state ORDER BY referral_state, referral_city, referral_name_last ASC";

Now, rather than giving me all the listings in the states, I'm only getting one per state. Prior to adding GROUP BY, I got everything I needed, but the State name repeated.

Maybe I'm missing something now?
And, just to show my ignorance, what is a "sanity check"?

Anon

You are going to get the data in tabular form like this, there is nothing you can do about it:

etc.

What you have to do is treat the information more intelligently rather than just dumping each column's result into a separate table cell. For example:

echo '<table>';

$prev_state = '';
while ($data = mysql_fetch_object(...))
  {
  if ($data->referral_state != $prev_state)
    {
    echo '<tr><td colspan="2" align="center">' .
    $data-> referral_state . '</td></tr>';
    $prev_state = $data -> referral_state;
    }
  echo '<tr>';
  echo '<td>' . $data-> referral_city . '</td>';

  echo '<td>' . $data-> referral_name_last . '</td></tr>';
  }

echo '</table>';

"</td>"