displaying group headings for MySQL query

I have a page that lists theme parks. When sorted by 'park name' the first letter is displayed followed by parks beginning with that letter. like this:

-A-
a park 1
a park 2

-B-
b park 1
b park 2 etc

I also have a query to sort by 'State' but instead of displaying the first of each new letter I'd like the php code to display the name of the state as the heading then list theparks in that state. Like this:

    -Florida-
    park 1
    park 2

    -Ohio-
    park 1 
    park 2 
-New York-
park 1

-North Carolina-
park 1
park 2
park 3 etc

The code used to display the first letters was pieced together from other questions posted here. I am a novice with php and I'm hoping someone could help adapt the code to show the full state names.

This is the MySQL query to sort by state:

$sql = 'SELECT park_id, name, town, state, country
FROM tpf_parks WHERE country = "USA" AND type = "tp" ORDER BY state ASC, name ASC';
$result = $pdo->query($sql);

and this is the php that currently shows the first of each new letter that needs to somehow be adapted to show the full state name:

<?php
$name = '';

foreach($result as $key=>$row){
    if(substr($row['state'],0,1)!=$name) echo '<br /><br />
<p class="indexletters">-<a name="'.substr($row['state'],0,1).'">'.substr($row['state'],0,1).'</a>-</p>';
    $name = substr($row['state'],0,1);

    echo "<a href='park.php?park_id=".$row['park_id']."'>

<h2>".$row['name']."</h2>
<h3>".$row['town'].", ".$row['state'].", ".$row['country']."</h3></a><hr>";

}

?>

Thank you for the help.

Answers


The code substr($row['state'],0,1) takes the first letter of the state. If you want to group them by the actual state name, just replace all substr($row['state'],0,1) to just $row['state'].


Need Your Help

In a hybrid asp.net mvc/webforms app, why does my redirect have a MIME of 'text/plain'?

asp.net asp.net-mvc webforms

For one reason or another, I have a site where the logout is performed then the user is redirected to the landing site which is separate (the landing is hosted in Azure, the main is hosted elsewher...

Need a quick JavaScript/jQuery Validation

javascript jquery

I have a 17000 line page with lots of HTML/JavaScript/jQuery and it's always frustrating when I make a typo and there's no clue when the page loads into the browser what the problem is. It just -

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.