Displaying query results by letter

wayoutpost

Hello all. I am a definite newbie to PHP and I'm kinda stuck. I've seen some other posts about this issue, but I never see the final answer to the guy's question.

I'm trying to display a query of a music database by the first letter of the artist's name/band name. Something like this:

A
ABBA Worst Hits LP Catalogue#: 666 Price: $6.66

B
Beuford's Band Albums Title CD Catalogue#: 999 Price $9.99

C
etc, etc.

OK, you get it. Here's my code so far:

//connect to database

				$db="dig513_auctions";
						mysql_select_db($db, $con);
						$query = mysql_query("SELECT * FROM itemtype, music WHERE itemtype.typeid=music.format ORDER BY music.artist DESC");
						$result=array(); 
					?>
				<?php 
					while ($row = mysql_fetch_array($query))  
					{  
					$first_char =  strtoupper(substr($row['artist'],0,1)
					);
					if(!array_key_exists($first_char,$result))  
					$result[$first_char] = array();  
					array_push($result[$first_char],$row['artist');  
					}  
					foreach ($result as $key=>$value)  
					{  
					echo "<b>$key</b><br>";  
					foreach ($value as $v)  
					{  
					echo ucfirst($v)."<br>";  
					}  
					}
					?>

As you may have guessed, I've 'borrowed' much of this code from elsewhere. Now that I can display each character (if it exists), how do I display the results underneath each letter?

Similar to Gary Gnu's Good Gnews....any Ghelp is Good Ghelp at this point <am i showing my age here?>

Cheers,
Jonathan
Sao Paulo

sarahk

here's my take on your code.

<?php 
$db="dig513_auctions"; 
mysql_select_db($db, $con); 
$query = mysql_query("SELECT * FROM itemtype, music WHERE itemtype.typeid=music.format ORDER BY music.artist DESC"); 

$old_char = '#';  // anything that won't be real
while ($row = mysql_fetch_array($query)) 
{ 
  $first_char = strtoupper(substr($row['artist'],0,1) ); 
  if ($first_char != $old_char)
  {
    echo "<b>{$first_char}</b><br>";
    $old_char = $first_char;
  }//first letter check
  echo $row['artist'].'<br>'; 
} //while
?>

wayoutpost

Thanks a lot! That worked great.

I have a new problem now...I can't seem to make more than one row with the same artist name come up. For example, I have two albums by the band "Mutantes, Os" in my database, but only one item per letter is appearing (http://www.wayoutpost.com/music1.php).

Any idea why this is happening?

here's my code:

<table align="left" cellspacing="0" cellpadding="0" height="100%">
				<?php $con=mysql_connect("localhost", "user", "pw")or die("Connect Error: ".mysql_error());
							$db="dig513_features";
							mysql_select_db($db, $con);
							$query = mysql_query("SELECT * FROM music ORDER BY artist ASC");
							$result=array(); 
							$old_char = '#';
							?>
					<?php
						while ($row = mysql_fetch_array($query))  

						{  

						$first_char =  strtoupper(substr($row['artist'],0,1));
						if ($first_char != $old_char) 
						  { 
							echo "<tr><td bordercolor=\"#EEEEEE\" align=\"left\" colspan=\"8\">";
							echo "<span class=\"menutext\">";
							echo "<b>{$first_char}</b>"; 
							echo "</span>";

						echo "</td>";
						echo "</tr>";
						echo "<tr>";
						echo "<td bordercolor=\"#EEEEEE\" align=\"left\">"; echo $row['artist']; 
						echo "</td>";
						echo "<td bordercolor=\"#EEEEEE\" align=\"left\">"; echo $row['title']; 
						echo "</td>";
						echo "<td bordercolor=\"#EEEEEE\" align=\"left\">"; echo $row['format']; 
						echo "</td>";
						echo "<td bordercolor=\"#EEEEEE\" align=\"left\">"; echo $row['label/cat#']; 
						echo "</td>";
						echo "<td bordercolor=\"#EEEEEE\" align=\"left\">"; echo $row['cover']; 
						echo "</td>";
						echo "<td bordercolor=\"#EEEEEE\" align=\"left\">"; echo $row['media']; 
						echo "</td>";
						echo "<td bordercolor=\"#EEEEEE\" align=\"left\">"; echo $row['comment']; 
						echo "</td>";
						echo "<td bordercolor=\"#EEEEEE\" align=\"left\">"; echo $row['price']; 
						echo "</td>";
						echo "</tr>";
						$old_char = $first_char;
						}
					}
					?>
			<tr>
				<td colspan="8">
					<?php
							mysql_close($con);
					?>
				</td>
			</tr>
		</table>

sarahk

yeah, you put too much in the if statement which checks for the new letter. I've put just the while statement below

while ($row = mysql_fetch_array($query))   

                        {   

                        $first_char =  strtoupper(substr($row['artist'],0,1)); 
                        if ($first_char != $old_char)  

                          {  

                            echo "<tr><td bordercolor=\"#EEEEEE\" align=\"left\" colspan=\"8\">"; 
                            echo "<span class=\"menutext\">"; 
                            echo "<b>{$first_char}</b>";  

                            echo "</span>"; 

                        echo "</td>"; 
                        echo "</tr>"; 
                        $old_char = $first_char; 
                        } //end check of new first char
//we want this code to run every time
                          echo "<tr>"; 
                          echo '<td bordercolor="#EEEEEE" align="left">'; 
echo $row['artist'];  

                            echo '</td>'; 
                            echo '<td bordercolor="#EEEEEE" align="left">'; 
echo $row['title'];  

                            echo "</td>"; 
                            echo "<td bordercolor=\"#EEEEEE\" align=\"left\">"; echo $row['format'];  

                            echo "</td>"; 
                            echo "<td bordercolor=\"#EEEEEE\" align=\"left\">"; echo $row['label/cat#'];  

                            echo "</td>"; 
                            echo "<td bordercolor=\"#EEEEEE\" align=\"left\">"; echo $row['cover'];  

                            echo "</td>"; 
                            echo "<td bordercolor=\"#EEEEEE\" align=\"left\">"; echo $row['media'];  

                            echo "</td>"; 
                            echo "<td bordercolor=\"#EEEEEE\" align=\"left\">"; echo $row['comment'];  

                            echo "</td>"; 
                            echo "<td bordercolor=\"#EEEEEE\" align=\"left\">"; echo $row['price'];  

                            echo "</td>"; 
                            echo "</tr>"; 

                    } 
                    ?>

wayoutpost

Thank you so much. I greatly appreciate it. I'm so much further along than I ever thought I'd be at this point. Shiiii....two weeks ago, I didn't know a single php line. Thanks to the folks on this site, I'm almost out of "newbie" mode <ok, well, maybe>.

thanks so much Sarah. Here's how we're doing so far:

http://www.wayoutpost.com/features.shtml

Greetings from Sao Paulo!

sarahk

this is off topic abit but...

your screen looks awesome. You've had some good design work done.

Now...

you have to optimise it for the search engines

move all that stylesheet stuff out into a separate file like you have with the other css.

create a div, or give the table a class and get all the formatting off each row
a) it will load faster
b) the search engines will find the real content more easily.

if you need help with stylesheets I recommend http://www.richinstyle.com and http://www.weberdev.com/phorum/list.php?f=15

then you just have to pass the right stuff to paypal and you're in business.

wayoutpost

I'll have a look at your site recommendations. I was wanting to put classes on my tables, but I hadn't learned how to do that yet. It seems fairly simple so I'll get to it as soon as I can. Thanks so much for all your help!

Viva os Kiwis!!

P.S. I have a couple of friends here in Sao Paulo that are Kiwis. They own a great bar called the "All Black". Funny name for a bar in Brazil, but they're doing great and making fistsful of money I think.

http://www.allblack.com.br/