Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : alphabetical list separating...

[Steven]

I have a database with a listing of campground names... I want to separate them with a letter on the outputing html..

for example i have:

Acorn Camp East
Acorn Camp West
Agnew Horse Camp
Agnew Meadow
Almanor
Angel Island SP
Antlers
Anza-Borrego Desert SP
Arroyo Seco
Aspen Group
Aspen Groupyo
Aspen Hollow
Auburn Sra Boat
Badger Flats
Bailey Cove
Barton Flats
Belknap
Benbow Lake SRA
Big Basin Redwoods SP
Big Cove

i want it to look like this

A

Acorn Camp East
Acorn Camp West
Agnew Horse Camp
Agnew Meadow
Almanor
Angel Island SP
Antlers
Anza-Borrego Desert SP
Arroyo Seco
Aspen Group
Aspen Groupyo
Aspen Hollow
Auburn Sra Boat

B

Badger Flats
Bailey Cove
Barton Flats
Belknap
Benbow Lake SRA
Big Basin Redwoods SP
Big Cove

i am currently just using a while loop and echo. I thought about using substr to get the first letter and then using a for loop or something, but I can not formulate how to do this exactly.

any suggestions would be great

[arkin]

I'd do it using order by `name` ASC.

Then i'd use,

if ($name{1}!=$cache_name) { echo "<h1>$name{1}</h1>";$cache_name=$name{1}; }

$name{1} being the substr, $name being the returned name row.

Its late, this is just an outline.

[Vdevelopers]

PHP Code:

echo "<h1>A</h1><br>\n\r";
for ( $i=0; $i<count($sortedarray); $i++ ) {
  if ( $i > 0 && substr($sortedarray[$i], 0, 1) != substr($sortedarray[$i-1], 0, 1) ) 
    echo "<h1>" . substr($sortedarray[$i], 0, 1) . "</h1><br>\n\r";
  echo $sortedarray[$i] . "<br>\n\r";
} 


Where, of course, $sortedarray refers to your array of items that are already sorted alphabetically. There are better ways to do it, and far more elegant ways, but I haven't reached programmer nirvana yet and so the elegance factor in my code is zilch. But it works.

[RH Swaroop]

Originally Posted by Vdevelopers

PHP Code:

echo "<h1>A</h1><br>\n\r";
for ( $i=0; $i<count($sortedarray); $i++ ) {
  if ( $i > 0 && substr($sortedarray[$i], 0, 1) != substr($sortedarray[$i-1], 0, 1) ) 
    echo "<h1>" . substr($sortedarray[$i], 0, 1) . "</h1><br>\n\r";
  echo $sortedarray[$i] . "<br>\n\r";
} 


Where, of course, $sortedarray refers to your array of items that are already sorted alphabetically. There are better ways to do it, and far more elegant ways, but I haven't reached programmer nirvana yet and so the elegance factor in my code is zilch. But it works.

FYI, that's a pretty good piece of code!

[dgeorge]

ok, you said you were currently using a while loop with an echo. I know this isnt correct syntax but you should get the idea

$strCurrentLetter = "";
$strThisLetter = "";

while($arrRecordset){
	
	$strThisLetter = strtoupper(substr($strRecord, 0,1));

	if($strThisLetter != $strCurrentLetter){
		$strCurrentLetter = $strThisLetter;
		echo '<h1>'.$strCurrentLetter.'</h1><br>';
	}

	echo $strRecord.'<br>';
}

[Steven]

Originally Posted by Vdevelopers

PHP Code:

echo "<h1>A</h1><br>\n\r";
for ( $i=0; $i<count($sortedarray); $i++ ) {
  if ( $i > 0 && substr($sortedarray[$i], 0, 1) != substr($sortedarray[$i-1], 0, 1) ) 
    echo "<h1>" . substr($sortedarray[$i], 0, 1) . "</h1><br>\n\r";
  echo $sortedarray[$i] . "<br>\n\r";
} 


Where, of course, $sortedarray refers to your array of items that are already sorted alphabetically. There are better ways to do it, and far more elegant ways, but I haven't reached programmer nirvana yet and so the elegance factor in my code is zilch. But it works.

It will just keep repeating a over and over.

[RH Swaroop]

Originally Posted by Steven

It will just keep repeating a over and over.

why would it repeat over and over?

I apologise if I overlooked something there.

[Steven]

im probably going to scrap the project getting tired of the frustration

[Vdevelopers]

Steven, there is no reason it would repeat over and over unless it reset $i in every iteration . Have you tried the code?

PHP Code:

echo "<h1>A</h1><br>\n\r";
for ( $i=0; $i<count($sortedarray); $i++ ) {
  if ( $i > 0 && substr($sortedarray[$i], 0, 1) != substr($sortedarray[$i-1], 0, 1) ) {
    echo "<h1>" . substr($sortedarray[$i], 0, 1) . "</h1><br>\n\r";
  }
  echo $sortedarray[$i] . "<br>\n\r";
} 


That may be better structured for comprehension and readability.

[Steven]

Actual code:

PHP Code:

<?PHP
include('includes/globals.php');
$query = "SELECT campcity,campname,campid FROM camps ORDER BY campname ASC";

$result = mysql_query($query);
if (!$result) {
   die('Invalid query: ' . mysql_error());
}
while ($row = mysql_fetch_array($result)) {
if ($row['0'] == '') {
$row[0] = 'NULL';
}
echo "<a href=\"index.php?id=$row[2]\">$row[1] - $row[0]</a><br>";
        }
mysql_free_result($result);

?>

outputs something like this:

Acorn Camp East - New Hogan Lake
Acorn Camp West - New Hogan Lake
Agnew Horse Camp - NULL
Agnew Meadow - NULL
Almanor - Lassen
Angel Island SP - NULL
Antlers - Shasta-Trinity
Anza-Borrego Desert SP - NULL
Arroyo Seco - Los Padres
Aspen Group - Tahoe
Aspen Groupyo - NULL
Aspen Hollow - Sequoia
Auburn Sra Boat-In - NULL
Badger Flats - Sierra
Bailey Cove - Shasta-Trinity
Barton Flats - San Bernardino
Belknap - Sequoia
Benbow Lake SRA - NULL
Big Basin Redwoods SP - NULL
Big Cove - Plumas
Big Meadow - Stanislaus
Big Pine Canyon - NULL

Camp name followed by the city.

What i need is it seperated by letter like:

A

Acorn Camp East - New Hogan Lake
Acorn Camp West - New Hogan Lake
Agnew Horse Camp - NULL
Agnew Meadow - NULL
Almanor - Lassen
Angel Island SP - NULL
Antlers - Shasta-Trinity
Anza-Borrego Desert SP - NULL
Arroyo Seco - Los Padres
Aspen Group - Tahoe
Aspen Groupyo - NULL
Aspen Hollow - Sequoia
Auburn Sra Boat-In - NULL

B

Badger Flats - Sierra
Bailey Cove - Shasta-Trinity
Barton Flats - San Bernardino
Belknap - Sequoia
Benbow Lake SRA - NULL
Big Basin Redwoods SP - NULL
Big Cove - Plumas
Big Meadow - Stanislaus
Big Pine Canyon - NULL

C
...
...
..

[Vdevelopers]

PHP Code:

<?PHP
include('includes/globals.php');
  $query = "SELECT campcity,campname,campid FROM camps ORDER BY campname ASC";
  $result = mysql_query($query);

  if ( $result && mysql_num_rows( $result ) ) {
    echo "<h1>A</h1><br>";
    for ( $i=0; $i<mysql_num_rows( $result ); $i++ ) {
      if ( $i > 0 && substr(mysql_result( $result, $i, "campname" ), 0, 1) != substr(mysql_result( $result, $i - 1, "campname" ), 0, 1) )
        echo "<h1>" . substr(mysql_result( $result, $i, "campname" ), 0, 1) . "</h1><br>\n\r";

      echo "<a href=\"index.php?id=" . mysql_result( $result, $i, "campid" ) . "\">" . mysql_result( $result, $i, "campname" );
      if ( mysql_result( $result, $i, "campcity" ) ) {
        echo " - " . mysql_result( $result, $i, "campcity" );
      }
      echo "</a><br>";
    }
    mysql_free_result( $result );
  } else {
    die( "Query failed:" . mysql_error() );
  }
?>

Fin.

[Steven]

Thanks bud! it works now

[Vdevelopers]

Glad I could help.

[thartdyke]

That code will fail if you have no camps beginning with the letter 'A' (it always spits out the header).

I prefer to let the database do the work in cases like this. You can use one query for all the initial letters that you have:

$queryHead = "SELECT DISTINCT LEFT(campname, 1) AS head FROM camps ORDER BY head";

After that, step through each row, and look up the camps that start with that name. In full:

PHP Code:

include('includes/globals.php');
  $queryHead = "SELECT DISTINCT LEFT(campname, 1) AS head FROM camps ORDER BY head";
  $resultHead = mysql_query($queryHead);
  
  while ($rowHead(mysql_fetch_array($resultHead) {
      echo "<h1>" . $rowHead['head'] . "</h1><br>\n";   /* This is the single character header */
    
    $query = "SELECT campcity,campname,campid FROM camps WHERE LEFT(campname, 1) = \'" . $rowHead['head'] . "\' ORDER BY campname ASC";
    $result = mysql_query($query);
    
    while ($row(mysql_fetch_array($result) {
        echo "<a href=\"index.php?id=" . $row['campid'] . "\">" . $row['campname'] . " - " . $row[campcity] . "</a><br>\n";
    }
  }
  
  $query = "SELECT campcity,campname,campid FROM camps ORDER BY campname ASC";
  $result = mysql_query($query);