question about displaying data

bbmak

this is a script that displays all the artists that last name's first letter is A
Are there any way I can loop this script A-Z because i do not want to type the thing out 26 times.

$query=("SELECT artist_id,artist_name,english_name FROM artist WHERE english_name LIKE 'a%' ORDER BY 'english_name' ASC ");
$result=mysql_query($query) or die(mysql_error()); 

//html page starts
echo "<html>";
echo "<meta http-equiv=\"Content-Type\" content=\"text/html; charset=big5\">";
echo "All Artist Name:<br><br>";
echo "A:<br>";

//table start
echo "<center><table width=\"750\" border=\"1\">"; 

while($row=mysql_fetch_array($result))
{ 
$artist_name = $row["artist_name"];
$english_name = $row["english_name"]; 

//loop all artist A

echo "<td><a href=\"show.php?artist_id={$row['artist_id']}\">{$row['english_name']}&nbsp;&nbsp;{$row['artist_name']} </a><br /></td>\n"; 
}

echo "</table></center> \n";
//end with loop A

konsu

duplicating this 26 times for each letter is outrageous ; -). looping this is better but still not good. i would rewrite the database query to return ALL artists sorted by their last names, and keep the while loop, but inside it i would check if the first letter has changed from the previous iteration and if so, i would open a new table like you do before the loop. do you see what i mean?

bbmak

I dont get what you mean, can you show me the output ??

can you do the code because i am not sure how to do it.

Can you show me how to loop this code 26 times from a range of a-z.

jansky

maybe this could help i use for loop, but i never test

<?
for ( $x=ord('a'); $x<ord('z'); $x++) // character from a to z
{
$character=chr($x);
$query=("SELECT artist_id,artist_name,english_name FROM artist WHERE english_name LIKE '$character%' ORDER BY 'english_name' ASC ");
$result=mysql_query($query) or die(mysql_error());
			//html page starts
			echo "<html>";
			echo "<meta http-equiv=\"Content-Type\" content=\"text/html; charset=big5\">";
			echo "All Artist Name:<br><br>";
			echo strtoupper($character).":<br>";

			//table start
			echo "<center><table width=\"750\" border=\"1\">"; 

			while($row=mysql_fetch_array($result))
			{ 
			$artist_name = $row["artist_name"];
			$english_name = $row["english_name"]; 

			//loop all artist $character

			echo "<td><a href=\"show.php?artist_id={$row['artist_id']}\">{$row['english_name']}&nbsp;&nbsp;{$row['artist_name']} </a><br /></td>\n"; 
			}

			echo "</table></center> \n";
			//end with loop $character 
}// end for loop

?>

bbmak

thank i got it
i loop the code with this
foreach(range('A','Z') as $_letter)
{
}

konsu

i was talking about something like this:


$query=("SELECT * FROM artist order by english_name acs"); 
$result=mysql_query($query) or die(mysql_error());  

//html page starts 
echo "<html>"; 
echo "<meta http-equiv=\"Content-Type\" content=\"text/html; charset=big5\">"; 
echo "All Artist Name:<br><br>"; 

while($row=mysql_fetch_array($result)) 
{
    $artist_name = $row["artist_name"]; 
    $english_name = $row["english_name"];  

    $this_letter = $english_name{0};

if (!isset($prev_letter) || $prev_letter != $this_letter)
{
    if (isset($prev_letter))
    {
        echo "</table></center> \n"; 
    }
    echo "$this_letter:<br>"; 
    echo "<center><table width=\"750\" border=\"1\">";  
}
$prev_letter = $this_letter;

echo "<td><a href=\"show.php?artist_id={$row['artist_id']}\">{$row['english_name']}&nbsp;&nbsp;{$row['artist_name']} </a><br /></td>\n";  
}

one sql query instead of 26. do not forget to close the current table after the loop.