Group mysql results by category and display them into groups under each category

by

I would use ORDER BY category instead. You can then iterate the result set like

$old = null;
foreach ($st as $s) {
  if $old != $s['id']
    echo 'Main category';
    $old = $s['id'];
  echo 'subcategory'

Update

There exist three possible solutions until now in this thread to the problem itself.

Original option 1

SELECT * FROM content group by category
foreach
  SELECT * FROM content WHERE category=$cat['category']

If one does only want to get each parent category once, one should use DISTINCT instead. One should not use GROUP BY without using any aggregation function. Combining GROUP BY with SELECT * is limited to (mostly) MySQL. You cannot select arbitrary columns in this case in ASNI SQL.

A variant of option 1

SELECT DISTINCT category FROM content ORDER BY category
foreach
  SELECT * FROM content WHERE category=$cat['category']

This is the corrected version with DISTINCT instead of GROUP BY.

It still lacks of nested query calls. For 5 parent categories, this leads to 5 queries in the loop. For 10 parent categories, there are already 10 queries inside. One should avoid this kind of growing in general.

Option 3

SELECT * FROM content ORDER BY category, menu_name

usable with the code above.

This is preferable to the other options shown due to different reasons:

  • You only need one single database query to gather all data at once. The database spends (on easy queries) most of its time parsing the SQL statement one provided and only a fraction of time to actually gather the data you requested. If you provide lots of SQL code, it has to spend a lot of time parsing it. If you provide less code, it has less to do.
  • It is easier for a database to get the data once, sort it once and return it to you once, instead of gather a part, sort a part, return a part and start all over again.

still unstated option 4

There exists an until now unstated further solution. One can use prepared statements, prepare the SQL once and run it with different ids. This would still query all categories inside the loop, but would avoid the necessity to parse SQL code every time.

Actually I do not know if this is better or worse (or sth. in between) than my solution.

More Related Contents:

  1. If number have inside number string [closed]
  2. recurring system my sql query [closed]
  3. Php date() Date and time is stuck [closed]
  4. Cannot modify header information [duplicate]
  5. Gather column name of a table from database [closed]
  6. inserting information and image in php and mysql
  7. CodeIgniter – foreach loop
  8. Project Links do not work on Wamp Server
  9. PHP/MySQL insert row then get ‘id’
  10. Using Jquery Ajax to retrieve data from Mysql
  11. Laravel migration: unique key is too long, even if specified
  12. Laravel Eloquent select all rows with max created_at
  13. PHP, MySQL error: Column count doesn’t match value count at row 1
  14. MySQL – count total number of rows in php
  15. Advantages Of MySQLi over MySQL [closed]
  16. Fix Access denied for user ‘root’@’localhost’ for phpMyAdmin
  17. SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO [duplicate]
  18. What does a successful MySQL DELETE return? How to check if DELETE was successful?
  19. check if the query results empty row mysqli [duplicate]
  20. PHP – Import CSV file to mysql database Using LOAD DATA INFILE
  21. double results in my array ( mysql_fetch_array )
  22. How best to get someone’s ‘rank’ from a scores table with php and mysql without looping
  23. php:Store image into Mysql blob, Good or bad?
  24. How to get rid of MySQL error ‘Prepared statement needs to be re-prepared’
  25. How to prepare statement for update query? [duplicate]
  26. Use of PDO in classes [duplicate]
  27. MySQL sort by some list
  28. Mysqli multiple row insert, simple multi insert query [duplicate]
  29. Updating column so that it contains the row position
  30. PHP/MySQL Pagination

Leave a Comment