PHP SQL echoing out rows issue

Question

I am trying to echo out a dome data out of a table but for some reason it is only echoing out two results. Probably because there are only two possible numbers under sub_parent. I was hoping it would echo out all 6 rows but just group them? Any ideas what's going wrong?

<?php
require_once 'db/db.php';

$sql = "SELECT *
FROM main_menu_sub
GROUP BY sub_parent
ORDER BY id
";

$result = $con->query($sql);
$con->close();
?>

<?php
    if ($result->num_rows > 0) {        
        while($row = $result->fetch_assoc()) {
        ?>
            <li><a href="<?php echo $row["Sub_menu_url"]; ?>"><?php echo $row["Sub_menu_title"]; ?></li>



            <?php
                }

                    }
            ?>

`GROUP BY` is usually used with the `HAVING` keyword and aggregate functions, which you do not have, why do you think you need it in your query? If you remove it, do you get your expected results? If not, what results do you get, and what results are you expecting? — GrumpyCrouton, Sep 28 '18 at 16:37
The only possible values for sub_parent are 2 & 3. I would expect the first 3 would echo out first that are grouped by 2, following by the other 3 that have the value of 3. AT the moment only one of each are echoing out. If I remove the GROUP BY the results are fine but these are subheading which neet to be grouped later on. — SamualG, Sep 28 '18 at 16:39
I don't think `GROUP BY` does what you think it does. It's good for aggregating data, such as adding total counts together. If you remove it, I bet you'll get your expected results. — GrumpyCrouton, Sep 28 '18 at 16:40

score 2 · Accepted Answer · answered Sep 28 '18 at 16:40

What you want is ORDER BY, not GROUP BY. GROUP BY means to combine all the rows with the same value of sub_parent into a single row. This is usually used in combination with aggregation functions like MAX(), COUNT(), etc. to summarize all the rows in the group.

So your query should be:

SELECT *
FROM main_menu_sub
ORDER BY sub_parent, id

This will put all the rows with the same sub_parent together, and within each group they'll be ordered by id.

If you want to show a heading for each sub_parent group, see How can i list has same id data with while loop in PHP? for a technique to print a heading whenever the sub_parent changes.

PHP SQL echoing out rows issue

1 Answers1