Alphabetical Organizing - some snags

[EternalSorrow]

I've got a code which allows me to take the first letter of every title in a database and create an alphabetical listing, like so:

 

Table 'feudal' holds these titles:

+ some guys

3000 Guys

bed bugs

Bed bugs

 

The titles are correctly assigned to their proper titles and outputted on the page, but too thoroughly.  The code differentiates between capital and lowercase letters, when I want all letters to be grouped together.  Here's an example:

 

What I want:

B

bed bugs

Bed bugs

 

What I get:

b

bed bugs

 

B

Bed bugs

 

The other problem I have is some of the titles start with characters and numbers, which are then given their own heading.  I would like those characters andnumbers to fall under a generic symbol, such as the pound (#).  Here's an example:

 

What I want:

#

+ some guys

3000 Guys

 

What I get:

+

+ some guys

 

3

3000 Guys

 

So in summary, can anyone help me figure out how to group upper and lower case letters together, and how to place all characters and numbers under a single heading?

 

Here's the code for reference (minus connection, of course):

<?php

$query = "SELECT *, substring(`title`,1,1) AS firstLetter FROM feudal WHERE `type` = 'Fanart' GROUP BY title, author ORDER BY title";
$result = mysql_query( $query ) or die(mysql_error());

$firstLetter = '';
while ($row = mysql_fetch_assoc($result))
{
$url = $row[url];
$author = $row[author];
$title = $row[title];

if ($firstLetter != $row['firstLetter']) {
            //Assign category to holding var
$firstLetter = $row['firstLetter'];
            //echo firstLetter heading
echo '<h3><a name="'.$firstLetter.'">'.$firstLetter.'</a></h3>';
}

echo '<li><a href="'.$url.'" target="_blank">'.$title.'</a> by '.$author.'</li>';

}
?>

[ngreenwood6]

the uppercase/lowercase issue can be resolved by changing this line:

if ($firstLetter != $row['firstLetter']) {

 

to this:

if (ucwords($firstLetter) != ucwords($row['firstLetter'])) {

[EternalSorrow]

the uppercase/lowercase issue can be resolved by changing this line:

if ($firstLetter != $row['firstLetter']) {

 

to this:

if (ucwords($firstLetter) != ucwords($row['firstLetter'])) {

 

That worked perfectly, thanks for the tip.

 

Now does anyone have any idea on how to manage the special characters and numbers?  I'm thinking some sort of IF statement where if the first letter isn't alphabetical they can be grouped, or using regex (always tricky, though), but I've never seen any examples explaining how this might be done.

[ngreenwood6]

try this:

 

<?php

$query = "SELECT *, substring(`title`,1,1) AS firstLetter FROM feudal WHERE `type` = 'Fanart' GROUP BY title, author ORDER BY title";
$result = mysql_query( $query ) or die(mysql_error());

$words = array('1','2','3','4','5','6','7','8','9','+');

$firstLetter = '';
while ($row = mysql_fetch_assoc($result))
{
$url = $row[url];
$author = $row[author];
$title = $row[title];

if(in_array($firstLetter,$words)){
  $firstLetter = '#';
  echo '<h3><a name="'.$firstLetter.'">'.$firstLetter.'</a></h3>';
} else if (ucwords($firstLetter) != ucwords($row['firstLetter'])) {
            //Assign category to holding var
$firstLetter = $row['firstLetter'];
            //echo firstLetter heading
echo '<h3><a name="'.$firstLetter.'">'.$firstLetter.'</a></h3>';
}

[EternalSorrow]

The code produced some strange results.  As far as I can tell the $words skips the first instance of the special character or number, but rightly adjusts the second.  It repeats this pattern, like this:

 

Table

+miss

+this

+yes

+zis

 

Those lines read like this:

+

+miss

 

#

+this

 

+

+yes

 

#

+zis

 

I am not really sure why the code would be skipping every other instance.  It does, however, work when a single instance occurs.

[ngreenwood6]

try this:

 

<?php

$query = "SELECT *, substring(`title`,1,1) AS firstLetter FROM feudal WHERE `type` = 'Fanart' GROUP BY title, author ORDER BY title";
$result = mysql_query( $query ) or die(mysql_error());

$words = array('1','2','3','4','5','6','7','8','9','+');

$firstLetter = '';
while ($row = mysql_fetch_assoc($result))
{
$url = $row[url];
$author = $row[author];
$title = $row[title];

if(in_array($firstLetter,$words)){
  if($firstLetter != '#'){
    $firstLetter = '#';
  }
  echo '<h3><a name="'.$firstLetter.'">'.$firstLetter.'</a></h3>';
} else if (ucwords($firstLetter) != ucwords($row['firstLetter'])) {
            //Assign category to holding var
$firstLetter = $row['firstLetter'];
            //echo firstLetter heading
echo '<h3><a name="'.$firstLetter.'">'.$firstLetter.'</a></h3>';
}

 

All I did there was add a line to check that the if the first letter has already been assigned and if it has do not repeat it.

[EternalSorrow]

The results are still (frustratingly) repeating.

[ngreenwood6]

oh sorry about that teh echo statement should also be inside of the if statement.

[EternalSorrow]

I placed the echo within the IF statement but the results are still the same.  Here's the code as it now stands (now minus the $query, which hasn't changed):

 

$words = array('1','2','3','4','5','6','7','8','9','+',':');

$firstLetter = '';
while ($row = mysql_fetch_assoc($result))
{
$url = $row[url];
$author = $row[author];
$title = $row[title];

if(in_array($firstLetter,$words)){
  if($firstLetter != '#'){
    $firstLetter = '#';
  echo '<h3><a name="'.$firstLetter.'">'.$firstLetter.'</a></h3>';
  }

} else if (ucwords($firstLetter) != ucwords($row['firstLetter'])) {
            //Assign category to holding var
$firstLetter = $row['firstLetter'];
            //echo firstLetter heading
echo '<h3><a name="'.$firstLetter.'">'.$firstLetter.'</a></h3>';
}

[ngreenwood6]

ok so is it working?

[EternalSorrow]

I'm afraid it still repeats incorrectly (skipping first, etc.).

[EternalSorrow]

I did find this tutorial which on the last page addresses special characters using ASCII, but I am not sure how to implement it into the current code (always a problem of mine).

[EternalSorrow]

Well, perhaps a change in code is necessary considering all the variety of ascii characters which are housed in the database (everything from a period to parenthesis, which means ascii 32 through 90 will be needed).

 

I've managed to get a page set up from a tutorial I found here.  It does have advice on how to deal with those odd ascii characters at the end, but I cannot figure out how to implement that into the main list.  Anyone have any tutorials or advice on how to implement this bit of code:

 

for ($i=32; $i< =90; $i++) {
  $letter = chr($i);
  ...code...
}

 

into the main code below?:

<?php

$sql = "SELECT *, UPPER(SUBSTRING(title,1,1)) AS letter FROM feudal WHERE `type` = 'Fanart' GROUP BY title, author ORDER BY title LIMIT 50";
$query = mysql_query( $sql ) or die(mysql_error());

while ($records = @mysql_fetch_array ($query)) {
$alpha[$records['letter']] += 1;
${$records['letter']}[$records['author']] = $records['title'];
}

echo '<ul class="alphabet">
<li><a href="#other">#</a></li>';

// Create Alpha link Listing
foreach(range('A','Z') as $i) {
echo (array_key_exists ("$i", $alpha)) ? '<li><a href="#'.$i.'">'.$i.'</a></li>' : '<li>'.$i.'</li>';
}

echo '</ul><ol class="listfanart">';

// Create Data Listing
foreach(range('A','Z') as $i) {
if (array_key_exists ("$i", $alpha)) {
echo '<a name="'.$i.'"></a><h3>'.$i.'<div style="font-size: 10px; float: right;"><a href="#">top</a></h3>';
foreach ($$i as $key=>$value) {
echo '<li><a href="" target="_blank">'.$value.'</a> by '.$key.'</li>
';

}
}
}

?>
</ol>