EternalSorrow Posted December 12, 2009 Share Posted December 12, 2009 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>'; } ?> Quote Link to comment Share on other sites More sharing options...
ngreenwood6 Posted December 12, 2009 Share Posted December 12, 2009 the uppercase/lowercase issue can be resolved by changing this line: if ($firstLetter != $row['firstLetter']) { to this: if (ucwords($firstLetter) != ucwords($row['firstLetter'])) { Quote Link to comment Share on other sites More sharing options...
EternalSorrow Posted December 12, 2009 Author Share Posted December 12, 2009 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. Quote Link to comment Share on other sites More sharing options...
ngreenwood6 Posted December 12, 2009 Share Posted December 12, 2009 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>'; } Quote Link to comment Share on other sites More sharing options...
EternalSorrow Posted December 12, 2009 Author Share Posted December 12, 2009 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. Quote Link to comment Share on other sites More sharing options...
ngreenwood6 Posted December 13, 2009 Share Posted December 13, 2009 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. Quote Link to comment Share on other sites More sharing options...
EternalSorrow Posted December 13, 2009 Author Share Posted December 13, 2009 The results are still (frustratingly) repeating. Quote Link to comment Share on other sites More sharing options...
ngreenwood6 Posted December 13, 2009 Share Posted December 13, 2009 oh sorry about that teh echo statement should also be inside of the if statement. Quote Link to comment Share on other sites More sharing options...
EternalSorrow Posted December 13, 2009 Author Share Posted December 13, 2009 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>'; } Quote Link to comment Share on other sites More sharing options...
ngreenwood6 Posted December 13, 2009 Share Posted December 13, 2009 ok so is it working? Quote Link to comment Share on other sites More sharing options...
EternalSorrow Posted December 13, 2009 Author Share Posted December 13, 2009 I'm afraid it still repeats incorrectly (skipping first, etc.). Quote Link to comment Share on other sites More sharing options...
EternalSorrow Posted December 13, 2009 Author Share Posted December 13, 2009 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). Quote Link to comment Share on other sites More sharing options...
EternalSorrow Posted December 13, 2009 Author Share Posted December 13, 2009 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> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.