Link to home
Start Free TrialLog in
Avatar of redpencilmedia
redpencilmedia

asked on

order by length of field

I have a result set where I want to order the result by the length of field, then display the length as a heading displayed only once.

Here is my query:
SELECT field CHAR_LENGTH(word) as lng FROM table ORDER BY lng ASC";

Here is how i loop
while ($row = mysqli_fetch_array($result,MYSQL_ASSOC)) {            
                  $echo .= '<li >'.$row["word"].'-'.$row["lng"].'</li>';      
            }
            
And my result is:

abc - 3
def - 3
ghi - 3
word -4
word1 - 5

This works fine but here I am stuck. So my question is: how can I loop the data so they are grouped like this:

3
abc
def
ghi

4
word

5
word1
SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of hielo
hielo
Flag of Wallis and Futuna image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Or use GROUP_CONCAT in your query.
Some code that will do what you want
<?php
// EMULATE THE INPUT
$input = array (
  array('word' => 'abc', 'lng' => 3),
  array('word' => 'def', 'lng' => 3),
  array('word' => 'ghi', 'lng' => 3),
  array('word' => 'abc', 'lng' => 3),
  array('word' => 'word', 'lng' => 4),
  array('word' => 'word1', 'lng' => 5),
);

// ARRAY TO STORE OUR OUTPUT TO
$rows = array();
// THIS WOULD BE YOUR 
// while ($row = mysqli_fetch_array($result,MYSQL_ASSOC)) {           
foreach($input as $i) {
  // CREATE COUNT NODE IF IT DOES NOT EXIST
  if (!isset($rows[$i['lng']])) {
    $rows[$i['lng']] = array();
  }
  // ADD ROW TO COUNT NODE
  $rows[$i['lng']][] = $i['word'];
}
// CHECKING TO SEE WHAT WE HAVE
echo "<pre>" . print_r($rows, true) . "</pre>";

// OUTPUT THE RESULT
foreach($rows as $k => $r) {
  echo "{$k}<br/>";
  echo '<ul><li>' . implode('</li><li>', $r) . '</li></ul>';
}
?>

Open in new window

Sample here