CODEWITHSUNDEEP
phpmysql
nmsdvid
nmsdvid

Reputation: 2898

List results in groups

I'm having trouble 'sorting' my mysql results in php.

Here's the database I have

id¦ value  ¦ category
---------------------------
1 ¦ Value1 ¦ category1
2 ¦ Value2 ¦ category2
3 ¦ Value3 ¦ category2
4 ¦ Value4 ¦ category3
5 ¦ Value5 ¦ category1
6 ¦ Value6 ¦ category1


What I'm trying to do, is to get a result in php looking like this:

category1

category2

category3


Any help is useful. Thanks

Upvotes: 1

Views: 183

Answers (6)

hdvianna
hdvianna

Reputation: 453

Here is a solution done with just one loop.

$currentCategory = null;
$closeTag = "";

//Using MySQLi since mysql_* functions are obsoletes
$mysqli = new mysqli($host, $username, $password, $dbname);

/**
 * Here I use an ORDER BY to group the result by categories
 */
$selectStmt = $mysqli->prepare("SELECT id, value, category FROM table_name ORDER BY category ASC");
$selectStmt->execute();        
$selectStmt->bind_result($id, $value, $category);
while($selectStmt->fetch()) {
    if ($currentCategory != $category) {
        /**
         * In the first iteration this variable will be empty, so the <ul> tag 
         * will not be closed
         */
        echo $closeTag; //Close the last category list
        echo $category;
        echo '<ul>';            
        $closeTag = '</ul>';
        $currentCategory = $category;
    }
    echo '<li>' . $value . '</li>';
}
echo '</ul>';

Upvotes: 0

nmsdvid
nmsdvid

Reputation: 2898

thanks everyone for the help. I'm going to use a mix of AlienWebguy's and jchavannes's code which is working perfect, just what i was searching for.

the code looks like this:

SQL:

$SQL = "SELECT * FROM table_name";
$results = mysql_query($SQL);

PHP:

while($row = mysql_fetch_assoc($results)) {
    $myArray[$row['category']][] = $row['value'];   
}

foreach($myArray as $key => $val)
{
    echo $key;
    echo '<ul>';

    foreach($val as $v)
    {
        echo '<li>' . $v . '</li>';
    }

    echo '</ul>';
}

Upvotes: 0

xkeshav
xkeshav

Reputation: 54016

use GROUP_CONCAT and you will get the output will be in string separated by ,

      SELECT GROUP_CONCAT(value) AS valueList FROM tableName GROUP BY category

You can also use some format of GROUP_CONCAT(). Like

SELECT GROUP_CONCAT( value SEPARATOR ‘-’ )

above query will use - instead of ,

To change the order and shorting 

SELECT GROUP_CONCAT( value ORDER BY Category DESC )

and then re arrange by ur scripting language, if using php then try with explode()

Note: GROUP_CONCAT() ignores NULL values.

Upvotes: 0

SeanCannon
SeanCannon

Reputation: 77966

SQL:

$SQL = 'SELECT * FROM theTable SORT BY category ASC';

PHP:

$results = mysql_fetch....($SQL);
$record_table = array();

// Create the array for the list
foreach($results as $row)
{
   $record_table[$row['category']][] = $row['value'];
}

// Output the HTML list
foreach($record_table as $key => $val)
{
    echo '<ul id="' . $key . '">';

    foreach($val as $v)
    {
        echo '<li>' . $v . '</li>';
    }

    echo '</ul>';
}

Upvotes: 1

jchavannes
jchavannes

Reputation: 2710

SELECT id, value, category FROM table;

Then in PHP

while($row = mysql_fetch_assoc($result)) {
    $myArray[$row['category']][] = $row['value'];
}

Upvotes: 1

Aleksey Korzun
Aleksey Korzun

Reputation: 680

Loop through the result-set, create array key within your initial array for each new category you encounter and push value data into it.

If key for category exists, skip the key creation stage and just add the data.

Then you can loop through generated array and output all the data (array of values) per category (key).

Upvotes: 0

Related Questions