a to z listing

[brown2005]

$sql = mysql_query("SELECT * FROM test ORDER by test_name ASC;");
			   
while($array = mysql_fetch_array($sql))
{

   $text = $array['test_name'];

     echo"$text<br>";

}

 

i have this code which generates results like this

 

apple

andrew

bath

cabbage

carrot

rabbits

 

but i want

 

a

 

apple , andrew

 

b

 

bath

 

c

 

cabbage , carrot

 

r

 

rabbits

 

 

any help please?

[trq]

<?php

if ($result = mysql_query("SELECT test_name FROM test ORDER by test_name ASC;")) {
  if (mysql_num_rows($result)) {
    $letter = '';
    while ($array = mysql_fetch_array($result)) {
      if ($letter != substr($array['test_name'],0,1)) {
        $letter = substr($array['test_name'],0,1);
            echo "<h1>$letter</h1>";
      }
      echo "<p>" . $array['test_name'] . "</p>";

    }
  }
}

?>

[Barand]

try

<?php
$sql = "SELECT SUBSTRING(test_name, 1, 1) as initial,
        GROUP_CONCAT(test_name ORDER BY test_name SEPARATOR ', ') as items
        FROM test
        GROUP BY initial";
$res = mysql_query($sql) or die (mysql_error());
while (list($init, $items) = mysql_fetch_row($res))
{
    echo "<h3>$init</h3>";
    echo $items;
}

?>

[brown2005]

thanks very much to both of you they are both helpful.

[brown2005]

sorry to be a pain but i have just realised what I needed to do.. I am using Barand's code. but i want each item to be <a href='$items'>$items</a>

[cooldude832]

so then write it as that

[brown2005]

	while(list($init, $items) = mysql_fetch_row($res))
{
    
	echo "<h3>$init</h3>";
    echo "<a href='$items'>$items</a>";

}

 

ive tried that.. but it doesn't work. puts a link on the whole list for each letter

[cooldude832]

well of course that will cause that is how you wrote it

<?php
$sql = "SELECT SUBSTRING(test_name, 1, 1) as initial,
        GROUP_CONCAT(test_name ORDER BY test_name SEPARATOR ', ') as items
        FROM test
        GROUP BY initial";
$res = mysql_query($sql) or die (mysql_error());
$letter = "";
echo "<ul>";
while ($row = mysql_fetch_row($res)){
if($row['inital'] != $letter){
	if($letter != ''){
	echo "</ul></li>";
}
	echo "<li><h3>$init</h3><ol>";
	$letter = $row['inital'];
}
echo "<li><a href=\"".$row['items']."\">".row['items']."</a></li>\n";
}
echo "</li></ul>";
?>

 

[Barand]

<?php
mysql_connect ('localhost');
mysql_select_db('test3');

$sql = "SELECT SUBSTRING(test_name, 1, 1) as initial,
        GROUP_CONCAT('<a href=\"',test_name, '\">', test_name, '</a>' ORDER BY test_name SEPARATOR ', ') as items
        FROM test
        GROUP BY initial";
$res = mysql_query($sql) or die (mysql_error());
while (list($init, $items) = mysql_fetch_row($res))
{
    echo "<h3>$init</h3>";
    echo $items;
}

?>

[brown2005]

thank you very much..

[cooldude832]

barand you still aren't giving that heading they want I think.

 

I see you have Group BY initial so each row should have 1 initial with a bunch of items that have said initial right?  and then if that is the case how do they get all the results of the row in 1 variable. 

 

I see what u saying now the SQL is doing it, my way works as well with php driving it, but you will probably want to select items differently 

my query should be

<?php
$q = "SELECT SUBSTRING(test_name, 1, 1) as initial, test_name as items FROM test ORDER BY test_name";
?>

As all I care about is the inital and test_name and not organizing via mysql

[Barand]

http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html#function_group-concat

[cooldude832]

that is where mysql always seems to berak down for me, I see what you are doing now.

 

Its returning a single formatted string for each initial.  So at most they should be 26+a blank start + 0-9 so 35 different rows.

 

I should get better at mysql, but I see it easier to let php handle the work in case I want to alter them later. so I don't have to query twice or the same data twice in 1 query