CODEWITHSUNDEEP
phpmysqlmysqli
Citizen SP
Citizen SP

Reputation: 1411

Sort items by category

I have a table containing the following records:

product_id | title   | category
------------------------------------
1          | apple   | mobile
2          | android | mobile
3          | dell    | desktop
4          | hp      | desktop

and the following query: 

$sql = "SELECT product_id, title, category FROM products ORDER BY category"; 
$stmt = $db->prepare($sql); 
$stmt->execute(); 
  while($results = $stmt->fetch(PDO::FETCH_ASSOC)) 
{ 
echo $results["product_id"];
echo $results["title"];
echo $results["category"];
}

The question is how to split the results and display all records in a list sorted by category as below:

Desktop

Upvotes: -1

Views: 139

Answers (4)

user1063280
user1063280

Reputation: 40

SELECT * FROM products ORDER BY category. This is to sort using category, your example output file seems different what are you trying to do?

Upvotes: 0

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324610

After sorting your items (ORDER BY category, title), do something like this:

$last_category = "";
while(/* get a row here */) {
    if( $last_category != $row['category']) {
        if( $last_category != "") echo "</ul>";
        echo "<ul>";
        $last_category = $row['category'];
    }
    echo "<li>".$row['title']."<li>";
}
if( $last_category != "") echo "</ul>";

Upvotes: 0

aykut
aykut

Reputation: 3094

Group your records after get the result set:

$products = array();

while ($results = $stmt->fetch(PDO::FETCH_ASSOC)) 
{ 
    $products[$results['category']][] = array(
        'product_id' => $results['product_id'],
        'title'      => $results['title']
    );
}

foreach ($products as $category => $productList)
{
    echo $category . '<br>';

    foreach ($productList as $product)
    {
        echo $product['title'] . '<br>';
    }

    echo '<hr />';
}

Upvotes: 1

lucasnadalutti
lucasnadalutti

Reputation: 5948

Use SELECT * FROM products GROUP BY category ORDER BY title

Upvotes: -1

Related Questions