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_ASS OC)) {
$echo .= '<li >'.$row["word"].'-'.$row[" lng"].'</l i>';
}
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
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
$echo .= '<li >'.$row["word"].'-'.$row["
}
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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>';
}
?>
Sample here